Tutorial Excel : Show Formulas Instead of Results

You can more easily review and troubleshoot a worksheet by changing its display to show the formulas in each cell instead of those formulas’ results. If you want to check the formula for a particular cell, you cannot examine the formula just by looking at the cell because Excel displays the result of the formula instead of the formula. You must click the cell so that Excel displays the formula in the Formula bar.

That is fine for a single cell, but what if you need to check all the formulas in a particular worksheet? You could simply click each cell that contains a formula, but that is impractical in a sheet with dozens of formulas, and it does not enable you to easily compare one formula with another to look for errors or anomalies. Instead, you can change the worksheet view to display the formulas in each cell rather than the formula results.

How to Show Formulas Instead of Results

1. Switch to the work sheet that contains the formulas you want to display.
2. Click the File tab.

3. Click Options.

The Excel Options dialog box appears.
4. Click Advanced.
5. Click to select the Show Formulas In Cells Instead of Their Calculated Results option.
6. Click OK.

● Excel displays theformulas instead of theirresults.
Note: You can also toggle thedisplay between formulas andresults by pressing Ctrl+`.

Try This!

The technique you learned in this section applies only to the current worksheet. If you
want to view the formulas in every sheet in a workbook, run the following VBA macro:
Sub ToggleFormulasAndResults()
   Dim win As Window
   Dim wv As WorksheetView
   For Each win In ActiveWorkbook.Windows
      For Each wv In win.SheetViews
         wv.DisplayFormulas = Not wv.DisplayFormulas
      Next ‘wv
   Next ‘win
End Sub
See Chapter 11 to learn how to add and run a VBA macro in Excel.

Categories: Excel 2010