Highlight All Formula Cells Using Conditional Formatting
January 24, 2022 - by Bill Jelen
![Highlight All Formula Cells Using Conditional Formatting Highlight All Formula Cells Using Conditional Formatting](/img/excel-tips/2022/01/highlight-all-formula-cells-using-conditional-formatting.jpg)
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