Status Bar Shows a Wrong Result (Sum, Average) – Here Is Why
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!
Contents
Select what information you want to see
Customize the information shown in the status bar.
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?
Example: The sum of cells C3, C4 and C5 should be 6.
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.
The sum (4) seems to be correct.
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).
The status bar regards hidden and grouped columns.
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.
Hold on a second. Was this information helpful so far?
If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:
The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
Subscribe now!
In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Option 2: Some numbers are formatted as text
Another reason for a wrong result might be that 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.
Option 3: The status bar doesn’t show “Sum” but just “Count”?
Solve or avoid errors.
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”.
