See Formulas on an Excel Worksheet

Last week I was testing a client’s workbook, and had filled in all the data entry cells, to make sure everything was working correctly.

Before sending the workbook back to my client, I wanted to clear all the data entry cells. Instead of selecting each cell individually, and clearing it, it would be easier to clear groups of adjacent cells where possible.

However, some cells had formulas, and I didn’t want to accidentally clear any of those. If the formulas are visible, that would prevent the problem.

To see the formulas in Excel 2003:

  • On the Tools menu, click Options
  • On the View tab, add a check mark to Formulas.

FormulaShow01

To see the formulas in Excel 2007

Click the Office button, then click Excel Options

Click the Advanced category

In the Display Options for This Worksheet section, add a check mark to Show formulas in cells instead of their calculated results.

FormulaShow02

Show or Hide Formulas with a Keyboard Shortcut

The keyboard shortcut to show or hide the formulas is Ctrl +   (accent grave, which may be above the Tab key on the keyboard)

___________________

You may also like...

7 Responses

  1. “I wanted to clear all the data entry cells”

    How about Edit, Goto, Special, Constants? or just constants that are Numbers?
    Or pick the final formula, select precedents all levels, then do the above?

  2. derek says:

    If you set up your input cells with data validation, you can Edit, Goto, Special, Validation, All or Same.

    The validation doesn’t even have to be restrictive or visible. You can make it “Allow any value”, then choose an input message, while unchecking the box that displays the input message. Now it’s bothering no one but has tagged the cells you want with a special name you can Goto any time.

    You can choose different names, and for each name have one labeled cell on the sheet you focus on before doing Goto Same Validation.

    None of this helps you with cleaning the sheet if you haven’t first set it up that way, of course :-)

  3. Thanks Patrick, your suggestions would work very well in many workbooks, but this one has labels that are also constants. The data entry is a mixture of numbers and text, and only a few are used as precedents.

    Derek, thanks, selecting data validation cells is an interesting approach, and one that I hadn’t thought of. About 80% of the data entry cells in this workbook have some type of data validation, so it wouldn’t clear everything.

    I’ll keep these ideas in mind when setting up future workbooks.

  4. Contextures Blog » Easily Find and Fix Excel Errors says:

    […] Or even worse, fixing errors in other people’s workbooks. After commenting on my article See Formulas on an Excel Worksheet, Patrick O’Beirne, author of Spreadsheet Check and Control , asked if I’d like a review copy of […]

  5. Terry says:

    I’m using Conditional Formatting in excel 2003 to highlight the formula cells in spreadsheet but unable, Can anyone help.
    Thx a lots.

  6. @Terry, you could use this technique from John Walkenbach’s website to identify formula cells with conditional formatting

  7. Rick Rothstein (MVP - Excel) says:

    @Terry,

    As long as your formula cells do NOT have any Conditional Formatting to begin with, then you can use the first macro below to highlight your formula cells on the active sheet using Conditional Formatting (so that any interior colors assigned to those cells will be preserved) and you can use the second macro to remove that highlight… make sure you do that before you run the first macro again on this, or any other sheet, or before you close the workbook. Copy/Paste all of the code below into a standard Module (making sure the first Dim appears before ANY other code in the Module, even code already existing in it)…

    Dim FormulaCells As Range

    Sub HighlightFormulaCells()
    Dim C As Range
    Set FormulaCells = Cells.SpecialCells(xlCellTypeFormulas)
    For Each C In FormulaCells
    C.FormatConditions.Add xlExpression, , “=COUNTA(” & C.Address & “)”
    C.FormatConditions(1).Interior.ColorIndex = 6
    Next
    End Sub

    Sub ClearHighlightedFormulaCells()
    FormulaCells.FormatConditions.Delete
    Set FormulaCells = Nothing
    End Sub

Leave a Reply

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