Use conditional formatting to view formula cells in Excel

There are several methods that you can use to view the formulas in an Excel worksheet that contains hundreds of cells. Find out why Mary Ann Richardson recommends using the conditional formatting method to highlight the cells that have formulas.

If you need to find and check the formulas in a Microsoft Excel worksheet that contains hundreds of cells, you could press [Ctrl][~] to display them all at once. However, you would have to press the shortcut keys again to see if the resulting value for the formula was correct. You could also use the Special command under Edit | Go To: to highlight all of the formula cells in a worksheet, but once you click on one cell to see the underlying formula in the formula bar, the rest of the cells are no longer selected. The method that I recommend is to use conditional formatting to highlight the cells that have formulas. With conditional formatting, the cells stay highlighted even after you click on one cell to view the underlying formula.

To use conditional formatting, create a Visual Basic function that automatically checks for formulas in cells. Follow these steps:

  1. Open the Excel file containing the formulas to be checked.
  2. Press [ALT][F11]
  3. Go to Insert | Module.
  4. Enter the following code at the prompt.
    Function IsFormula(Check_Cell As Range)
            IsFormula = Check_Cell.Hasformula
        End Function
  5. Press [ALT][Q].
  6. Select the cells to be examined for formulas (e.g., select A5:H1000 with A5 as the active cell).
  7. Go to Format | Conditional Formatting.
  8. Select Formula Is from the Cell Value Is drop-down list.
  9. Enter =IsFormula(A5)
  10. Click the Format button.
  11. Choose Yellow in the Color drop-down list.
  12. Click OK twice.

All formulas in the current workbook are highlighted in yellow and will remain so when you reopen the workbook, as long as macros are enabled.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks