If you have to print a worksheet that contains formulas, or track the formulas, you should know how to display formulas in Excel instead of the numbers.
When you enter a formula into the cell and press Enter, Excel shows the result of the formula, but hides the formula beneath it. But when you are working with large quantities of data and a lot of formulas, you might need to display the formulas within the cells rather than the outputs due to a variety of reasons.
These reasons can include showing formulas to print, changing your formulas, understanding the relationships, repairing mistakes and errors, etc. Formulas displayed in cells will help you keep the track of the information (or the cells that contain the information) that are used in the formula, and also check the formulas you enter for potential errors.
Whatever your reason whatever reason, we’ll show you a variety of ways for showing formulas for cells in Excel.
Table of Contents
Show the Cell Formula using the Formula Bar
Formula bar can be the simplest and fastest method to see or edit formulas entered in Excel. However, it is not ideal as you only have the ability to see only one formula at a.
Open the spreadsheet where you wish to see the formula. If you are aware of which cell has the formula, just choose that cell where you’ll see the formula on the Formula Bar right below the Ribbon.
Show Formula using Double-Click
If you wish to see one formula in the cell instead of using the Formula bar, simply double-click on the cell.
Simply double-click the cell that contains the formula, and Excel will display it in edit mode, as illustrated below.
Display Excel Formulas using Keyboard Shortcut
Another way to display formulas for cells is to use keyboard shortcuts that display all formulas that are entered into a spreadsheet.
Press Ctrl+ (Grave Accent Key). The Grave Accent Key just over that Tab button on the left hand side on your keyboard.
If you wish to display results instead of formulas, press Ctrl+button again.
Display the Formulas by using the Show Formulas command.
Another option to display formulas in every cell of the worksheet you are working on is by using the Show formulas command on the ribbon. Here’s how:
The spreadsheet that you are opening is where you wish to display all formulas. Go to the tab ‘Formulas’ and select the “Show Formulas option under the Formula Auditing group.
Excel will now display all formulas on the worksheet.
To display the values within the cells, go back to the Formulas tab, and switch the “Show Formulas command.
Show Formulas in Cells Instead of Their Results
This approach requires a few additional steps than the earlier methods, however with the method in place, Excel can show formulas on multiple worksheets, instead of their outcomes. Use these instructions to show formulas within cells, instead of the results they produce:
Then, click on the File tab, then choose ‘Option’ from the view of the backstage.
Within the Excel Options window, select Advanced from the left-hand panel.
On the right you can scroll down until you reach the “Display Options available for this worksheet Section. From the drop-down menu, choose the worksheet you would like to show the calculations. It is possible to select any sheet in the currently open workbook, and sheets of any other workbook that is open.
Check the ‘Show Formulas in Cells instead of the calculated results Option.
If you enable the ‘Show Formulas within Cells.. Option in the specific sheet, all other worksheets are not affected. If you want to display formulas on other sheets, you’ll have to select the sheet from the drop-down menu and activate this option.
Then, click ‘OK to apply the modifications.
The formulas will now be displayed on the particular sheet. If you wish to display results instead of formulas return to Advanced options and remove the box that says ‘Show formulas within cells, not calculated results’ option.
Use the FORMULATEXT Function to Display Formulas in Cell
If you prefer using formulas, you can utilize the FORMULATEXT function in order to display the formula of a particular cell within another as text strings. This method is useful when you wish to display both formulas and the result simultaneously in two distinct cells.
First, choose the cell you wish to show the formula, and enter the formula below:
=FORMULATEXT(F2)In this case, F2 is the cell that has the formula. If you are referring to an unrelated cell, which does not include the formula and it returns an error code of #N/A. In the example the formula displayed in cell H2 and its value is displayed in F2.
Display Formulas in Selected Cells Only
The above methods will assist you in displaying formulas in one cell or in all cells of the worksheet, but not just within specific cells on the sheet. If you wish to display formulas only in specific cells of the worksheet, you can apply the find and replace method to accomplish this:
Let’s suppose that we have the following spreadsheet:
In the above example data If we select the Ctrl+shortcut, or the “Show Formulas” option on the menu bar, Excel can display all formulas on the worksheet.
However, we don’t want to do that so we want to show the formulas in J4, J9 and J14, none of the formulas found in column F. To accomplish this you must follow these steps:
Then, select all cells that you wish to show the formula, not the formula.
Click on the ‘Home’ tab, then click ‘Find and Select’, then select the option ‘Replace’ in the dropdown. Press Ctrl+H.
Within the Find and Replace dialog box, which is located under the Replace tab enter the word '=' within the “Find What field, and the word '=' in the field ‘Replace with. After that, click the “Replace All’ button.
Then you click OK, then ‘Close’ to close the dialog box.
Only the formulas in the cells selected will be displayed, while others (with formulas) will not be altered as illustrated below.
In addition, by including an apostrophe (‘) symbol before the ‘equal’ sign by making the formula into a text string, and then displaying it inside the cell.
Additionally, the Apostrophe (‘) symbol we put before the = sign within the formula would hide within the cells, and will only appear on the bar for formulas after you choose the cells.
Format the Cell as Text to Display Formula
Another way to display formulas within cells is to change the format of the cell to Text. But, you must change the formatting of the cell to Text prior to entering the formula in order for this to function.
Choose the cell in which you’d like to enter the formula and then display the formula. Go to the tab ‘Home and click on the drop-down menu from the numbers group, and then select Text..
After you have set the cell’s text to format, you will be able to insert the formula into the cell, and it remains visible as text strings.
If you wish to display the results instead of the formula, switch the formatting of the cell to General Click in the Formula bar, and then hit Enter.
Hide Formulas Completely in Excel
Sometimes it is not a good idea to display formulas, but instead keep them away from the view of anyone else. If you share an Excel spreadsheet with complex formulas and you don’t want other users to accidentally alter the formulas and causing problems in your calculations, it is possible to conceal them and secure the spreadsheet. This will stop the formulas contained in cell from getting seen or altered. This is how it’s done:
Then, choose the cells with formulas that you wish to conceal. You can choose to hide just a few cells on the worksheet or even all of the worksheet. In the following example we’d like to hide formulas within J4, J9 and J14.
Then, click the tab ‘Home’ and then click ‘Format’ within the Cells group located on the right-hand edge of the ribbon. After that, choose the Format Cells’ choice from the dropdown menu.
If you see the Format Cells dialogue box pops up, select the tab ‘Protection’ and then select or check the hidden option. Then, click ‘OK’.
Then, you have to secure the worksheet in order so that you can completely conceal the formulas on Excel. Excel sheet.
To do this, go to the tab ‘Review’ and then click on the “Protect Sheet option under the section titled Protect.
It will open a small dialog box for protecting sheets. Enter an email address in the ‘Password to deprotect worksheet’ editing box, and ensure that the Protect worksheet and the content of cells locked’ selection is chosen.
In the ‘Allow all users of this worksheet to choose the actions you wish to let users perform. Then, click ‘OK’.
You will be required to confirm your password. Enter the password again in the box and then select “OK”.
After this, all cell’s locked or hidden will become password-protected.
If you choose an option that has an equation then the bar for formulas will appear blank, as shown below.
If you want to display the formulas once more then you must remove the worksheet’s protection using that same password.
To remove a spreadsheet from protection return to the tab ‘Review’ and click the option ‘Unprotect Sheet’ in the section titled Protect.
Enter your username and password (you used to secure the sheet using) in the dialog box for Unprotect Sheet and then click ‘OK’.
Then, return to the tab ‘Home Click ‘Format’, then choose the ‘Format Cells’ option. Under the tab ‘Protection remove the Hidden option, then select ‘OK’.
The worksheet will no longer be protected and the formulas will not be hidden.
How do I fix Excel Displaying Formulas instead of Values issue?
Sometimes you might notice the Excel worksheet displays formulas rather than their calculated results , even if you’ve not used any of the methods above for displaying formulas. There are a variety of reasons why this happens, so let’s look at some possible solutions to solve this problem.
Remove the “Show Formulas” option, or press Ctrl+
It is possible that you have accidentally activated the command ‘Show Formulas’ on the ribbon, or you may have pressed the Ctrl+to access the shortcut. To display the values again you just need to press the Ctrl+shortcut once more or switch the option ‘Show Formulas’ on tab Formula tab.
Eliminate Unnecessary Characters prior to the Formula
If Excel believes that the formula is text, it will display the formula in text, instead of computing the formula. This could be due to an apostrophe, space character or any other character that is before the equal sign of the formula. When the formula has been enclosed in double quotes or there is no equal symbol prior to the formula, it will transform the formula into text.
In these cases, be sure that you remove the leading space, apostrophe or any other characters prior to the formula.
Change the Cell Formatting
If you change the cell’s format to text and you insert the formula into the cell Excel is going to treat that formula like text and will not compute it.
To resolve this issue you must select your formula cells, click the tab ‘Home and then click the drop-down menu in the group called Number and then alter the formatting to General..
When the formula cell has been selected Click on the bar for formulas or press F2 and then press Enter.
That’s it.