If you are a seasoned Excel user, you may know what the ‘auto-calculate’ option is. On the other hand, you may think you know (no, it is not the feature where you stop cells from calculating automatically). The auto-calculate option is the status bar at the bottom of your screen when you are working in an Excel workbook. If you are a newbie in Excel, you might not have noticed this helpful feature.
If you have never noticed or paid much attention to this area of your screen, unlearn this behavior! This status bar can be very helpful for a quick check of your figures. You can customize what you see in the status bar as well.
Right click in the status bar, and you will see this menu:
There are some very useful calculations you can include, such as Numeric Count and Count. Count counts all cells in the selection that have content in them (of whatever type), and Numeric Count counts all cells that have numbers in them. (Please, no more counting manually, ever!)
You can have the Minimum or Maximum value displayed, which would allow you to quickly pull out these values from a long column of numbers. Simply click on the Column (or Row) letter (or number), and you will see the Max and Min values in the column displayed in the Status Bar.
On more useful thing about the status bar. If you include a title for your row or column (in Column A, Row 1– or wherever your data starts), make sure your title cell is formatted in the same way the data is formatted. In the example below, you can see that the Sum, Min, and Max are accurate, but not formatted as currency. This is not a show stopper, but it certainly is nicer if formatted the same way as the source cells.
To accomplish this, simply format the title cell “Total Sales” as Currency.
Now your totals are formatted in the same way as your source data.
Meh you say? What’s the big deal–I can read the totals without the currency symbol. Fair enough. What about the dates data? Here’s the Status Bar, unformatted, when the Dates column is selected:
What’s the latest (Max) date? What’s the earliest (Min) date? You are seeing the date as it is stored in Excel–as a pure number. Unless you do the math in your head, you don’t know what dates those are, in human-speak. But, if you format the title cell “Date” as a date, the magic happens.
Now you can see the earliest date and the latest date, in a format that you can easily read.
So what’s in a name? Turns out, a lot. Be sure to use the Auto-Calculate option to its’ fullest capacity. In the meantime, keep soaring!