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:
the Excel file containing the formulas to be checked.
to Insert | Module.
- Enter the following code at the
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.Hasformula
- Press [ALT][Q].
the cells to be examined for formulas (e.g., select A5:H1000 with A5 as the
to Format | Conditional Formatting.
Formula Is from the Cell Value Is drop-down list.
the Format button.
Yellow in the Color drop-down list.
All formulas in the current workbook are highlighted in
yellow and will remain so when you reopen the workbook, as long as macros are
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.