How to Show All Formulas in Excel – 5 Easy Methods

In the following overview image all the formulas are displayed in the sheet.

Overview Image for How to Show All Formulas in Excel


This is the sample dataset containing Employee Name, Basic Salary, Overtime Salary, and Total Salary.  Formulas were used in the Total Salary column.

Dataset to Show All Formulas in Excel

Method 1 – Displaying All Formulas Using the ‘Show Formulas’ Option in Excel

  • Go to Formulas
  • In the Auditing group >> click Show Formulas.

Selecting Show Formulas Option

All the formulas are displayed in the Total Salary column.

Result after clicking on Show Formulas Option

Method 2 – Using the Keyboard Shortcut (CTRL + `) to Show All Formulas in Excel

  • In the Excel sheet, press CTRL +`.

Use of Keyboard Shortcut

All formulas are displayed in the Total Salary column.

Result after using Keyboard Shortcut

Method 3 – Apply the Excel FORMULATEXT Function to Show All Formulas

  • Enter the following formula in F5.

The FORMULATEXT function returns a text.

  • Press ENTER.
  • Drag down the formula with the Fill Handle tool.

All formulas are displayed in the Total Salary column.

Use of FORMULATEXT Function

Method 4 – Display All Formulas with Excel Options

  • Click File.

Clicking on the File tab

  • Select Options.

Selecting Options

The Excel Options dialog box opens.

  • Choose Advanced.
  • In Display options for this worksheet, select Sheet6.
  • Check Show formulas in cells instead of their calculated results.
  • Click OK.

Selecting Show Formulas from Option

All  formulas are displayed in Sheet6.

Result after using Excel Options to Show All Formulas

Method 5. Showing Formulas in a Protected Sheet

  • To unprotect the sheet, go to the Review tab >> click Unprotect Sheet.

Unprotecting Sheet

In the Unprotect Sheet dialog box:

  • Enter the Password >> click OK.

Providing Password

The sheet is unprotected.

  • To unhide the formula column, right-click the hidden column >> select Unhide.

selecting Unhide from Context Menu

All  formulas are displayed.

Result after unhiding Column

How to Highlight Cell Formulas in Excel?

  • Go to the Home tab.
  • In Find & Select >> choose Formulas.

Selecting Formulas from the Find & Select Feature

The highlighted cells with formulas are displayed.

Highlighted Cells with Formulas

How Can You Hide All Formulas in Excel?

  • Go to the Formulas tab >> unmark Show Formulas or press CTRL+` to hide all formulas in Excel.

No formula is displayed in the Total Salary column.

Gif showing how to Hide All Formuls in Excel

How Can You Print an Excel Sheet with All Formulas?

  • Use Method 3 to show all formulas.
  • Press CTRL+P to print the sheet.

Printing Formulas in Excel

This is the print preview.

  • Select the View tab >> click Page Break Preview.

Selecting Page Break Preview

This is the Print Preview with formulas.

Print Preview of Excel Formula


Download Practice Workbook

You can download the Excel file and practice.

Related Articles

<< Go Back To Show Excel Formulas | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF