“SUM” Excel-lent ways to see numbers

For those of us who manage, compare, contrast and report numbers, to really know Excel is to love Excel.  There is not enough room on this page to explore all of the ways Excel helps but we do have room for a few quick tips about how some things “add up”.

SUM Where:  The quickest and easiest way to add the total of a list of numbers in a column or a row (columns up and down and rows go across) to your worksheet is to hit the “AutoSum” button.  In Both Excel 2003 and 2007 the AutoSum button, marked with symbol like this: ∑, is on the main toolbar.  In ’07 it is in the “Editing” group on the “Home” tab on the ’07 “ribbon.”  If you click into the first empty (blank) cell at the bottom of a list in a column or to the right of a list in a row and press the AutoSum button Excel will guess that you want to total that list and select it for you.  Press “Enter” and it’s done.

SUM Thing Else:  Look at the AutoSum button and you will notice a small downward facing arrow to the right of the sum symbol. Upon clicking that arrow, you will see a list of options that allow you to choose from other common math functions such as: finding an average of the numbers, a count of the numbers, or the minimum (Min) or maximum (Max) values in the list each with basically one click.

SUM Thing a Little Quicker:  If you do not want to add a sum of numbers to a worksheet but need a quick total of numbers in a column or row all you need to do is select them.   Select them and glance down at the bottom of your worksheet to what is called the “Status Bar.”   On the right side of the status bar Excel ’03 will display the sum of the selected numbers.  In Excel ’07 by default you will get the Sum, the count and the average.  In ’03, by right clicking on the number, you can have it display the Average, the Count, the Min, the Max or the sum (just like with the AutoSum button).  Excel ’07 will allow you to display any or all.  If you need a quick total of what the attorney Zimmerman billed in January,  select just those cells and glance down at the status bar and (in ’07) see this:

SUM Times You Need More Control:  If you need to sum (or average or count) numbers that are not in a neat and orderly column or row you can do that with the same tools and your Control (Ctrl) key.  When using the AutoSum button, rather than allowing Excel to “guess” which numbers you want to sum, just click on individual cells.  Click the first cell you want to include then, while holding down the control (Ctrl) key, click the next, and the next and the next…  If you need a quick Status Bar Sum do the same; click the first cell you want to include then, while holding down the control key, click the next, and the next and the next… 

SUM Thing That Stands Out:  This “something” is not really a “SUM Thing” but something worth noting.  Conditional Formatting can be used to make certain numbers on your list stand out if they meet certain conditions.  To use conditional formatting in ’07 there is a button on the “Styles” section of the home tab.  In ’03 go to the “Format” menu and choose “Conditional Formatting.”   The possibilities for formatting are virtually endless and, again; there is not enough room on this page.  But here is one simple example that will hopefully inspire you to try more.  Using that Zimmerman list above you want to see, at a glance, any billing amount that was less than $10,000.  (Because, really; why waste the postage stamp?)  By clicking on the letter “B” at the top of the column of billed amounts select the entire column.   Then (in ’07) click the conditional formatting button, choose “Highlight Cell Rules” then “less than” and you see a dialog box.  Put in the number 10,000 and click the “OK” button.  Instantly any cell in that column that has a number in it lower than 10,000 is highlighted in red.  (The dialog box in ’03 is very similar and just as simple to use.)

SUM Of Its Parts:  Excel can calculate subtotals for your worksheet data.  On a sheet with 799 “Billed” amounts for nine attorneys you can quickly see a sum of how much each attorney billed.  First be sure that each column of your data has a “header” or a title at the top.  Then be sure that your data is sorted by the column for which you want subtotals (Attorney).  The easiest way to do this is select the cell that contains that header and click the “Sort Ascending” button.   It is on the Standard toolbar in ’03 and on the “Data” tab in ’07.

With your data sorted and that cell (“Attorney”) still selected press the “Subtotal” button also on the “Data” tab in ’07.  (In ’03, on the “Data” menu, choose “Subtotals.”)  In either version a resulting Subtotal dialog box will appear.  Excel will “guess” that  you want information at each change in “Attorney.”  (That is why it was important to sort the data by that column.)  Choose the function “Sum.”  (Average, count, min and max are also available here as they were with AutoSum and on the Status Bar.)  Under “Add subtotal to:” check “Billed” because those are the numbers to total.  (If Excel has “guessed” another column uncheck that as well.)  When you click “OK” you will see, to the left of your worksheet, the numbers 1, 2 and 3 with dots or lines below them.  (Those numbers are the three levels of detail that excel will display.)  Click the number 2 and SUM thing wonderful happens!  You may need to make your columns a little wider to see the bigger numbers.  Clicking the little “plus sign” next to any of the names will expand that section and show the detail for that attorney.

SUM Thing to remember:  If you have a problem you can always click “Undo” (or hold down the control key (Ctrl) and press “Z” to undo) and just try again.

And finally...Have SUM Fun!

 

This article was written by THE Jeffrey Zimmerman.