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.
Have you ever built an app based on Excel?
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.
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.
Have you ever built an app based on Excel?
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”.
Hi Hendrik, thanks for the post. I stumbled over a fourth option: I had my cells display only one decimal place. The sum in the status bar uses the complete numbers and thus for some cases let to different results.
I have this issue where there is a difference of around 1000 between the sum in status bar and a formula. All cells are formatted the same way, numeric and there are no hidden rows. Also if i copy the values and paste them in another sheet, the status bar gives correct result but nothing helps for the sheet where i need it. Any idea what might be the issue?
The Excel status bar will not give results for only one item in a column. For 2 or more items it works fine.
OK a new one for you. I have a sheet with a column of numbers that should add to zero. If I select the first (top) cell first, then control down to the bottom, I get the status bar result of 1.75342 x 10-5 or something. Fine. But if I select the BOTTOM cell first and reverse the process I get flat 0.00.
(No there is no mistake here!)