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.

ShowFormulas02

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.

ShowFormulas2007

___________________

You may also like...

8 Responses

  1. Tim Mayes says:

    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. Dave says:

    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:

    =SUM(RC[-4]:RC[-1])
    =SUM(RC[-4]:RC[-1])
    =SUM(RC[-4]:RC[-1])
    =SUM(RC[-4]:RC[-1])
    =SUM(RC[-4]:RC[-1])

    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. Sridhar says:

    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. And remember the hotkey to toggle this view:
    Control+’
    (control + the key immediately above your tab key)

  6. kumar says:

    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. kumarsrmdudivdg@gmail.com
    thankingyou

  7. Jorge Esparza says:

    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.

  8. Michael says:

    thank you

Leave a Reply

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