Highlight All Formula Cells Using Conditional Formatting

January 24, 2022 - by Bill Jelen

Problem: I need to audit a worksheet created by a former co-worker. How can I mark all formula cells?

Strategy: There are two great ways to do this. The first is a one-time snapshot of current formulas. The second will continually update so you can see if someone types a plug number where a formula should be.

To do a one-time snapshot go to Home, Find & Select, Formulas. Go to the Font color or Fill color and apply a color. All of the formula cells will be highlighted.

To have the formulas be highlighted using conditional formatting, follow these steps:

• ---

  ---

  1. Select the used range of your worksheet. Note the top left cell (usually A1, but it might be something else).

• 2. Home, Conditional Formatting, New Rule, Use a Formula. Type this formula: =ISFORMULA(A1). Adjust the A1 if the top left cell of your selection is not A1.

• 3. Click Format and apply a fill color. Click OK.

All formulas will be highlighted. If a formula gets replaced by a constant, the fill color will disappear.

This article is an excerpt from Power Excel With MrExcel

Title photo by Steve Johnson on Unsplash