Show Formulas in Excel Instead of the Values

Once you type in a formula and hit enter in Excel, it will provide a calculated result that the formula will disappear.

However, what if you don’t want to show the calculated values but want to show the cells’ formulas?

Our Excel tutorial will discuss these topics:

  • How you can show Excel Formulas instead of values
  • Ways you can show the selected cells Excel formulas.
  • What you can do whenever Excel shows the Formulas and not the Calculated Values
  • Ways you can print the Excel formulas.

How you can show Excel Formulas instead of values

With these steps, you can show the Excel formulas and not the value:

  • In the ribbon, click on the tab ‘‘Formulas.’’

  • Check the Formula Auditing group and then click on the option Show Formulas.

Once you click on Show Formulas, it will allow the worksheet’s formulas to be visible.

It is a toggle button that can let you click on it once more so that the formulas will get replaced by the calculated result.

As a toggle button, you can click on it once more to replace the formulas with the calculated result.

Shown here is column I with formulas.

When you click on the button ‘‘Show Formulas,’’ the cells will show the formulas and not the value.

It can also be possible for you to use the keyboard shortcut Control +.

Here is the key that you will see at the top-left portion of your keyboard under the key Escape.

Take note that this technique is sheet level.

Because of that, whenever you use the shortcut or Sheet Formulas option, it will only provide the active sheet’s formulas.

Every other worksheet will not get affected.

If you want the other worksheets to show the formulas, you need to check out that sheet and use the ribbon button or this shortcut.

There may be times when there are so many worksheets you need to work on, and you want all of the formulas in the workbook shown.

These are the steps that will show all of the Excel worksheets’ formulas:

  • Check out the ‘‘File’’ tab. In case you have Excel 2007, tap on the Office button.

  • Select ‘‘Options’’.

  • Choose Advanced in the left pane.

  • Check out the right part and scroll down to the section ‘‘Display options for this worksheet.’’

  • Look at the drop-down and choose the worksheet that shows the formulas and not the values.

  • Review the option ‘‘Show formulas in cells instead of their calculated results’’.

  • Click OK afterward.

Like what we discussed, even if this may appear to have more steps than the ribbon’s ‘‘Show Formulas’Formulas’ button or shortcut, this can be useful whenever you have more than one worksheet and you want the formulas to show in every worksheet.

Instead of Only Showing the Selected Cells’Cells’ Value Show the Excel Formulas Instead

So far, the methods that I covered will show the worksheet’s formulas.

Yet if you only want to show the formulas in selected cells, here are templates that will have the formula shown in a cell and the result provided in the other cell:

With these steps, you can show the Excel formulas in selected cells:

  • Choose the cell where you want to have the formula instead of the value shown.
  • Go to Home –> Tap on Find & Select –> Select Replace (or tap on the keyboard shortcut – Control + H).

  • In the dialog box Find and Replace, check out the replace tab. In the ‘‘Find what’’ field, enter ‘‘= in ‘‘Replace with’’ field.

  • Tap on Replace All.

It will provide the formulas in every cell that you selected while the cells that are remaining will stay unchanged.

Note that having a space entered before a formula can turn it into a text string that the space character will be visible before the sign is equal.

You can use the apostrophe before the sign equal to so that the formula will turn into a text string.

Yet, you will not see the apostrophe in the cell as it can only show up in edit mode or the formula bar.

Ways You Can Handle Excel To Show Formulas And Not Calculated Values

There may be times when you find Excel cells that show the formula and not the value.

There are some reasons why this occurs:

  • It may be because of an apostrophe or space character before the formula’s equal to sign. Whenever these are present before the sign equal to, it can make the cell format become text. Moreover, the formula and not the value will show up. Handle this by simply removing it by using find and replace.
  • Whenever the cell applied ‘‘Text’’ formatting, then you enter the formula and tap on Enter. It will continuously show the formula and not the calculated value. Fix this issue by going to the Home tab. Once you get to the Number group, have the formatting changed to General.
  • You could have accidentally pressed on the shortcut Control + or enabled the mode ‘‘Show Formulas.’’ Disable it by clicking on the option ‘‘Show Formula’’ in the tab Formulas or using the shortcut once more.

Ways You Can Print Excel Formulas in Excel

These are the steps on how you can print Excel formulas:

  • Go to the tab Formula.
  • Click on the option Show Formula.
  • Go to File and then click on Print.

With these steps, you will ensure that it will print the formulas instead of the values.

Get to Know How You Can Show Formulas Instead of the Values in Excel with ExcelMaster

Make yourself an Excel expert with us!

Become aware of how you can show formulas instead of values in Excel with ExcelMaster NOW!