Status Bar Shows a Wrong Result (Sum, Average) – Here Is Why

wrong, sum, number, average, excel, status, bar

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

customize, change, information, status, bar, excel, sum, average, status bar
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:

  1. Right-click on the status bar.
  2. Click on the type of information you’d like to see.

Why does the status bar show an obviously wrong number?

example, group, grouping, rows, columns, status, bar, excel, status bar
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.

Reason 1: There are hidden or grouped columns

sum, correct, status bar, excel
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).

regard, columns, hidden, hide, excel, grouped
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:

  1. Do your selection as normal. In this case you would cells C3 to E6 (including the hidden cell D6).
  2. Press Ctrl + G on the keyboard.
  3. Press Alt + S on the keyboard. Now you should see the “Go To Special” window.
  4. 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

number, formatted, text, excel, status, bar, information
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.

Reason 3: The status bar doesn’t show “Sum” but just “Count”?

solve, avoid, errors, mistake, message, status bar
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”.

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

4 comments

  1. 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.

  2. Hi!

    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?

  3. The Excel status bar will not give results for only one item in a column. For 2 or more items it works fine.

  4. 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.
    Explain that!
    (No there is no mistake here!)

Leave a comment

Your email address will not be published. Required fields are marked *