This post is going to show you all the ways you can show, hide, expand, or collapse the formula bar in Microsoft Excel.

Did you know you can show, hide, and resize the formula bar?

The formula bar is a toolbar that appears above the worksheet and displays the formulas in the cells of the worksheet. It can be helpful to show, hide, expand and collapse the formula bar when you are working on your spreadsheet.

This can be essential when you need to view long formulas or when you want more real estate to view your worksheet area.

This blog post will show you how to show, hide, expand and collapse the formula bar in Microsoft Excel.

Get your copy of the example workbook and follow along with this post.

Show or Hide the Formula Bar with the View Tab

Formulas are an essential component of using Microsoft Excel, so the formula bar is visible by default.

The formula bar is where you can enter, edit, and view formulas in your workbook, and this is an area in the Excel window that can be hidden.

It’s easy to show or hide the formula bar, and this can be done from the View tab in the ribbon commands.

Follow these steps to show the formula bar.

1. Go to the View tab.
2. Check the Formula Bar option found in the Show section of the ribbon.

When you check this option, the formula bar will be shown just below the ribbon commands and just above the worksheet grid.

Follow these steps to hide the formula bar.

1. Go to the View tab.
2. Uncheck the Formula Bar option found in the Show section of the ribbon.

When you uncheck this option, the formula bar will no longer be visible.

Note: This is an app-level setting. This means the formula bar will be shown or hidden according to this setting for all your workbooks, and if you send the workbook to anyone else their formula bar settings won’t be changed.

Show or Hide the Formula Bar with Excel Options

The option to show or hide the formula bar can also be found in the Excel Options menu.

This is the exact same setting as found in the View tab, it’s just a bit more obscure.

1. Go to the File tab to access the Excel Options menu.

This will open the Excel backstage area.

1. Click on Options at the bottom left side of the window to open up the Excel Options menu.

3. Go to the Advanced tab in the Excel Options menu.
4. Check or uncheck the Show formula bar option in the Display section.
5. Press the OK button.

When you press the OK button, the Excel Options menu will close and the new setting will take effect. This setting will also be updated in the View tab.

Show or Hide the Formula Bar with a Keyboard Shortcut

There is no dedicated keyboard shortcut to show or hide the formula bar, but you can use the Alt hotkey shortcuts to access the setting found in the ribbon commands.

When you press the Alt key, this will activate the hotkey prompts that will show you what key combinations to use to navigate to the command you want.

Press Alt + W + VF on your keyboard to toggle on or off the Formula Bar setting in the View tab.

Excel also has legacy Alt hotkey combinations from Excel 2003 before there was a ribbon and some of these can still be used.

Press Alt + V + F to use the legacy hotkey combination to show or hide the formula bar.

Both these keyboard shortcuts act as a setting toggle to show or hide the formula bar depending on the current setting.

Show or Hide the Formula Bar with VBA

Visual Basic (VBA) is a scripting language baked into Excel and you can use it to automate just about anything in your spreadsheets.

Showing and hiding the formula bar is even an event that can be automated with VBA.

Press Alt + F11 on your keyboard to open the Visual Basic Editor (VBE). This is where you can use the below codes to show or hide the formula bar.

VBA Code to Show the Formula Bar

Sub ShowFormulaBar()
    Application.DisplayFormulaBar = True
End Sub

The above VBA code will set Excel’s DisplayFormulabar property to True and this will show the formula bar.

VBA Code to Hide the Formula Bar

Sub HideFormulaBar()
    Application.DisplayFormulaBar = False
End Sub

The above VBA code will set Excel’s DisplayFormulabar property to False and this will hide the formula bar.

VBA Code to Hide the Formula Bar when a User Selects a Given Column

Imagine you don’t want a user to see the formula bar when a certain column is selected. You can trigger the VBA code to run a script that hides the formula bar.

You can trigger this macro to run for a particular worksheet.

Right-click on the sheet and choose View Code from the option.

This will open the Visual Basic Editor on the code module for that sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 3 Then
        Application.DisplayFormulaBar = False
    Else
        Application.DisplayFormulaBar = True
    End If
End Sub

Paste the above VBA code into the sheet module.

This will run every time you make a selection in the sheet. The If... Then... Else... statement will test if the user has selected the third column and then hide the formula bar if this is the case, otherwise it will show the formula bar.

The result is the formula will be hidden anytime the third column gets selected.

Expand or Collapse the Formula Bar with the Mouse

If you’re dealing with a really long formula or data on multiple lines, the standard size formula bar can be an issue.

By default, the formula bar will only show one line. But thankfully, this can be expanded quite easily as needed.

When you hover the mouse cursor over the bottom edge of the formula bar, it will turn into a two-sided arrow. You can left-click and drag this to expand the formula bar to multiple lines.

You can also resize the length of the formula bar. Hover the mouse over the three dots between the name box and the formula bar and it will turn into a two-way arrow. Left-click and drag to resize.

Expand or Collapse the Formula Bar with the Ribbon Toggle

Once you have resized the formula bar vertically to show more lines, you might want to toggle between the expanded and collapsed view.

This can quickly be achieved with the toggle found to the right of the formula bar in the ribbon.

Left-click on this and the formula bar will resize to its last expanded line size.

Expand or Collapse the Formula Bar with a Keyboard Shortcut

The formula bar toggle also comes with a handy keyboard shortcut!

Press the Ctrl + Shift + U to expand or collapse the formula bar.

Expand or Collapse the Formula Bar with VBA

Resizing the formula bar is another task that can be performed with VBA.

With a single line of code, you can set the formula bar height to any size.

Sub ResizeFormulaBar()
    Application.FormulaBarHeight = 4
End Sub

The above code will set the formula height to 4 lines.

VBA Code to Resize the Formula Bar when a User Selects a Given Column

Suppose you have a column of data that contains multiple lines of text in each cell and you only want the formula bar to be in its expanded state when actively working with this column.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 2 Then
        Application.FormulaBarHeight = 5
    Else
        Application.FormulaBarHeight = 1
    End If
End Sub

You can add the above code to the sheet module to change the height of the formula bar whenever the user selects data in the second column.

The result is the formula bar will be expanded anytime a cell in the second column gets selected.

Conclusions

The formula bar is an integral part of Excel. But you might need to temporarily remove it for more worksheet real estate. You also might need to expand it when working with longer formulas.

It’s possible to show, hide, expand, or collapse the formula bar for an occasional need.

These features are easily accessible from the ribbon and can even be automated with VBA.

Do you ever use this option to customize the formula bar? Do you know any other formula bar tricks? Let me know in the comments below!