How to Show All Formulas in Excel (4 Easy & Quick Methods)

In this article, I will discuss how you can show all formulas in  Microsoft Excel easily. Sometimes, when you receive a spreadsheet from others, you might need to check which cells are containing formulas. Besides, if you want to investigate why a certain formula is not working, you might need to display all formulas. Moreover, you can understand the correlation between excel data by seeing all formulas. So, let’s dive into the article to learn some easy and quick methods to display all formulas in excel.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


4 Easy & Quick Methods to Show All Formulas in Excel

Let’s consider a dataset containing several items’ sales data including the total sales. In this dataset, the summation of total sales is calculated using excel formulas. Now I will show you 4 easy and quick ways to show all formulas of this dataset.

4 Easy & Quick Methods to Show All Formulas in Excel

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

You can use the Show Formulas option to see all formulas in an excel sheet.

Steps:

  • First, go to the excel sheet where you want to show all the formulas.
  • Then form Excel Ribbon, go to the Formulas tab, and click on the Show Formulas option (Formula Auditing group).

Display All Formulas Using ‘Show Formulas’ Option in Excel

  • As a result, all the formulas present in the existing worksheet are displayed in the below screenshot.  You can hide all the formulas by clicking the Show Formulas option again.

Note:

  • You can print excel sheets with formulas by using the Show Formulas option. Simply show formulas using the path Formulas > Show Formulas. Then go to File > Print to print the worksheet.

Read More: How to Show Formulas When Printing in Excel


2. Apply Excel FORMULATEXT Function to Show All Formulas

This time, I will use the FORMULATEXT function in excel to show all formulas in a worksheet. By using this function, I will show formulas in a cell next to the calculated result. This method is effective when you need to see both the calculated result of the formula and the formula simultaneously. To do the task, follow the below steps.

Steps:

  • Go to the worksheet where the formulas are located. In my dataset, as I know total sales are calculated using formulas, I have typed the below formula in Cell F5, referencing the formula cell. You can enter the cell reference as you need.
=FORMULATEXT(E5)

Apply Excel FORMULATEXT Function to Show All Formulas

  • Next press Enter from the keyboard. Consequently, Excel will return the below result. We can see that the formula used in Cell E5 is displayed as a text string. Now you can use the Fill Handle (+) tool if formulas are present in contiguous cells.

Apply Excel FORMULATEXT Function to Show All Formulas

  • Finally, you can see that all the formulas used over the range E5:E10 are displayed in another range (here, F5:F10).


Similar Readings


3. Use Keyboard Shortcut (Ctrl + `) to Show All Formulas in Excel

You can show all formulas in a worksheet by using Ctrl + ` keys. We can use this keyboard shortcut instead of clicking the ‘Show Formulas’ option.

Steps:

  • Go to the excel sheet where you want to display all the formulas and press Ctrl +` from the keyboard. The grave accent (`) key is located between the Tab and Esc keys of the keyboard.

Use Keyboard Shortcut (Ctrl + `) to Show All Formulas in Excel

  • As a consequence, all the formulas are displayed in the below screenshot. You can hide formulas again by pressing Ctrl + ` again.


4. Display All Formulas with Excel Options

Now, I will use Excel Options to show all formulas. You can see all the formulas in an excel workbook too. However, I will show you displaying all the formulas in a specific worksheet.

Steps:

  • Suppose I have some formulas in Sheet1. To display all the formulas in this sheet, click the File tab from Excel Ribbon.

Display All Formulas with Excel Options

  • Next, click on Options.

Display All Formulas with Excel Options

  • As a result, the Excel Options dialog box appears. Then choose the Advanced option. Scroll down the bar on the right side until the Display options for this worksheet appear. Now, choose the sheet name and put a checkmark on the Show formulas in cells instead of their calculated results. After that press OK.

Display All Formulas with Excel Options

  • Finally, upon pressing OK, you can see all the formulas in Sheet1 are displayed.


Things to Remember

  • You can select all the formula-containing cells by using the Find & Select options in excel. However, following this method won’t show formulas.

Things to Remember

  • We can show formulas only in selected cells using the Find and Replace option in excel. This method too won’t display all formulas in an excel worksheet.

Conclusion

In the above article, I have tried to discuss several methods to show all formulas in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo