How to Display Cell Formulas in Excel (6 Methods)

The image below displays the cell formulas in the Excel worksheet using the Show Formulas command in the Formulas tab.

Consider a dataset that contains sales amounts of different products in January, February, and March, as well as the total sales column where we used the SUM formula to combine these three months.

Method 1 – Use Show Formulas Command to Display All Cell Formulas in Excel

To see formulas instead of results:

Go to the Formulas tab.
Click on the Formula Auditing drop-down.
Select Show Formulas.
You’ll see the formulas in the Total Sales column.

Method 2 – Show Formulas in All Cells Through Keyboard Shortcut

Press the Ctrl + ` keys together to display the cell formulas in Excel.

Method 3 – Insert FORMULATEXT Function to Display Cell Formulas in Excel

The FORMULATEXT function returns the formula present in a cell.

Make a helper column G.
In cell G5, we insert the formula:

=FORMULATEXT(F5)

Apply AutoFill to get other formulas as well.

The FORMULATEXT function returns a #N/A error if the reference cell is in another workbook that is not opened in the background.

Method 4 – Check Advanced Excel Options to Show Cell Formulas

Go to the File tab.
Click Options.

In the Excel Options dialog box, go to the Advanced tab and check Show formulas in cells instead of their calculated results.
Press OK.

You’ll see the formulas in the Total Sales column.

Method 5 – Temporarily Display Excel Formula in a Cell

Method 5.1 – Press F2 Function Key to Show Cell Formula

Select the F5 cell and press the F2 key.
The cell will display the sum result again when you click elsewhere.

Method 5.2 – View Cell Formula in Formula Bar

You can also see the formula in the Excel Formula Bar. Click on the desired cell and the formula bar shows the formula. Here the F5 cell has the formula:

=SUM(C5:E5)

Method 5.3 – Double-Click on Cell to Display Formula

Another way to display formulas temporarily is by double-clicking on the cell. The above image shows the formula in the F5 cell by double-clicking on it.

Method 6 – Modify Formula to Display Cell Formulas in Excel

Now we will show you some modifications of the formulas to display alongside the results:

Copy the F5:F14 range through the Ctrl + C keys.
Paste them into G5:G14 with Ctrl + V keys.
Select the G5:G14 range and press Ctrl + H.

The Find and Replace dialog box pops out. Insert “=” for Find what and “ =” for Replace with (there’s a space before =).
Press Replace All.

You can also use an asterisk ‘ instead of a space.

Therefore, you’ll see the formulas beside the Total Sales column.

How to Hide Cell Formulas in Excel

Let’s hide the cell formulas present in F5:F14:

Select the range.
Go to the Home tab.
Click the Cells drop-down.
Click Format drop-down.
Choose Format Cells.

The Format Cells dialog box pops out. Check Hidden and press OK.

Go to the Review tab and select Protect drop-down.
Click on Protect Sheet.

The Protect Sheet dialog box appears. Check for Protect worksheet and contents of locked cells. You can enter a password.
Press OK.

Click on any cell that has the formula in it. The formula bar will show blanks, hiding the cell formulas in Excel.

Why Is Excel Showing Formula Instead of Result

Sometimes you may see that Excel shows formulas instead of results. Some of the reasons are:

1. If the Show Formulas mode is enabled, you’ll see the formulas. To disable it, go to the Formulas tab > click the Formula Auditing drop-down > select Show Formulas. Or you can press the Ctrl and ` keys together. It’ll give back the calculated values.

2. If you accidentally put an apostrophe (‘) before the = sign in the formula, it’ll only display the formulas.

3. Similarly if you put a space before the sign, you’ll see the formulas only.

4. If you enter a formula in the cell that is set to the Text format, the cell won’t show the calculated value. Because Excel assumes the formula to be a text string. So, change the format to General to get the formula results.

Things to Remember

All the above methods work for the current Excel worksheet only. That means it’ll display the cell formulas in the active worksheet. You have to repeat the method for other worksheets too if you want to display cell formulas there.
Formula Bar is the dedicated place to show cell formulas in Excel. If the formula bar shows blank after clicking a cell that contains the formula, it’s likely that the sheet is protected. Unprotect the worksheet first to display the formulas.

Download Practice Workbook

You are recommended to download the practice workbook and practice along with it.

Display Cell Formulas.xlsx

<< Go Back To Show Excel Formulas | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!