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.

overview image of how to display cell formulas in Excel using Show Formulas command

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.

dataset containing sales amounts and total sales

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.

displaying cell formulas through Show Formulas Command

Method 2 – Show Formulas in All Cells Through Keyboard Shortcut

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

displaying cell formulas through keyboard shortcut

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:
  • Apply AutoFill to get other formulas as well.

displaying cell formulas using FORMULATEXT Function

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.

click options in File tab

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

check necessary settings in Excel options dialog box

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

Cell Formulas on display as an outcome of the settings change in Excel options

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.

Pressing F2 Function Key Shows Cell Formula

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:


displaying Formula in Formula Bar

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.

Double-Clicking on Cell Displays Formula

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.

select desired range and press ctrl and H keys

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

find target and replace it with desired changes through Find and Replace

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

find target and replace it with desired value

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

displaying cell formulas after modifying the original formula

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.

click Format cells after selecting range

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

check for hidden in protection tab

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

protect sheet

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

Check for Protect worksheet and contents of locked cells in protect sheet box

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

formula bar showing blanks instead of formulas

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.

total Sales column showing formulas due to apostrophe in the front

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

Total Sales column showing formulas due to space in the front

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.

Total Sales column showing formulas due to Text format

Things to Remember

  1. 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.
  2. 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.

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF