Hosne Ara
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.
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).
- 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)
- 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.
- Finally, you can see that all the formulas used over the range E5:E10 are displayed in another range (here, F5:F10).
Similar Readings
- How to Convert Text to Formula Using the INDIRECT Function in Excel
- How to Show Formula in Excel Cells Instead of Value (6 Ways)
- [Fixed]: Excel Formula Not Showing Correct Result (8 Methods)
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.
- 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.
- Next, click on 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.
- 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.
- 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.
