Search Not Just Numbers

Tuesday 3 September 2013

Excel Tips - Quickly adding up numbers without formulae

This week, I've got one of those really simple tips, that are just that - if you already know them.

It is these kinds of tips that tend to get the most interesting reactions when someone sees them for the first time, and realise they have been doing something the long way round for so long.

A couple of other examples from the blog were:

A really quick way to copy down a formula

and

A quick way to add dollar signs in a formula

This week's quick tip tells you how to add up any numbers on the screen without having to type in a formula.

There are many times, when looking at numbers on a spreadsheet, that we want to quickly add a few of those numbers, just to check something (we may be trying to spot which numbers make up a difference that we are trying to find, for example). We could, of course, find an empty cell and enter a formula in the cell, using either SUM to add up a range of adjoining cells, or a simple formula in the form =A1+B3+A4+C7.

Excel, does however provide a simple tool for doing this that you might not have noticed (I've certainly worked with many experienced users that hadn't).

If you highlight any cells with numbers in them, Excel presents their sum on the status bar in the bottom right corner below the spreadsheet:

You can highlight them in any of the usual ways:
  • by using the mouse to select a range of adjacent cells, which can include multiple columns or rows;
  • clicking on the column letters or row numbers to highlight whole columns or rows;
  • highlighting multiple cells or ranges, by holding the Ctrl key while you click on individual cells or select ranges as described above.
In Excel 2007 onwards, you also see a count of the cells with values in, and an average. Note that the count also includes cells with text in - not just numbers. The average, however, only averages the cells with numbers in - as you would want.

This sum (and average and count) appears every time you highlight cells (that include numbers) - you don't need to do anything else.


Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

4 comments:

  1. Not sure why you restricted this feature only to numbers?

    It works just as well and is probably more useful with figures.

    ReplyDelete
    Replies
    1. I'm not sure I understand your distinction between numbers and figures. I certainly didn't mean to make one.

      Delete
  2. Or.. forget highlighting.... and much faster,,,

    "CONTROL + ="

    ... Works on TOTAL as well as SUBTOTAL functions in EXCEL 07, 10 & 13.
    But... ensure that there are no "EMPTY" celled in the column. If empty cells exist, the calculation will stop there. NOT fatal ....EASY FIX... place cursor in last cell and scroll up to the first cell of the column being summed. The problem is cured.
    Enjoy!

    ReplyDelete
    Replies
    1. Surely highlighting is much faster than entering a TOTAL or SUBTOTAL formula.

      Delete