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.