Excel has a very handy feature: It displays some quick information about the selected cells in the status bar. This might be the sum, average, maximum or minimum value. Unfortunately, in some cases, the shown number seems to be wrong. Here is why!
Select what information you want to see
No doubt, the status bar is very helpful. Since the Excel version 2003, you can display more than one type of information. For example, the sum, the average, maximum and so on at the same time.
Customizing the information in the status bar is very easy. Follow these steps:
- Right-click on the status bar.
- Click on the type of information you’d like to see.
Why does the status bar show an obviously wrong number?
Let’s take a look at an example. You have a simple Excel table with 3 rows. When summing up the values of item 1, item 2 and item 3 the result should be 6. This is shown in the screenshot on the right-hand side.
Sometimes, Excel doesn’t show the correct result. There are 3 possible reasons.
Reason 1: There are hidden or grouped columns
First of all: you are right. You didn’t make a mistake. But Excel isn’t wrong either. Well, it handles the status bar information in a strange and rather inconsequential way.
When summing up rows, hidden rows in-between are disregarded (see the upper screenshot on the right-hand side).
On the other hand, when summing up columns, the shown result regards hidden and grouped columns. This is shown in the lower of the 2 screenshots on the right-hand side.
So how to disregard hidden and grouped columns? Select only visible cells. Follow these steps:
- Do your selection as normal. In this case you would cells C3 to E6 (including the hidden cell D6).
- Press Ctrl + G on the keyboard.
- Press Alt + S on the keyboard. Now you should see the “Go To Special” window.
- Select “Visible cells only” (or press Y on the keyboard) and press enter.
Now, the correct result of all visible cells should be shown.
Reason 2: Some numbers are formatted as text
It’s also possible that some cells aren’t formatted as numbers but rather as text. In such case the information in the status bar will show wrong results as well.
Please take a look at the image on the right-hand side. The sum of the 3 cells C3, C4 and C5 should be 6. Instead, Excel shows 4. The reason is that cell C4 is formatted as text instead of a number.
How to handle this? Force all numbers to text. In this simple example it’s easy: Format the cell C4 as number, enter it one time and press Enter on the keyboard.
If you have many cells, please refer to this article for more information of how to force cells to the number format.
Reason 3: The status bar doesn’t show “Sum” but just “Count”?
When you select the range of cells, does Excel show a sum? Or just “Count” and “Numerical Count”? In such case, please check if there are error messages in any of your cells.
So what to do now? You have got several options:
- Solve the errors. Please refer to this page for help on solving error messages in Excel.
- Suppress the error messages by using the “IFERROR” or “IFNA” formulas. Please refer to this article for more information.
- Only select cells without errors. Like in option 1, use the “Go To Special” function and unselect “Errors”.