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.
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
This short video shows the steps for customizing the Status Bar.
If you're using Excel 365, there's a new Status Bar feature that you can show -- 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.
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.
Here is a quick way to check for real numbers:
In the screen shot below, the selected numbers are real - the Count is the same as Numerical Count.
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.
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.
The default setting for Excel calculation mode is Automatic. When that setting is selected:
However, when calculation mode is set to Manual:
Learn more about the Excel calculation settings, go to the Calculation Options and Macros page.
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.
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:
Note: This problem has been fixed in newer versions of Excel.
You can see the Filter Mode problem and workarounds in the following video, and there are written instructions on the AutoFilter Tips & Troubleshooting page.
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:
Tip: To get the sample data and the two macros shown below, go to the Download section, at the bottom of this page.
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.
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
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
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.
Last updated: June 8, 2022 6:54 PM