HOT TOPICS:  Foreign buyers   |   Housing affordability  |  NZ$                                                       KEY RESOURCES:    Economic calendar   |  Rent ratio

The comment stream

Recent comments

Reader poll

Actually they should ban high LVR lending, where the borrower doesn't have a deposit of at least 20%, altogether.
36% (221 votes)
Yes, as soon as possible.
24% (148 votes)
They should leave the restriction as it is for at least another year.
14% (89 votes)
No, but they should increase the limit from 10% to 15%.
13% (81 votes)
No, they should leave the 10% limit there permanently.
13% (78 votes)
Total voters: 617

Should the Reserve Bank remove its "speed limit" on banks' high loan to value ratio (LVR) home lending?

Older polls

In the first of our new regular series on Microsoft Excel tips, we look at the various ways you can add up things on a spreadsheet

Posted in Business

One of the most widely used tolls on any office is the spreadsheet.

It is used to do a huge number of tasks and we are today launching a regular Excel tip series to help those who are not expert users get more out of the software.

Today we are looking at the many ways you can add stuff up on a spreadsheet. Many you may know, but there may also be some ideas here that you could find useful that you did not know about.

We encourage you to use the comment section below to add in your own tips.

---------------------------------------------------------------------------------------------

Adding up stuff

Firstly, and basically, the fastest way to do addition on a spreadsheet is to use your keyboard.

In any cell on any spreadsheet, type a + then the numbers you want added, like this ...

Related Topics

+56+69+887+2-14+5 and hit ENTER. That will give you an answer of 1,005.

(Note that the same thing works for negative values; just key a ' - ' instead of a '+'.)

The good thing about doing this is that each of the numbers you keyed in are preserved in the cell so you can go back and check what you keyed.

Notice that when you completed doing this, it recorded the string as =56+69+887+2-14+5 with an equals sign as the first symbol (=). Technically you should always start a formula with an =, but Excel is smart enough to know you mean = when you start an add-up with a +.  (But a + may not always do the job when you start typing other types of formulae.)

SUM

Of course, you can total up the values in many cells - probably the most used formula in Excel.

=SUM(A5:A17) for a list in a column (column A), or something like =SUM(A5:X5) for a list in a row (row 5).

You can add everything in a column by a formula like =SUM(A:A) - which adds every value in column A. But don't forget, you can't put this formula in column A because it will refer to itself and you will get a circular reference error.

You can add values in a Range of cells, like =SUM(A5:G17); this adds up all values in that big block of cells. The only constraint here is to make sure you want every value included in that range.

Or you can use a mixture of things like =SUM(A5:A19,G16,G:G,45,72,1000,-55). In this case, you are adding up seven separate things; including fixed numbers, cell ranges, whole columns, all separaed by a comma (,).

The =SUM( etc) can be nested within other functions, which is often very helpful when you are using other formulas.

AUTOSUM

Another way to add-up specific data is to highlight the data you want to sum and click the 'autosum' button from your toolbar. The sum of the numbers highlighted should appear in the next cell. Can't find the autosum button? It looks like a Greek symbol for sigma (for those who have graduated university) or an .

To add this button to your excel toolbar go to View in your current toolbar, select toolbars from the drop down menu, from the categories on the left hand side select insert and on the right hand find the autosum button. Next, while holding down the left mouse button, drag the icon up to your toolbar in excel; if done correctly you should now see the icon appear - you can shift icons around by dragging them to where you want them. 

Tips

#1. Make sure the other cells you want to add up are all numbers. If they are not, they won't be included in your answer. If they are some error value, you won't be able to get an answer until you sort thoser errors out.

#2. Sometimes you want to show a number in a cell, but you don't want it to be included in any calculations. Enter such 'numbers' (actually, they are just text that you want to look like a number) with a ' in front of it, like '54. The ' will not display, but the 54 will and be treated as text. It won't be treated as a number in any calculation. (Don't forget you did that, as the person you confuse could well be yourself!)

#3. Sometimes you can find it hard to resolve why =SUM( etc) won't work as you expect. Often that is because what you think looks like a number in a cell, actually isn't one (or at least one Excel can recognise). The most common problem is that you hit the space bar (or some other invisible character) before or after you typed in your value. Excel thinks these are not numbers (and they can be frustratingly hard to identify). But that could be the reason your formula gives a wrong answer.

#4. If you use a cell to key in a string of numbers (as we showed above), eg +56+69+887+2-14+5, you can convert that to the result of 1005 easily by clicking in the value bar (sometimes called the formula bar) and keying Ctrl+. You can do this same shortcut in the cell itself - works the same. Give it a try; its a quick trick.

AND

Don't think AND is a way to add things up; it isn't. It is used in Excel as a logical expression, not as an adding expression. We will deal with AND at another time.

---------------------------------------------------------------------------------------------

We encourage you to use the comment section below to add in your own tips about this function. Or ask a question ...

We welcome your help to improve our coverage of this issue. Any examples or experiences to relate? Any links to other news, data or research to shed more light on this? Any insight or views on what might happen next or what should happen next? Any errors to correct?

We welcome your comments below. If you are not already registered, please register to comment in the box on the right or click on the "'Register" link at the bottom of the comments.

Remember we welcome robust, respectful and insightful debate. We don't welcome abusive or defamatory comments and will de-register those repeatedly making such comments. Our current Comment policy is here.

18 Comments

#3 by default, Excel puts

#3 by default, Excel puts what it thinks is text on the left and numbers on the right, making it fairly easy to spot text unless someone has messed with the formatting. If someone has messed with the formatting, clear the formatting from the column. If you sort the column, the non-number entries (even '95) should sort after the number ones (but sorting needs extreme caution).

Um.... admittedly I've been

Um.... admittedly I've been using Exel at a professional level for like 20 years, but really, an article on how to add in Excel???

Don't they teach this now in kindergarten or early primmers???

Perhaps more useful would be some articles showing how to use some of the amortize functions or loan repayment functions, or how to crosstab.... VLOOKUP is an essential Excel function to know.

Yesterday I eased my GF's stress about her kiwisaver account by taking her payments and units data since inception and working out, in Excel via some crosstabbing and summing, how many units she has now and the approx value of her Kiwisaver, and how it was performing over time vs her inputs and the fees.

It's not rocket surgery. Ramp up the complexity of your tips, David.

Yeah, way too basic.  I'd say

Yeah, way too basic.  I'd say that VLookups, IFs, Counts would be better.
 
VBA suggestions would be even better. 

Seriously?  Just go to the

Seriously?  Just go to the Excel is Fun! channel on you tube and get all the tips and tricks you need.
David, you are seriously wasting your time on this (and the so called jobs calculator)...
 
 

I have always found the brain

I have always found the brain is far cheaper...pencil and paper!

This reminds me of the time I

This reminds me of the time I created spread sheets from the return my accountant did the previous year and printed off the current year. I sent them off to the accountant but being an old fella he re did them all manually anyway. Didn't save me any money in the end.
 
One thing I will give him credit for though is the calculator that prints on a ribbon, great for data entry and you can see if you went wrong anywhere. But I would do my GST like you say Wolly, using the bank statements and a pencil.

Wolly - I agree. An incorrect

Wolly - I agree. An incorrect entry can change the figures and if you are using the brain at the same time you immediately know something is wrong.

except when you model and do

except when you model and do what if say 5 years out.

With Microsoft Office you pay

With Microsoft Office you pay a lot of money for a lot of stuff you will never use.
There are some really good FREE office programs out there that can read and save as a Microsoft document.
I use Libra Office

Yep I use libra office at

Yep I use libra office at home and at work.....if you do macros though (in excel) you cant get those to load in libra....but for what ppl use outside that ie small businesses its a free download and good enough...I model in it a lot. Disadvantge 2 is fancy formatting doesnt cross sometimes, but most ppl wont bother.
These days though basic office on say a new dell is free I think...probably have to as ppl would just use libra.
In fact if you want to minimise costs go linux, no anti-virus programs needed which are a stop gap anyway...ie reactive...ubuntu is a free download you can try off a cd...
regards

Steven, I can't work out your

Steven, I can't work out your politics, but your IT advice is sterling.   Ubuntu and Libre Office rock. 

For me well I dont do

For me well I dont do politics, I just left the green party as they had turned into a tomato with some green fungus in the side IMHO.  So I want the best economic answer to the problem, for the zero bound trap, that is keynesian.  But using keynesian out side of that well I think there is enough real world examples eg the mess Gordon  Brown made and the 1970s in the UK to show quasi-keynes would be wrong...
regards
regards

Im not a power user in

Im not a power user in either, I do servers not desktops, But for all my simple workings they always swap no problem just as long as I save in <2003 xls format...2007  with xlsx is less compatible but even that has improved hugely, I dot think ive had an issue in 2 years. Open office used to be staroffice I think and its been re-badged libre, its come a long way since then.  Most businesses though still use MS Office so I have to have that to hand as well.
:/
You could download it and try it...its free after all...even getth ubuntu desktop that boots off your cd drive, a little slow but it wont touch your hard drive.
;]
Let me know what you think...
 
 
 

One thing thats quite

One thing thats quite important is actually compatmentalising a spreadsheet if its likely to get complex.
So if you have say 5 input variables I take each one and either have a seperate sheet or background colour a section and work out the sub-answer in that,. say yellow.
When I go to the main calc where I place that number i background colour that yellow as well so I know where it came from.
regards