[Excel VBA] Useful Macros to Toggle Display Settings (Formulas, Gridlines, Phonetics, and More)

Introduction

When using Excel, there are many situations where you want to switch display settings, such as “checking all formulas temporarily,” “hiding gridlines to check the print preview,” or “showing phonetics (furigana) all at once.”

Changing these settings from the menu every time can be a bit tedious. However, if you create a “Toggle Macro” using VBA, you can switch them On/Off instantly with a single button.

In this article, I will introduce 6 carefully selected “Display/Hide Toggle Macros” that you can copy and paste for immediate use. Registering these macros to your Quick Access Toolbar will make your Excel usage even more efficient.

1. Toggle Formula Display

This is useful when you want to display the actual formulas in cells instead of the calculation results. It helps with checking the entire sheet’s formulas and debugging. This performs the same function as the Ctrl + Shift + @ shortcut key.

' Toggle formula display for the current sheet
Sub ToggleFormulasDisplay()
    With ActiveWindow
        .DisplayFormulas = Not .DisplayFormulas
    End With
End Sub

Explanation: ActiveWindow.DisplayFormulas is the property that controls whether formulas are displayed in the active window. By inverting its own current state using Not, it switches between True and False every time you run it.

2. Toggle Gridlines Display

This is helpful when you want to temporarily hide the gridlines that separate cells, such as when checking the visual appearance of a report or taking a screenshot.

' Toggle gridlines display for the current sheet
Sub ToggleGridlinesDisplay()
    With ActiveWindow
        .DisplayGridlines = Not .DisplayGridlines
    End With
End Sub

Explanation: ActiveWindow.DisplayGridlines controls the display of gridlines. It uses the same logic as the formula display above to toggle visibility.

3. Toggle Formula Bar Display

This is useful when you want to use the screen widely to view the entire Excel sheet. Hiding the formula bar expands the visible area.

' Toggle formula bar display for the entire Excel application
Sub ToggleFormulaBarDisplay()
    With Application
        .DisplayFormulaBar = Not .DisplayFormulaBar
    End With
End Sub

Explanation: The formula bar is a setting for the entire Excel application, not just a specific window. Therefore, we use Application.DisplayFormulaBar to control it.

4. Toggle Status Bar Display

This is also effective when you want to maximize screen space. It toggles the display of the status bar at the bottom of the Excel window, which shows sums and averages.

' Toggle status bar display for the entire Excel application
Sub ToggleStatusBarDisplay()
    With Application
        .DisplayStatusBar = Not .DisplayStatusBar
    End With
End Sub

Explanation: Since the status bar is also an application-wide setting, we use Application.DisplayStatusBar.

5. Toggle Phonetics (Furigana) Display

You can show or hide “Phonetic information” (reading aids) entered for names in a list. This is useful for checking name lists.

' Toggle phonetics display for the selected range
Sub TogglePhoneticsDisplay()
    ' Prevent errors if selected cells have no phonetic info
    On Error Resume Next
    With Selection.Phonetics
        .Visible = Not .Visible
    End With
    On Error GoTo 0
End Sub

Explanation: This macro works on the selected cell range (Selection). Selection.Phonetics.Visible controls the visibility of the phonetics.

6. Toggle Page Breaks Display

This toggles the display of the blue dotted lines (page breaks) that indicate the print area. This is useful when these lines become distracting during normal editing work.

' Toggle page breaks display for the current sheet
Sub TogglePageBreaksDisplay()
    With ActiveSheet
        .DisplayPageBreaks = Not .DisplayPageBreaks
    End With
End Sub

Explanation: Page breaks are a setting specific to each sheet. Therefore, we use ActiveSheet.DisplayPageBreaks to control the display for the currently active sheet.

Summary

In this article, I introduced 6 “Toggle Macros” that instantly switch various display settings.

• The basic logic for toggling is Property = Not Property.
• It is important to be aware of the target object: Window, Application, Sheet, or Selection.

Once you save these short codes in your “Personal Macro Workbook,” you can call them immediately regardless of which file you have open. Please try registering them to your Quick Access Toolbar to streamline your daily Excel tasks.