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

Excel formula bar is a tool that can be used to edit any cell values applying a formula. 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 along with protecting the datasheet. In this article, you will get to know how to show and hide formula bars in Excel.

To make the explanation clearer I am using a datasheet. There are 4 columns which are Employee name, Base Salary, Transport Fee, and Total Salary.

Sample Datasheet

Workbook to Practice

3 Ways to Show and Hide Formula Bar in Excel

1.Using Ribbon

I. To Show the Formula Bar

For demonstrating you I’ve hidden the Formula Bar. Let’s see the approach to show it again.

To show the formula bar using ribbon first, open View tab >> then go to Show group >> Select the Formula Bar option.

Show the Formula Bar

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

Formula Bar Visible

II. To Hide the Formula Bar

To hide the Formula Bar using ribbon first, open View tab >> then go to Show group >> Formula Bar option

Now Deselect the Formula Bar.

Hide formula bar

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

Formula Bar hidden

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 >> then switch to the View tab >> Show category to find the Formula Bar option.

2. Using Excel Options

I. To Show the Formula Bar

To use Excel Options first click File >> then select Options.

Using Excel Options

After selecting Options, it will pop up a new dialog box. From Excel Options, select Advanced then go to Display and select Show formula bar.  Finally, click OK.

Excel Options to show formula bar

Now it will show the Formula Bar in Excel

Showing formula bar using Excel Options

II. To Hide the Formula Bar

To use Excel options first open File >> then select Options.

Using Excel Options

After selecting Options, it will pop up a new window/dialog box. From Excel Options, select Advanced then go to Display and deselect Show formula bar.  Finally, click on OK.

Hiding formula bar using Excel Options

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

Formula Bar hidden

 

3. Using VBA

I. To Show the Formula Bar

First, open Developer tab then select Visual Basic.

Show Formula bar using VBA

Then a new window of Microsoft Visual Basic for Applications will appear. Now Insert a Module.

Show Formula bar using VBA

Now, in the module write the VBA code to show the formula bar.

Sub Show_FormulaBar()

    Application.DisplayFormulaBar = False

End Sub

VBA code to show formula bar

Save the code and go back to your worksheet.

From worksheet now open View>> go to Macros>> then select View Macros

Using Macros to show formula bar

After selecting View Macros, a dialog box will pop up. Now select Show_FormulaBar and click on Run

Run Show_FormulaBar

It will show the Formula Bar in Excel.

Showing Formula Bar

II. To Hide the Formula Bar

First, open Developer tab then select Visual Basic.

Hide Formula bar using VBA

Then a new window of Microsoft Visual Basic for Applications will appear. Now Insert Module.

Inseted module

Now write the VBA code to hide the formula bar.

Sub Hide_FormulaBar()
    Application.DisplayFormulaBar = False
End Sub

Using VBA code to hide formula bar

Save the code and go back to your worksheet.
From worksheet now open View>> go to Macros>> then select View Macros

Using Macros to hide formula bar

After selecting View Macros, a dialog box will pop up. Now select Hide_FormulaBar and click on Run.

Run Hide_FormulaBar

After selecting Hide_FormulaBar to run it will hide the Formula Bar in Excel.

Formula bar hidden

Expand Excel Formula Bar using Cursor

When we use a large formula in a Formula Bar it becomes difficult to see the full formula together. When you need to expand the formula bar bigger than the default size you can expand the formula bar using a mouse cursor.

Place the cursor on the lower side of the formula bar and hold the left side of the mouse and drag it down. It will expand the formula bar.

Expand formula bar

You can resize it just by dragging the cursor upward using the left side of the mouse.

Expand formula bar

If you want to expand the default size of Formula Bar using keyboard shortcut then you can use the keyboard shortcut.

Press CTRL+SHIFT+U

  • One press will expand the Formula Bar.
  • Second press will go back to default size.

Default formula bar

Hide All Formulas from Formula Bar in Excel

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

Excel gives us that opportunity of hiding formulas.

First, open the Home tab >> then go to Number>> click the arrow.

Hide Formulas

It will pop up a dialog box. Now select Protection then click OK.

Hide Formulas

Now another dialog box will appear. Select Hidden and click OK.

Hiding formulas

Now go back to the worksheet and open Review>> select Protect Sheet

Protecting sheet

After selecting the Protect sheet a dialog box will appear. Now insert a password in Password to unprotect sheet: then click on OK.

Insetting Password to protect

Now another dialog box will appear to Confirm Password. Reenter the password and click OK.

Confirming Password

Look at the formula bar E6 cell is selected but not showing any formula.

Formula not visible

If you double click on any cell of Total Salary it won’t show any formula also it will give a message.

No formula is showing

To unprotect the worksheet Open Review tab>> then select Unprotect Sheet. It will pop up a dialog box to enter a password.

Unprotect sheet

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

After unprotecting formula is visible

Conclusion

In this article, I explained several ways of how to show and hide formula bars. I think it will help you to hide and show the formula bar when you needed. You are most welcome to give any kind of suggestions, ideas, feedback. Feel free to comment down below.

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo