Confusing Options on Excel Status Bar

Confusing Options on Excel Status Bar

The Excel Status Bar shows you messages about what’s going on, while you work. Some of those messages are optional, and you can turn them on or off. Here’s how you can customize the Excel Status Bar, and see my notes on a couple of the options that were confusing.

What’s in the Status Bar

This week, I checked the Status Bar, to see what the Num Lock setting was, but it wasn’t visible. So, while adjusting my Status Bar settings, I found a few odd things, and decided to share them with you.

By sharing my findings, as inconsequential as they are, I can justify the time that I wasted (invested?) in exploring the Status Bar options.

P.S. I have an English/French keyboard, with keys in odd places, and keeping hitting Num Lock accidentally. I hope the Status Bar setting will help me notice when it’s off.

Automatic Status Messages

Excel automatically shows some messages in the Status Bar, which you can’t turn on or off. For example, if you point to a cell with a comment, the author’s name appears in the Status Bar.

statusbarcomments01

Or, if you apply a filter, the status bar will show the number of visible records, and the total number of records. And sometimes it just shows “Filter Mode”.

statusbarfilter01

Another message you might see in the Status Bar is a Circular Reference warning. If you’re on a sheet that has a Circular Reference, the cell address is shown.

statusbarcircular01

There are other automatic messages too, such as query refreshes, file opening information, and things that are loading, such as the Data Model.

Add Your Own Messages

With programming, you can show your own messages in the Status Bar too.

For example, in a slow-running macro, show the progress, to help people understand what’s happening.

Optional Messages in Status Bar

There’s also optional information on the Status Bar, that you can turn on or off, based on your preferences.

Some of the items are turned on by default, when you install Excel. This Microsoft article shows the list of options, and which ones are selected by default.

  • To see the list of Status Bar options, right-click on the Status Bar.
  • Then, click on any option, to toggle it on or off.

statusbaroptions01

Status Bar Calculations

Some of the Status Bar options are for quick calculations:

  • Average
  • Count
  • Numerical Count
  • Maximum
  • Minimum
  • Sum

If you select two or more cells that contain data, the Status bar show a quick calculation for the function options that you’ve turned on.

  • If the selected cells contain text only, a Count is shown.
  • If at least one cell contains numeric data, the other functions are shown.

NOTE: The Status Bar Count calculations is like the worksheet COUNTA function – it counts all types of data.

statusbarquickcalc01

See Current Status

When you right-click on the Status Bar, to see the list of options, some show a current status, to the right of the option name.

To the right of some option names, you can see current status for that setting. The list also shows the calculation amounts for the functions that are shown on the Status Bar.

statusbaroptions02

See More Totals

While the Status Bar options list is showing, you can force the totals for other functions, without adding them the Status Bar

  • Check a function option, to see its total
  • Immediately uncheck the function

statusbaroptions03

WARNING: For functions that are not in the Status bar, the forced totals will not change automatically, if you select different data. Check/uncheck the functions again, to see updated amounts.

Confusing Options

Most of the Status Bar options are easy to figure out, but there were a couple that confused me at first.

View Shortcuts – Oh! Does this show helpful keyboard shortcuts, like the tooltips for the Ribbon commands show? No. It does not. It shows or hides the icons for the workbook views – Normal, Page Break Preview, or Page Layout.

Page Number – I thought this would show which page you were on in Page Break Preview, but that didn’t work. You’ll only see page number in the Status Bar if you’re in Page Layout view. I never use that view, do you?

Flash Fill – There are 2 options for the Flash Fill feature:

The Changed Cells message was easy to see – it appears after you use the Flash Fill feature.

Here’s a screen shot of the infamous month name Flash Fill (Mayuary?), and the Status Bar shows the number of changed cells.

statusbarflashfill01

The Blank Cells option was confusing though. How could you have blank cells in a Flash Fill?

After a bit of experimenting, I finally saw that message, and here’s what I did:

  • Use the Flash Fill feature, and press Enter, to accept the suggestions.
  • Immediately, clear one or more of the cells that were filled

You can see the number of blank cells in the Status Bar.

statusbarflashblank01

What’s on Your Status Bar?

So, what’s on your Status Bar? Did you customize it, or do you just go with the default settings?

Do you show all the functions? Num Lock? Caps Lock? Other good stuff?

______________________

Confusing Options on Excel Status Bar

Confusing Options on Excel Status Bar

______________________

10 thoughts on “Confusing Options on Excel Status Bar”

  1. I always turn on Average, Count, Numerical Count, Min, Max, and Sum. After reading this article, I am adding Caps Lock and Num Lock! Thank you Debra!

  2. I wrote a macro that shows the difference between two selected cells. It replaces Ready if I select two cells, two rows, two columns, or other combinations where a difference make sense.

  3. Hi. When I select more than two cells, the font changes for the sum, count and average. When this happens, the last digit of the total is cut off. Has anyone else had this problem?

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.