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

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 ...

+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 ...