How to Show All Formulas in Excel (5 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Looking for ways to show all formulas in Excel? We are here for you. In this article, we will extensively describe 5 easy ways to do the task smoothly. In addition to this, we will describe how you can highlight, hide, and print formulas in Excel.

If you’re dealing with a spreadsheet that contains numerous formulas, it can become difficult to understand the relations between formulas. Displaying formulas rather than their results in Excel can aid in monitoring the data. Thus, this article is helpful for you when you want to show all formulas in Excel.

In the following overview image, you can see that we have shown all the formulas in Excel. So, let’s dive into this article to do the task.

Overview Image for How to Show All Formulas in Excel


What Are the Benefits of Showing All Formulas in Excel?

Showing all formulas in Excel has several benefits for the users. Below we are discussing the benefits:

  • Finding Error Quickly: You will be able to scan and identify errors quickly in a formula.
  • Understanding Formula Logic: After viewing the formula, you will be able to understand the logic of a formula. This is especially helpful for complex formulas.
  • Modifying Formula: Viewing the formula helps you to change or modify the formula very quickly if needed.
  • Avoid Double-Clicking: You do not need to double-click on a cell to see the formula in the Formula Bar. In larger spreadsheets with a bulk amount of formulas, this can save huge time.
  • Verify Consistency: Viewing the formula helps to check the consistency of the cell reference. This assures the accuracy of the formula.

How to Show All Formulas in Excel: 5 Easy Methods

In the following dataset, we have the Employee Name, Basic Salary, Overtime Salary, and Total Salary columns. Here, we have formulas in the Total Salary column. Now, using this dataset, we will go through 5 easy methods to show all formulas in Excel.

Here, we used Excel 365. You can use any available Excel version.

Dataset to Show All Formulas in Excel


1. Display All Formulas Using the ‘Show Formulas’ Option in Excel

In this method, we will use the Show Formulas option to show all the formulas in Excel.

  • First of all, go to the Formulas
  • Then, from the Auditing group >> click on Show Formulas.

Selecting Show Formulas Option

Therefore, you can see all the formulas in the Total Salary column.

Result after clicking on Show Formulas Option


2. Use Keyboard Shortcut (CTRL + `) to Show All Formulas in Excel

In this method, we will use the keyboard shortcut CTRL+` keys to show all formulas in Excel.

  • Go to the Excel sheet where you want to display all the formulas and press CTRL +` from the keyboard.

Note: The grave accent (`) key is located between the TAB and ESC keys of the keyboard.

Use of Keyboard Shortcut

Therefore, you can see all the formulas in the Total Salary column.

Result after using Keyboard Shortcut


3. Apply Excel FORMULATEXT Function to Show All Formulas

In this method, we will use the FORMULATEXT function to show all formulas in Excel. Here, we will keep the calculated result and the corresponding formula side by side in two columns.

  • We first type the following formula in cell F5.
=FORMULATEXT(E5)

The FORMULATEXT function returns a text in place of a formula.

  • Then, we press ENTER.
  • We drag down the formula with the Fill Handle tool.

Thus, you can see the complete formula column with the formulas.

Use of FORMULATEXT Function


4. Display All Formulas with Excel Options

In this method, we will use Excel Options to show all formulas.

We have some formulas in Sheet6.

  • To display all the formulas in this sheet, click the File.

Clicking on the File tab

  • Then, select Options.

Selecting Options

At this moment, an Excel Options dialog box will appear.

  • Then choose the Advanced option.
  • Scroll down the bar on the right side until the Display options for this worksheet.
  • After that, we select Sheet6 >> mark on the Show formulas in cells instead of their calculated results.
  • Finally, click OK.

Selecting Show Formulas from Option

Therefore, you will see the formulas of Sheet6.

Result after using Excel Options to Show All Formulas


5. Showing Formulas of a Protected Sheet

In this method, we will show how you can show the formulas of a protected sheet.

Here, we have a protected Excel sheet. We have a column showing formulas. However, the column is hidden.

Thus, we first have to unprotect this sheet and then unhide the column to see the formulas.

  • First, to unprotect the sheet, we will go to the Review tab >> click on the Unprotect Sheet option.

Unprotecting Sheet

At this moment, an Unprotect Sheet dialog box will appear.

  • Then, we will provide the Password >> click OK.

Providing Password

Thus, the sheet is now unprotected.

Now, we will unhide the formula column.

  • To do so, we will right-click on the hidden column >> select Unhide from the Context Menu.

selecting Unhide from Context Menu

As a result, you can see the Formulas.

Result after unhiding Column


How to Highlight Cell Formulas in Excel?

If you do not need to view the formulas but you need to identify the cells that contain them, you can highlight the cells with formulas. Here, we will describe how you can do so.

  • In the beginning, go to the Home tab.
  • Then, from the Find & Select group >> select Formulas.

Selecting Formulas from the Find & Select Feature

Therefore, you can see the highlighted cells with the formulas.

Highlighted Cells with Formulas


How Can You Hide All Formulas in Excel?

Here, you can see that we have displayed the formula in the Total Salary column. Now, we want the calculated results instead of the formulas. We have to hide the formulas for this case.

Now, we will describe how you can hide all formulas in Excel.

  • Go to the Formulas tab >> unmark Show Formulas.

This will hide all the visible formulas in a dataset.

Here, you can press the CTRL+` keys to hide all the formulas in Excel.

Therefore, you can see that there is no formula shown in the Total Salary column.

Gif showing how to Hide All Formuls in Excel


How Can You Print an Excel Sheet with All Formulas?

Here we will describe how you can print an Excel sheet that displays all the formulas.

  • To do so, first, we used Method 3 to show all formulas in Excel.
  • We will press CTRL+P to print this sheet.

Printing Formulas in Excel

Now, we will see the print preview of this sheet.

Therefore, we will click on the View tab >> click on Page Break Preview.

Selecting Page Break Preview

Thus, you can see the Print Preview of the Excel sheet with formulas.

Print Preview of Excel Formula


Things to Remember

  • You can display formulas and calculated values after each alternative click because CTRL+` is a toggling option.
  • It’s important not to delete a formula in Excel. Instead, take care to either show or hide them as needed.
  • In Excel, if a cell is formatted as TEXT, it could display the formula instead of the formula’s resulting values. To display the values of the formula, change the cell’s formatting to General.

Frequently Asked Questions

1. How Can I Just Copy a Formula Ignoring The Values?

  • Choose the cell that holds the formula you wish to copy.
  • Right-click on the cell >> select Copy or, press Ctrl + C on your keyboard.
  • Select the cell or range of cells where you want to paste the formula.
  • Right-click on the selected cells >> select Paste Special or, press CTRL+ ALT + V on your keyboard.
  • In the Paste Special dialog box >> select Formulas under Paste >> click OK.

This will paste the formula only excluding the value.

2. How Can I Convert a Formula to Text in Excel?

  • To convert a formula to text, select the cell containing the formula.
  • Go to the Home tab >> click on the Number Format dropdown arrow in the Number
  • Select Text as the Number Format.
  • Press ENTER.

This will convert a formula to Text.


Download Practice Workbook

You can download the Excel file and practice the explained methods.


Conclusion

In this article, we describe 5 easy methods to show all formulas in Excel. You will find all these methods easy and effective.

In addition, we describe the benefits of showing all formulas in Excel. The benefits will help you understand why and when we need to show all formulas in Excel.

This article also describes how you can highlight, hide, and print all formulas in Excel. The detailed description of these topics will be greatly beneficial for you.

We hope this is helpful to you. Thank you for going through the article. If you have any queries or suggestions please let us know in the comment section.


Related Articles


<< Go Back To Show Excel Formulas | Excel Formulas | 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.
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

 

 

ExcelDemy
Logo