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

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.

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.

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.

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

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.

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

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.

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.

Then, select 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.

Therefore, you will see the formulas of Sheet6.

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.

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

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

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.

As a result, you can see the Formulas.

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.

Therefore, you can see the highlighted cells with the 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.

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.

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

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

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

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.

Show All Formulas.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!