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. 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. When you uncheck this option, the formula bar will no longer be visible.
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. This will open the Excel backstage area. 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
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
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. 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. 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. 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!
