Sometimes, it is important to see the formulas used in an Excel cell to understand how the results are obtained from the given dataset. Displaying formulas allows you to verify their accuracy and troubleshoot any issues. By seeing the actual formulas, you can easily check if the calculations are correct and identify any errors or discrepancies.
In this article, we are going to show important methods to show Excel formulas. There are some features and functions in Excel that can display formulas from the cells containing formula results. We will cover them one by one in this article.
In complex spreadsheets, formulas can become lengthy and intricate. By showing formulas, you can better understand the logic and structure behind the calculations. This is especially helpful when working with formulas created by others or when revisiting your own spreadsheets after some time.
While writing this article, we applied all procedures using Excel 365, but they may be utilized with any Excel version.
Here is an overview of showing formulas in Excel.
How to Show Formulas in Excel?
We can show formulas by commands from ribbon or using formulas. Let’s have a quick look at the procedures.
How to Show Formulas Using Show Formulas Feature from Excel Ribbon?
Here, the Full Name column contains formulas that join first and last names. To show the formulas,
- Go to the Formulas tab first, and then click on the Show Formulas button from the Formula Auditing group.
This command will show all the formulas in your Excel sheet. If you click on this button again, the formula results will appear again.
Thus, you can show the formulas in an Excel sheet.
How to Show Formulas by Enabling Proper Options from Excel Options Features?
We can also show formulas in a sheet by enabling the “Show formulas in cells instead of their calculated results” command from the Options feature. The following picture shows the cells containing formula results and sheet names.
- Open the Excel Options window from the File
- After that, select the Advanced button and scroll to Display options for this worksheet.
- Next, select the worksheet in which you want to show formulas from the dropdown menu.
- Later, check “Show formulas in cells instead of their calculated results”.
- Finally, click OK.
You will see the formulas of the corresponding results in the desired sheet.
The advantage of this feature is, that when you have a huge number of sheets in the workbook, you don’t have to manually search for the one where you want to show formulas. Search from the drop-down list and apply the feature to show formulas.
How to Show Formulas Using Keyboard Shortcut?
- You can also use a keyboard shortcut to show formulas in an Excel sheet. Just press Ctrl + ` (Oemtilde symbol) and the formulas in the sheet will be displayed. You can toggle back this command by pressing Ctrl + ` which will show you the formula results again.
Read More: How to Show All Formulas in Excel
How to Display Formulas Using FORMULATEXT Function?
You can also use the FORMULATEXT function to show the formula used in a sheet.
- Create a new column to store the formulas, and type the following formula beside the corresponding formula result. Use the cell reference of the cell containing the formula in the FORMULATEXT argument.
=FORMULATEXT(E5)- Next, copy the formula by the Fill Handle feature to the lower cells to show all the formulas.
Thus, you can show formulas using the FORMULATEXT function.
Read More: How to Display Cell Formulas in Excel
How to Highlight Formulas in Excel?
- To highlight cells with formula results, we can use the Find & Select feature to select them, which will highlight the cells with formula. Follow the video below to understand how this feature works.
[Fixed!] What to Do If Excel Is Showing Formulas Not Results?
Is It Possible to Fix by Removing Spaces or Single Quote Symbol Before Formulas?
Sometimes, we may put spaces or a single quote symbol or apostrophe (‘) before a formula. This is the reason we see only the formulas, not the formula results.
Here is an image showing that the presence of space causes formula results not to appear in the cell.
The presence of a single quote before the formula also causes a similar issue.
By removing them, we can get the formula results easily.
Thus, you can see the formula results in your Excel sheet.
How to Fix the Issue by Converting Cell Formatting from Text to Other?
If your cell is converted to Text before inserting a formula in it, you won’t get the formula result after entering the formula. The following image illustrates this fact.
So convert the cell format to another format and enter the formula again. Here, we set the format to General and got the formula results.
How to Hide Formulas in Excel?
- To begin with, select the cells with formulas first and right-click on them to open the Context Menu.
- Next, select Format Cells. You can also press Ctrl + 1 to open the Format Cells dialog box.
- After that, select the Protection tab of the Format Cells
- Later, check the Hidden option and click OK.
- After that, select Review >> Protect Sheet. Make sure the cells with formulas are selected.
- The Protect Sheet window will appear. By default, the first 3 check boxes in that window are checked. Keep them checked and provide a password.
- Finally, click OK.
Another dialog box will pop up to confirm the password. After confirming that, you will see that the cells with formulas contain values only. You cannot make any changes or delete those cells.
However, you can edit the cells if you need by unprotecting the sheet. After protecting the sheet, the Unprotect Sheet feature is automatically added to the Review tab in the same ribbon. Just click on it, provide the password and your sheet becomes editable like before.
How to Print Formulas in Excel?
To print the formulas in an Excel sheet, follow one of the methods that we described previously to show formulas and then go to File >> Print. You can also open the Print feature by pressing Ctrl + P. Choose your desired formatting of the printing page and click on the Print command.
Read More: How to Show Formulas When Printing in Excel
What Do You Have to Keep in Mind?
- Do not use the keyboard shortcut Ctrl + ` as a hotkey for other applications.
- The cell formatting should not be “Text” before inserting formulas in them.
Frequently Asked Questions
1. Will showing formulas affect the functionality or integrity of my Excel workbook?
Answer: No, showing formulas does not affect the functionality or integrity of your workbook. It only changes the way formulas are displayed on the screen. The actual calculations and results remain intact.
2. Is there a way to show formulas in Excel temporarily, without permanently changing the display?
Answer: Yes, you may use Excel’s Evaluate Formula tool to briefly display a formula’s step-by-step evaluation. This feature can be accessed by heading to the Formulas tab, clicking on Evaluate Formula in the Formula Auditing group, and then following the prompts to move through the calculation procedure.
Conclusion
In the end, we can conclude that you will learn important aspects of showing formulas in Excel. In my opinion, using the formula with the FORMULATEXT function and the keyboard shortcut are two of the better ways to show formulas in Excel. If you have any questions or feedback regarding this article, please share them in the comment section. Your valuable thoughts will help me generate genuine ideas in my upcoming articles.
Show Excel Formulas: Knowledge Hub
<< Go Back To Excel Formulas | Learn Excel
