Show Excel Formulas Instead of Results

When you're troubleshooting an Excel worksheet, it may help to see the formulas temporarily, instead of the results. With the formulas visible, you can quickly check that the cell references are correct and the formulas are consistent.
Tip: The keyboard shortcut to show or hide the formulas is Ctrl + ' (accent grave, may be above the Tab key on the keyboard).

To view the formulas in Excel 2003:

  1. On the Tools menu, click Options
  2. On the View tab, under Windows Options, add a check mark to Formulas.

To view the formulas in Excel 2007:

  1. On the Ribbon, click the Formulas tab.
  2. In the Formula Auditing Group, click Show Formulas.



0 thoughts on “Show Excel Formulas Instead of Results”

  1. Deb, this is useful. I do something a bit different that meets my needs for blog pictures, presentations, etc.

    I often want to show a formula for a single cell in a neighboring cell. For example, I may want to display the formula for A1 in cell B1. You could copy the formula and paste it into B1 using an apostrophe in front of the equals sign, but that won't update if the formula changes. So I use a simple VBA function:

    Public Function GetFormulaText(Cell As Range) As String
    GetFormulaText = Cell.Formula
    End Function

    There's no error checking at all, but it works as long as you supply only a single cell reference.

  2. A quick way to look for consistent formulas is to show formulas. But change to R1C1 reference style first.

    Then you'd see something like:


    You may be able to see that formula that doesn't belong with a quick glance.

    Tools|Options|General Tab|Check R1C1 reference style
    (xl2003 menus)

  3. In Excel 2007 you can also show formulas directly from the ribbon. Formulas Tab > Formula Auditing Group > Show Formulas

  4. Tim and Dave, thanks, those are great tips.I've got at toolbar button that toggles R1C1 reference style, and that would make it easier to check a column of formulas. Now I've added the GetFormulaText to my collection too.

    Thanks Sridhar, I've changed the Excel 2007 instructions in the post.

  5. 27 march 2009
    i am verymuch interested in excel..

    this informations is very very useful for my everyday home training.
    now my age is nearing 50 years.
    iam working as a tech. in signal deportment in indian railways. iam not taken any training in regular computer course. [email protected]

  6. Thanks, It works:)
    To view the formulas in Excel 2007:
    On the Ribbon, click the Formulas tab.
    In the Formula Auditing Group, click Show Formulas.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.