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

Get FREE Advanced Excel Exercises with Solutions!

The Excel formula bar is a tool that can be used to edit any cell value by 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 while protecting the datasheet. In this article, you will learn how to show and hide the formula bar in Excel.


What Is Formula Bar in Excel?

The formula bar is a feature that shows the formula of any cell without distracting the other cells in the dataset. It is located between the ribbon and the dataset. The function symbol (fx) is attached to this bar. The formula bar is marked in the image below.

Formula bar in Excel


1. Using Ribbon Feature to Show and Hide Formula Bar in Excel

In this section, we will use the ribbon feature to show and hide the formula bar.

i. Showing the Formula Bar

To demonstrate to you, I’ve hidden the formula bar. Let’s see the approach to showing it again.

  • To show the formula bar using the ribbon first, click on the View tab.
  • Then go to Show group and select the Formula Bar option.

Show the Formula Bar in Excel

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

Formula Bar Visible


ii. Hiding the Formula Bar

  • To hide the Formula Bar using the ribbon first, click on the View tab.
  • Then go to Show group and deselect the Formula Bar option.

Hide formula bar in Excel

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

Read More: [Solved]: Excel Cell Contents Not Visible but Show in Formula Bar


2. Applying Excel Options to Show and Hide Formula Bar

i. Showing 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.
  • Then, select the Show formula bar. Finally, click the OK button.

Excel Options to show formula bar

  • Now it will show the Formula Bar in Excel.

Showing formula bar using Excel Options


ii. Hiding the Formula Bar

  • Go to the Excel Options window as shown in the previous section.
  • 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

Read More: How to Hide Cell Contents in Excel Formula Bar


3. Creating VBA Macro to Show and Hide Formula Bar in Excel

i. Showing the Formula Bar

  • First, open the Developer tab.
  • Then select the Visual Basic option.

Show Formula bar using VBA

  • Then a new window of Microsoft Visual Basic for Applications will appear.
  • Now choose the Module option from the Insert tab.

Show Formula bar using VBA

  • Now, in the module section write the VBA code to show the formula bar.
Sub Show_FormulaBar()
    Application.DisplayFormulaBar = False
End Sub

VBA code to show formula bar

  • After that, run the VBA code by pressing the F5 button. It will show the Formula Bar in Excel.

Showing Formula Bar


ii. Hiding the Formula Bar

  • Again, we go to the VBA module window as shown in the previous section.
  • Now write the VBA code to hide the formula bar.
Sub Hide_FormulaBar()
    Application.DisplayFormulaBar = False
End Sub

VBA macro to hide formula bar

  • Now, run the code by pressing the F5 button. This will hide the Formula Bar in Excel.

Formula bar hidden

Read More: [Fixed!] Formula Bar Is Not Showing in Excel


How to Expand Excel Formula Bar Using the 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.

📌 Steps:

  • Place the cursor on the lower side of the formula bar 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 transform the Formula Bar into its default size, there is a keyboard shortcut for this.

  • Select the formula bar and press Ctrl+ Shift+ U.

Default formula bar

The formula bar was modified to its default size.

Read More: How to Reduce the Size of Formula Bar in Excel


How to 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 the opportunity to hide formulas.

📌 Steps:

  • First, select Range E5:E11.
  • Now go to the  Review tab.
  • Choose the Protect Sheet from the Protect group.

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

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

  • Look at the formula bar of Cell E5 is selected but does not show any formula.

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

  • To unprotect the worksheet Review tab.
  • Then 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.


Download Practice Workbook


Conclusion

In this article, I explained several ways how to show and hide the formula bar in Excel. I think it will help you to hide and show the formula bar when you need it. You are most welcome to give any kind of suggestions, ideas, or feedback.


Related Articles

What is ExcelDemy?

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

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo