How to Show Formula in Cells Instead of Value in Excel (7 Ways)

In the sample dataset, we have height in cell B5 and width in cell C5. The formula for calculating the area is, Area= Height*Width.

We have calculated the area using the formula =B5*C5.

Method 1 – Using Show Formulas Option from Formulas Tab

Steps:

Click on the Formulas tab on top of your Excel worksheet.
Click on the Show Formulas option under the Formula Auditing group.

The formulas will be shown instead of the values in the formulated cells.

Method 2 – Using Advanced Excel Option

Steps:

Click on the File tab.

Click on the Options section.

In the Excel Options dialogue box, click on the Advanced option and scroll down to the Display options for this worksheet.

Select the worksheet where you want the formulas to be displayed and click on Show formulas in cells instead of their calculated results. Press OK.

It will show the formula instead of values.

Method 3 – Using Keyboard Shortcut

In Press CTRL and ` together anywhere on the Excel worksheets. You will see that the values are turned into formulas.

Press Ctrl and ` again if you want to disable this feature.

Method 4 – Using ‘’=’ Instead of “=” While Applying Formula

If you have a worksheet with a formula applied to it and you want one of the cells to show you the formula that has been applied, click on that specific cell and insert the grave sign (`) just before the equal sign.

Method 5 – Replacing “=” with “’=”

If you applied formulas in your worksheet and you want some or every formulated cell of your worksheet to show the formulas you can replace the “=” sign with a “`=” sign. To do this, press CTRL+H together. You will get a Find and Replace dialogue box. In that dialogue box, press the Replace All option. You will get to see all of your formulas by doing this.

Method 6 – Using Format Cells Option

Steps:

Select the formulated cells of your worksheet and right-click on your mouse button to select the Format Cells Or you can press CTRL+1.
The Format Cells dialog box will open up. Under the Category icon, select the Text option and press OK.

Click on any of those formulated cells and on the Formula Bar press ENTER.

The formula will be shown instead of the values.

Repeat the same steps for each cell you want to show the formula.

Method 7 – Applying FORMULATEXT Function

The FORMULATEXT function allows a user to select a cell containing a specific formula and return the formula of the selected cell as a text string in the cell where the function is applied. It will show the formula in another cell.

Enter the following formula in a new cell where you want to show the formula.

=FORMULATEXT(D5)

D5 = the cell containing the formula that you want to show.

Press ENTER and you will see that the cell has returned the formula as the text string.
Use the Fill Handle tool to Autofill the formula to the cells in which you want to show the formula instead of the value.