How to Display Cell Formulas in Excel

Learning excel properly will help you regardless of the field you are in. whether you are a teacher who needs to add marks or a student or an auditor who needs to audit a large corporation, learning to display cell formulas is the key to keeping track of the formulas instead of inserting numbers. This can also be useful when investigating why certain formulas are not working correctly or to ensure using formulas correctly.

Due to default settings, when you type a formula in a cell and press enter, Microsoft Excel will show the result of the formula and hides the formula underneath that cell. However, when dealing with a large quantity of data and many formulas, you may need to show the formulas in the cells instead of the end product for numerous reasons.

These reasons can vary from printing, editing the formula, understanding the dependencies, and correcting errors and mistakes to keeping track of data (or the cells containing the data) used in every formula and validating the entered formulas for any possible errors or mistakes. It may also be due to job nature or request by a superior officer or any other reason which needs you to display formulas on excel cells.

Whatever the reason maybe you can easily learn to do so, and we are here to help you by showing you several different methods for displaying the cell formulas in Excel. In order for you to better understand what’s going on in your workbooks by showing and highlighting all the formulas in a sheet.

Methods to Show Cell Formulas In Excel (9 Easy Ways)

If you want to know how to display cell formulas in Excel, then fasten your seatbelts as we’re going to discuss the nine ways to show formulas in Excel.

Method 1: Use the Formula Bar to show the cell formula

The formula bar is the simplest and fastest method to view or edit the entered formula in Excel. The disadvantage is that you can view one formula at a time only. However, you can overlook this disadvantage if you need a quick and easy fix.

Simply open the spreadsheet in which you want to view the formula. Then, select the desired cell containing the formula and you can view the formula in the formula bar below the ribbon.

Method 2: Double-click to display the formula

There is also an option to view a single formula inside the cell instead of viewing it in the formula bar. You only need to click twice.

First double click on the cell which has the formula, and then Excel will itself uncover the formula in the edit mode

Method 3: Use keyboard shortcuts to display Excel formulas

Another easy method will display Excel formulas using the keyboard shortcut. It is extremely helpful while adding numbers and using both keyboard and mouse simultaneously.

Press Ctrl + ` (Grave Accent Key). Grave Accent Key is right above the Tab key on the left side of your keyboard. It usually has 2 marks on it ( ` below and ~ above).

And to reverse the effect, that is to show results again, simply press the (Ctrl+ `) again.

Method 4: Show Formulas command will display the formulas

you can also use this method to show formulas in all the cells in your worksheet by using the Show formulas command from the ribbon located on the top. To apply this method first Open the excel spreadsheet in which you want to show all the formulas. Then, click on the ‘Formulas’ tab and click again on the ‘Show Formulas’ option in the Formula Auditing group which is located in the top right corner.

Excel will now show all the formulas in the worksheet alongside your data.

To again show values in the cells, go back to the ‘Formulas’ tab and adjust the ‘Show Formulas’ command again. This will reverse the action.

Method 5: Show Formulas in Cells In place of Their Results

This method is a little complicated compared to the previous methods, but with this method, Excel can show formulas in several worksheets at once instead of their results. This method also requires a few more steps.

Follow these 6 steps to display formulas in cells instead of their results:

Step 1: Firstly, go to the ‘File’ tab in the ribbon and click on ‘Option’ from the backstage view.

Step 2: once opened, In the Options window, select ‘Advanced’ from the left-side panel.

Step 3: then, On the right side, scroll down to the ‘Display options for this worksheet‘ section. In the drop-down list, select the specific sheet in which you want to display the formulas. You can also select multiple sheets including any sheet from the current workbook together with the sheets from any other open workbook.

Step 4: Now, check the box of ‘Show Formulas in cells instead of their calculated results’ option.

Step 5: once the ‘Show Formulas in cells’ option is enabled in a specific sheet all other worksheets will be unaffected. To apply this effect on other sheets, you also need to select that sheet from the drop-down list and enable this option.

Step 6: Lastly, click ‘OK’ to apply the changes.

This will result in all the formulas being displayed on the specific sheet. If you want to show results instead of the formula again and reverse the action, return to the Advanced options, and uncheck the box ‘Show formulas in cells instead of calculated results’ option.

Method 6: FORMULATEXT Function Displays Formulas in Cells

I use formulas are your preference and it will positively affect your workflow, you can use the FORMULATEXT function to show the formula from a specific cell in another cell in form of a text string.

This method can be used if you want to show both formulas and results at the same time in two separate cells. It has really easy steps that you can learn by trying once or twice.

The first step is to select the cell where you want to display the formula and type this formula:

=FORMULATEXT(F2)

F2 refers to the cell containing the formula. F2 can be any cell in which you have your formula applied. If you mention a cell that does not contain a formula, it will return a ‘#N/A’ error. Hence, make sure you have the right cell number applied in this formula.

If the #N/A error doesn’t seems to go, refer to this article by Microsoft.

Method 7: Select Cells Only to Display Formulas

the methods we have discussed above can help you show formulas in either a single cell or all the cells in the worksheet whereas this method displays formulas only in specific cells of the sheet. This is especially useful in office work.

If you want to display formulas in only selected cells in your excel worksheet, follow these steps:

Let’s assume the spreadsheet below:

Lets retrieve the formulas under the cells by using the following command “CTRL + `“.

Step 1: Choose and select the cells where you require to display the formula instead of the value.

Step 2: Proceed to the ‘Home’ tab and click ‘Find & Select’. then in the ‘find & select’ dialog box select the ‘Replace’ option from the drop-down list. Alternatively, you can also press CTRL + H instead.

Step 3: In the Find and Replace dialog box, under the Replace tab, type (=) in the ‘Find what’ field and (‘=) in the ‘Replace with’ field. Then, click the ‘Replace All’ option.

Step 4: Finally, click ‘OK’ and then ‘Close’ to exit both dialog boxes.

Now only the formulas in the selected cells will be displayed while the other cells (with formulas) would not change. The apostrophe (‘) symbol placed before the equals (=) sign makes the formula a text string and allows us to display it in the cell. 

Additionally, the apostrophe symbol (‘) we added before the = sign in the formula will be hidden in the cells and will only appear in the formula bar when the cells are selected. 

This method might seem complicated however if you follow these steps one by one you will display formulas in specific cells only, which is to your liking.

Method 8: Displaying a formula by formatting the cell as text

Do you need an easier method?

This method to display formulas in cells is to set the cell format to Text. However just one small thing, you need to set the cell’s formatting to Text before entering the formula for this to work. It will not work if the cell formatting is not set to text. There is only one step for this method.

After Selecting the cell where you want to enter the formula and display it, proceed to the ‘Home’ tab, click the drop-down from the Numbers group and select ‘Text’. This is how you can set the cell format to text.

After setting the cell formatting text, you can now enter the formula in the cell and it will remain visible as a text string.

If you wish to display the result instead of the formula, change the formatting of the cell to ‘General’, click on the Formula bar, and then press Enter.

Method 9: Hide Excel formulas completely

Things can be complicated in a workspace. Hence protecting and hiding formulas in order for them to be shown only when required is very important. You might need to hide these formulas If you are sharing an excel spreadsheet that contains some complicated or important formulas and you don’t want other viewers to unintentionally change the formulas and disarray your calculations, you can hide them and safeguard the sheet.

This method will help you to prevent the formulas in the cells of your spreadsheet from being viewed or edited.

The following are some simple steps to follow:

Step 1: Select the desired cells, that is, whose formulas you want to hide. It is up to you if you want to select only specific cells in the sheet or the entire worksheet.

Step 2: Next, go to the ‘Home’ tab, and click ‘Format’ in the Cells group on the right side of the ribbon. Select the ‘Format Cells’ option from the drop-down menu.

Step 3: When the Format Cells dialog box opens up, switch to the ‘Protection’ tab, and check or select the ‘Hidden’ option. Then, click ‘OK’.

Step 4: then, you need to protect the worksheet to completely hide the formulas in the Excel spreadsheet. To do that, proceed to the ‘Review’ tab and click the ‘Protect Sheet’ option in the Protect section.

Step 5: This will open a small Protect sheet dialog box. Here, enters a password in the ‘Password to unprotect sheet’ edit box and make sure the ‘Protect worksheet and contents of locked cells’ option is selected.

Step 6: Under the ‘Allow all users of this worksheet to’ box, select the functions that you want to allow the users to execute. Then, click ‘OK’.

You will be inquired to confirm the password. Re-enter the password in the field and click ‘OK’.

Once these steps are done, all the locked and hidden cells will be password protected. If you select a cell with a formula, the formula bar will be blank and others cannot view formulas as shown below:

If you want to display the formulas, you will need to unprotect the worksheet with the same password.

To unprotect a spreadsheet, head back to the ‘Review’ tab and select the ‘Unprotect Sheet’ option in the Protect section.

Enter the password (that you used to protect the sheet) in the Unprotect Sheet dialog box and click “OK”.

On the ‘Home’ tab, click ‘Format’ and select ‘Format Cells’. Click on the ‘Protection’ tab and uncheck the ‘Hidden’ option. As a result, the worksheet will no longer be protected, and the formulas can be viewed.

How do I fix the “Excel Shows Formulas Instead of Values” issue?

Many of you might have faced the issue of finding that your Excel worksheet shows formulas instead of their calculated results even though you might have not used any of the above methods to display formulas. There are multiple reasons for this, here are some of the most common issues and their fixes.

Disable the ‘Show Formulas’ option or Press “Ctrl + Shortcut”

You may have enabled the ‘Show Formulas’ command from the ribbon or pressed Ctrl + shortcut by mistake. There is a very easy fix for it. To show values again, simply hit the Ctrl + shortcut again or switch the ‘Show Formulas’ option from the Formula tab.

Remove Unnecessary Characters before the Formula

Excel might sometimes evaluate your formula as text and display the formula as text instead of calculating the formula. It could be due to many reasons naming some as to a space character, apostrophe, or any other character before the equal sign in the formula. 

Similarly, if the formula is enclosed in double-quotes or if there is no equal sign before the formula, the formula will be converted into text.

To solve this issue, make sure to remove the leading space, apostrophe, or other characters before the formula.

Change the Cell Formatting

If you arranged the cell formatting to Text and you enter the formula in that cell, Excel will reflect that formula as text and does not calculate it.

To fix this issue, select the formula cell, go to the ‘Home’ tab, click the drop-down in the Number group, and change the formatting to ‘General’. Click on the formula bar or press F2 while the formula cell is selected, and then press Enter.

How to Print Formulas in Excel

To print formulas and not the values in your excel spreadsheet follow these steps

Step 1: Head to the Formula tab and click on the Show Formula option.
Step 2: Go to the File option and select Print.

Conclusions

We hope this guide has solved any issues related to displaying cell formulas in excel. You can now choose any of these methods that best suit your needs and work requirements.

These method ranges from easy to complicate, to specific cell application to entire and multiple spreadsheets application hence you can pick and choose whichever you find best.

You can also apply these easy Fixes for the Excel Showing Formulas Instead of Values issue.