Ever wondered how to make Excel show you the actual formulas instead of just the results? It’s super useful for debugging, understanding how your spreadsheet works, or even just for printing. Here’s how you can do it.
By default, Excel shows the results of the formulas you enter. While this is usually what we need, there are times when viewing the actual formulas is essential. This could be for editing, understanding complex calculations, tracking data used in each formula, or checking for errors. Let’s explore several ways to make those formulas visible!
Method 1: Using the Keyboard Shortcut
The quickest way to toggle between showing formulas and results is using a simple keyboard shortcut that applies to the entire worksheet.
-
Press
Ctrl +(the grave accent key, usually found to the left of the ‘1’ key). This will switch your view from formula results to the formulas themselves.
-
To revert back to displaying formula results, press
Ctrl +again.
Method 2: Using the “Show Formulas” Command
Another straightforward way to display all formulas in your worksheet is by using a command in the ribbon.
-
Open your Excel sheet, then go to the “Formulas” tab in the ribbon.
-
In the “Formula Auditing” group, click on “Show Formulas.”
-
This action will show all formulas in the current worksheet.
-
To switch back to displaying the results, simply click the “Show Formulas” button again.
Method 3: Using Excel Options to Show Formulas
If you need to display formulas across multiple worksheets or want a more persistent way of viewing them, the Excel Options is the way to go.
-
Go to the “File” tab and select “Options” from the backstage view.
-
In the “Excel Options” window, select “Advanced” from the left-side menu.
-
Scroll down to the “Display options for this worksheet” section. Select the worksheet you want to modify from the drop-down.
-
Check the box that says “Show formulas in cells instead of their calculated results”.
-
This will show formulas in the cells of the specific sheet. Remember this setting is sheet-specific, and you need to repeat the process for other sheets if required.
-
Click “OK” to save your changes.
-
To return to showing results, uncheck the “Show formulas in cells instead of their calculated results” option in the same location.
Method 4: Using the Formula Bar
The Formula Bar is the easiest way to see or edit the formula of a specific cell, though you can only view one at a time this way.
-
Select the cell containing the formula you want to view. The formula will appear in the Formula Bar, located just below the ribbon.
Method 5: Double-Clicking a Cell
If you want to see the formula directly within a cell rather than in the Formula Bar, just double-click it.
-
Simply double-click the cell that contains the formula. Excel will then display the formula directly in the cell in Edit mode.
Method 6: Using the FORMULATEXT Function
The FORMULATEXT function allows you to display a formula from one cell as a text string in another cell. This can be helpful if you want to show both the formula and its result side-by-side.
-
In the cell where you want to show the formula, enter the following formula:
=FORMULATEXT(cell_reference)replacingcell_referencewith the cell that contains the formula you want to display. For example, if the formula is in cell F2, you would enter:=FORMULATEXT(F2).=FORMULATEXT(F2) -
The formula will now be displayed as text in the cell, while the original cell shows its result.
Method 7: Displaying Formulas in Specific Cells
Sometimes, you might want to show formulas only in certain cells and not the entire worksheet. Here’s how you can accomplish that.
-
Start by selecting the cells where you want to display the formulas.
-
Go to the “Home” tab, click on “Find & Select,” and choose “Replace,” or just press
Ctrl + H. -
In the “Find and Replace” dialog box, under the “Replace” tab, type
=in the “Find what” field and type’=in the “Replace with” field. Then, click on “Replace All.”
-
Click “OK”, then close the dialog box.
-
Only the selected cells will now show the formulas. The apostrophe
’will not be visible in the cells but will show up in the formula bar.
Method 8: Formatting Cells as Text
Formatting cells as text can also display formulas, but you must apply the formatting before entering the formula.
-
Select the cells where you want to enter and display the formula, go to the “Home” tab, and in the “Number” group, choose “Text” from the dropdown menu.
-
Enter your formula in the cell. It will now be displayed as text.
-
If you need to show the formula’s result, change the cell formatting back to “General”, click on the formula bar and press Enter.
Hiding Formulas Completely
Sometimes, you might want to hide formulas altogether. This will prevent anyone from viewing or changing them.
-
Select the cells with the formulas you want to hide.
-
Go to the “Home” tab, click on “Format” in the Cells group, and select “Format Cells”.
-
In the “Format Cells” dialog box, go to the “Protection” tab, check the “Hidden” box, and click “OK”.
-
Next, you need to protect the sheet to make the hidden formulas truly invisible. Go to the “Review” tab and click “Protect Sheet”.
-
In the “Protect Sheet” dialog box, enter a password. Make sure “Protect worksheet and contents of locked cells” is selected and choose the permissions you want to allow and then click “OK”.
-
Confirm the password, then click “OK”.
-
Now the selected cells’ formulas are completely hidden; the formula bar will be blank when you select these cells.
-
To unhide the formulas, you must unprotect the worksheet. Go to the “Review” tab and click “Unprotect Sheet”.
-
Enter the password and click “OK”.
-
Next, go back to the “Home” tab, click “Format” and select “Format Cells”. Under the “Protection” tab, uncheck “Hidden”, and click “OK”.
-
The formulas will now be visible again.
Common Issues and Fixes
Sometimes Excel may display formulas even when you haven’t intentionally set it to do so. Here are a few potential solutions:
Disable ‘Show Formulas’ option or Press Ctrl +
You might have accidentally enabled the “Show Formulas” command or pressed the Ctrl + shortcut. Simply press Ctrl + again or toggle the “Show Formulas” option under the “Formula” tab to revert the view.
Remove Unnecessary Characters
If Excel thinks your formula is text, it’ll display it instead of calculating it. This usually happens when there’s a space, an apostrophe, or some other character before the = sign in the formula or if the formula is wrapped in double quotes. Just remove the unnecessary characters, including leading spaces, apostrophes, etc.
Change the Cell Formatting
If the cell is formatted as “Text” before entering the formula, it won’t calculate. To fix this, select the cell, go to the “Home” tab, and change the formatting in the Number group to “General”.
Then, select the cell with the formula, click on the formula bar or press F2, then press Enter.
That’s all there is to it! These methods should give you full control over how Excel displays your formulas.