Excel Troubleshoot With Status Bar

Use the Excel Status Bar to troubleshoot problems, and get key information. When running macros, show custom messages in status bar, with macro progress updates.

Customize Excel Status Bar

At the bottom of the Excel window, the Status Bar shows information about Excel, and the files that you're working on.

When you install Excel, a default set of features are shown on the Status Bar. This Microsoft article shows the list of options, and which ones are selected by default.

You can customize the Status Bar, to remove features that you don't need, and add features that will be helpful to you. Just follow these steps, to turn options on or off

• Right-click on the Status Bar, to see the list of options
• Click on an option, to turn it on or off.

Video: Customize Excel Status Bar

This short video shows the steps for customizing the Status Bar.

Status Bar Workbook Statistics

If you're using Excel 365, there's a new Status Bar feature that you can show -- Workbook Statistics.

• To add it, right-click the Status Bar
• Click on Workbook Statistics

The pop-up list shows what's on the current sheet, and in the active workbook. It has counts of pivot tables, images, and objects too, if the file contains those. It even counts hidden and very hidden worksheets.

Tip: Learn more about the Workbook Statistics feature on the Microsoft website.

Troubleshoot VLOOKUP Errors

With some VLOOKUP Function formulas, if you're trying to match numbers in a lookup table, you might gt unexpected #N/A errors.

Usually, that's because some of the numbers are "text" numbers, and the others are real numbers.

Status Bar Check for Real Numbers

Here is a quick way to check for real numbers:

1. Select two or more cells that contain the numbers you want to check
2. Look in the status bar, at the bottom of the Excel window.
   • If only a Count is showing, the values are test, not numbers.
   • If Numerical Count is also showing, and is equal to the Count, all the values are real numbers

In the screen shot below, the selected numbers are real - the Count is the same as Numerical Count.

Video: VLOOKUP Number Problem

To see the steps for fixing the Excel VLOOKUP function problem when the lookup table has text values, and the lookup value is a number, watch this short video. There are written steps on the Fix VLOOKUP Numbers Problem page.

Calculation Alerts in Status Bar

There are a couple of calculation alerts that you might see see in the Status Bar, and those alerts can help you troubleshoot a workbook.

• Calulate
• Circular Reference

Calculate Alert

The default setting for Excel calculation mode is Automatic. When that setting is selected:

• all open workbooks automatically recalculate every time there is a change

However, when calculation mode is set to Manual:

• all open workbooks only recalculate if you press F9, or Ctrl+Alt+F9, or you save a workbook
• if there are uncalculated formulas, the status bar will show "Calculate"

Learn more about the Excel calculation settings, go to the Calculation Options and Macros page.

Circular Reference Alert

Another calculation alert that you might see is Circular Reference.

If the problem is in a formula on the active sheet, the Status Bar will show the cell address. If the problem is on a different sheet, the Status Bar will show "Circular References".

NOTE: For more information on working with circular references, or fixing them, go to the Circular Reference support page on the Microsoft site.

AutoFilter Problem - Filter Mode

Normally, after you have applied an Excel AutoFilter, the Status Bar shows a count of the records that remained visible. In the screen shot below, the Status Bar shows, "10 of 37 records found".

However, in some situations, the Status Bar does not show the record count. Instead, the Status Bar simply shows, "Filter Mode." This can happen if:

• the list has many formulas
• something is changed in the list, after a filter has been applied.

Note: This problem has been fixed in newer versions of Excel.

Video: AutoFilter Problem - Filter Mode

You can see the Filter Mode problem and workarounds in the following video, and there are written instructions on the AutoFilter Tips & Troubleshooting page.

Show Helprul Status Bar Messages

You can add your own messages to the Status Bar too, such as a progress message for macros that take a long time to run.

For example, there are two macros shown below, and each macro:

• checks 100K cells that contain numbers
• enters a value in the cell to the right, based on the checked cell's value

Tip: To get the sample data and the two macros shown below, go to the Download section, at the bottom of this page.

1. The first macro does NOT show a custom message in the Status Bar. If you're running that macro, you'll have to wait patiently, and hope the macro will end soon!
2. The second macro DOES show a custom message in the Status Bar. As the macro runs, you'll see messages that indicate the macro progress, so you know what's happening!

The extra bit of code might slow the macro down a little more, but it’s usually a good tradeoff – a few seconds slower, for a little peace of mind.

Slow Macro - No Status Bar Messages

The following macro runs slowly, and does NOT show any messages in the Status Bar.

Sub GroupNumbers_Orig()
Dim ws As Worksheet
Dim rngSel As Range
Dim c As Range
Dim lCol As Long
Dim lRow As Long
Dim lRowEnd As Long
Dim strGroup As String
Dim lRowCurr As Long

Set ws = ActiveSheet

lRow = 2
lCol = 1

With ws
  lRowEnd = _
    .Cells(.Rows.Count, lCol) _
      .End(xlUp).Row
  Set rngSel = _
    .Range(.Cells(lRow, lCol), _
      .Cells(lRowEnd, lCol))
End With

For Each c In rngSel
  strGroup = ""
  Select Case c.Value
    Case Is > 750:  strGroup = "A"
    Case Is > 500:  strGroup = "B"
    Case Is > 250:  strGroup = "C"
    Case Else:      strGroup = "D"
  End Select
  
  c.Offset(0, 1).Value = strGroup
Next c

End Sub

Slow Macro With Status Bar Messages

aThe following macro runs slowly, and DOES show messages in the Status Bar. For example, this line of code lets you know that the macro is starting.

Application.StatusBar = "Starting the update"

At the end of the macro, this line of code gives control of the status bar back to Excel:

Application.StatusBar = False

Here is the full macro, to show Status Bar messages.

Sub GroupNumbers_Messages()
Dim ws As Worksheet
Dim rngSel As Range
Dim c As Range
Dim lCol As Long
Dim lRow As Long
Dim lRowEnd As Long
Dim strGroup As String
Dim lRowCurr As Long

Set ws = ActiveSheet

lRow = 2
lCol = 1

With ws
  lRowEnd = _
    .Cells(.Rows.Count, lCol) _
      .End(xlUp).Row
  Set rngSel = _
    .Range(.Cells(lRow, lCol), _
      .Cells(lRowEnd, lCol))
End With

For Each c In rngSel
  If c.Row Mod 10000 = 0 Then
    Application.StatusBar = _
      "Updating Row " _
        & Format(c.Row, "#,##0") _
        & " of " _
        & Format(lRowEnd, "#,##0") _
        & " rows"
  End If
  strGroup = ""
  Select Case c.Value
    Case Is > 750:  strGroup = "A"
    Case Is > 500:  strGroup = "B"
    Case Is > 250:  strGroup = "C"
    Case Else:      strGroup = "D"
  End Select
  
  c.Offset(0, 1).Value = strGroup
Next c
Application.StatusBar = False
End Sub

Get the Sample File

VLOOKUP Troubleshoot: To see the VLOOKUP problem and suggested solutions, get the sample workbook. The zipped file is in xlsx format, and does not contain any macros.

Macro Messages: To see the macro with status bar messages, get the Status Bar Message sample workbook. The zipped file is in xlsm format, and contains the two Status Bar Message macros, shown on this page, above.

More Tutorials

Fix VLOOKUP Numbers Problem

Calculation Options