How to Show and Hide Formula Bar in Excel (3 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

To show and hide the Formula Bar in Excel:

  1. Go to the View tab > Show group.
  2. Mark the Formula Bar checkbox to show it.
  3. Unmark the checkbox to hide the Formula Bar.

This will successfully show or hide the Formula Bar in Excel.


The Formula Bar is an Excel feature that allows users to view, edit, and enter Formulas or data into cells. By default, the Formula Bar is visible in Excel directly above the worksheet area. Sometimes we share our worksheet data but don’t want to share the Formula. Then we can hide the Formula Bar while protecting the datasheet.

In this article, you will learn to show and hide the Formula Bar in Excel using the Ribbon feature, Excel Options, and creating a VBA macro.

The following image illustrates the shown and hidden version of the Formula Bar in Excel.

Before after of show and hide formula bar


What Is Formula Bar in Excel?

The Formula Bar in Excel is a designated area located above the spreadsheet grid where you can view and edit cell Formulas. It displays the contents of the currently selected cell, allowing users to input, edit, or review Formulas and data.

The Formula Bar is an essential tool for handling complicated calculations. It offers a user-friendly platform to input and manage Formulas, providing a more detailed view of cell content compared to the cell itself. This feature allows you to view the Formula of any cell without affecting the other cells in the dataset.

Here are 3 methods to show and hide the Formula Bar in Excel:


How to Show Formula Bar in Excel?

If the Formula Bar is not visible when you open Excel, it means it is hidden and you should adjust the settings to display it. You can use Excel features or VBA macro to show the Formula Bar in Excel.

Here you will learn three methods to show the Formula Bar in Excel:

1. Using Ribbon Feature

The Ribbon feature in Excel contains various commands and tools to use Excel. It is designed to make commands and functions more visible, helping users discover and utilize tools for various Excel tasks.

To show the Formula Bar using the Ribbon feature, follow the steps below:

  1. Click on the View tab and check the Formula Bar option in the Show group.
    Check the Formula Bar option in Excel

After selecting the Formula Bar checkbox, the Formula Bar will be visible.

Formula Bar Visible

These options work on Excel 365, Excel 2019, Excel 2016, Excel 2013, and Excel 2010. 

  • For Excel 2007, you will find the Formula Bar option within the View tab >> Show/Hide group.
  • For Excel 2003, Tools >> Options >> View >> Show category to find the Formula Bar option.

2. Applying Excel Options

You can easily switch the visibility of the Formula Bar by accessing the Excel Options. This helps you customize the interface according to your needs and makes your workspace more flexible.

To show the Formula Bar using Excel options, try these steps:

  1. Click on the File tab.Clicking the File tab
  2. Select Options from the left navigation pane.Selecting Options from left pane After selecting Options, it will pop up a new dialog box.
  3. Go to Excel Options > Advanced > Display.
  4. Select the Show Formula Bar OK.Selecting show formula bar in Excel options

Now it will show the Formula Bar in Excel.

Showing formula bar using Excel Options

3. Applying Excel VBA

Using VBA in Excel gives users the ability to automate and personalize tasks. By utilizing VBA code, users can easily switch between displaying and hiding the Formula Bar.

To show or hide the Formula Bar using VBA, follow the steps below:

  1. Open the Developer tab and select the Visual Basic option.Opening Visual Basic Then a new window of Microsoft Visual Basic for Applications will appear.
  2. Choose the Module option from the Insert tab.Inserting new module
  3. Write the VBA code in the Module section.
    Sub Show_FormulaBar()
    Application.DisplayFormulaBar = False
    End Sub
  4. Run the VBA code by pressing the F5 button.

It will show the Formula Bar in Excel.

Showing Formula Bar using VBA


How to Hide Formula Bar in Excel?

You can also hide the Formula Bar using similar features in Excel. Hiding the Formula Bar allows more visibility and space in the Excel worksheet.

Here you will learn three ways to show the Formula Bar in Excel:

1. Using Ribbon Feature

To hide the Formula Bar using the ribbon feature, follow the steps below:

  1. Click on the View tab.
  2. Go to the Show group and unmark the Formula Bar checkbox.Unmarking formula bar in Excel

After unselecting the Formula Bar, the Formula Bar will not be visible in Excel.

Formula Bar hidden


2. Applying Excel Options

To hide the Formula Bar using Excel options, follow the steps:

  1. Click on the File tab.
  2. Select Options from the left navigation pane.
    The Excel Options dialog box will appear.
  3. Go to Excel Options > Advanced > Display.
  4. Unmark the Show Formula Bar checkbox and click on OK.Hiding formula bar using Excel Options

Now the Formula Bar won’t be visible in Excel.

Formula Bar hidden


3. Applying Excel VBA

To hide the Formula Bar using Excel VBA, follow the steps:

  1. Open the Developer tab and select the Visual Basic option.Opening visual basic The Microsoft Visual Basic for Applications window will appear.
  2. Choose the Module option from the Insert tab.Inserting new module
  3. Write the VBA code in the Module.
    Sub Hide_FormulaBar()
    Application.DisplayFormulaBar = False
    End Sub
  4. Run the code by pressing the F5 button.

This will hide the Formula Bar in Excel.

Hiding formula bar using VBA


How to Hide All Formulas from Formula Bar in Excel

Now Data is the most valuable thing in the world. When you share any Excel workbook with anyone, they can see all the functions in that Excel file which is good as well as bad. If you want to show the sheet only and not let anyone change the sheet then you need to protect the datasheet by hiding all Formulas.

To hide all Formulas from the Formula Bar, follow the steps below:

  1. Select the cell range.
  2. Go to the Review tab > Protect group > Protect Sheet option.Selecting protect sheet After selecting the Protect Sheet a dialog box will appear.
  3. Insert a password in Password to unprotect sheet: and click on OK.Inserting new password The Confirm Password dialog box will appear.
  4. Reenter the password and click OK.Reenter password to protect
  5. Look at the Formula Bar of a selected cell but it does not show any Formula.Formula is invisible
  6. Double-click on any cell of the particular column, it won’t show any Formula but will give a message.Warning message to enter password
  7. To unprotect the worksheet, go to the Review tab and choose the Unprotect Sheet.
    It will pop up a dialog box to enter a password.Unprotect sheet to show formula in formula bar

After entering the password sheet will be unprotected. Now you will be able to see the Formulas in the Formula Bar.

Formula is visible


Download Practice Workbook


Conclusion

This article has shown various methods to show and hide the Formula Bar in Excel. Among the methods, the Ribbon feature method is very simple to apply. You can also apply the VBA method here. I think it will help you hide and show the Formula Bar when needed. You are most welcome to give any kind of suggestions, ideas, or feedback.


Frequently Asked Questions

What is the shortcut key for showing the Formula Bar in Excel?

The shortcut to show the Formula Bar in Excel is “Alt+W+V+F”. This shortcut also reverses the condition (hides the Formula Bar).

How do I get the Formula Bar back in Excel?

To get the Formula Bar back in Excel,  click File and go to Options. Then click Advanced in the left pane. Scroll down to the Display section and select the Show Formula Bar option.

How do I collapse the Formula Bar in Excel?

Simply use the keyboard shortcut Ctrl + Shift + U to complete your desired expansion or reduction of the Formula Bar in no time.


Related Articles


<< Go Back to Excel Formula Bar | Excel Parts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo