Quick Excel Tip: The FASTEST Way to Sum Numbers
Categorize this as the most tactical of Excel posts ever. But, doggonit, it sometimes amazes me how many really, really, really handy features of Excel most people don’t even know exist. Even in Excel 2003. This is one that I showed to a co-worker several weeks back as I was looking over her shoulder at a spreadsheet. It’s worth sharing.
Question: What is the fastest way to get the sum of a small range of cells in Excel?
If you immediately think of something along the lines of: “Click on an empty cell, hit the sigma icon, and then highlight the range of cells you want to sum and press <Enter>,” then this tip is for you.
The approach I just described is great if you actually want to keep the sum in the spreadsheet as a permanent calculation. But, what if you are on the phone and discussing the data with somebody who asks, “What was the total from July through October?” You don’t already have that calculated, and you don’t really want to keep that calculation on the spreadsheet. The approach above would work. But there is a better way.
The tip put really, really simply: Highlight the cells you want totaled and look at the bottom right of your screen. They’re totaled for you there (assuming you haven’t gone in and turned off the Status bar under the View menu, and, let’s face it, if you were that desperate for screen real estate, you really should be out shopping for a larger monitor rather than tooling around the internet reading blogs).
Now, With Pictures…
You’ve got a table with numbers in it:
(The formatting of this table offends my data presentation sensibilities in many, many ways, but it’s the default way that many people format tables, and I don’t want to detract from the core of this tip.)
Let’s say you want to get the sum for January through April. Highlight the data you want to total:
Look down in the bottom righthand corner of Excel (if you don’t see this, select View»Status Bar):
Lookie there! Sum=54,200. Chances are, you never noticed that, and yet it’s been industriously summing away every time you’ve highlighted a range of cells in Excel for years!
“That’s all well and good, Gilligan, but what if I want to see the average for these four months?”
Well, that’s easy, too. Just right-click on the Sum= area on the status bar, and you will get a menu that lets you pick what you want math function that box should perform.
Change it to Average, and, henceforth and forthwith (until you change it to something else), that area will show the average of any set of cells you select.
Pretty handy. And, of course, you don’t have to drag a contiguous set of cells. You can hold down <Ctrl> and select multiple non-contiguous cells (e.g., “What was the total for Jan-07 and Jan-08?”).
Be honest. Did you already know that?
