To highlight any cell that contains a formula in an Excel workbook, there is a simple conditional formatting formula that can be used.
Contents:
Conditional Formatting Formula
This formula will work on any selected range, no edits required.
= ISFORMULA(INDIRECT(ADDRESS(ROW(), COLUMN())))Alternative Formula without INDIRECT:
You must replace A1 in this formula with the top-left most cell in your selected range. This formula is more efficient than the above one across large workbooks.
= ISFORMULA(A1)Explanation
How to Conditionally Format a Cell if it Contains a Formula
Whether you want are looking to debug a workbook or gain a better understanding of how a financial model works, highlight all of the cells containing a formula will allow you to easily see what cells are static, and what cells contain a formula.
Let's start with a simple table outlining our company's monthly sales:
You can't tell by just looking at it, but two of these columns are actually formulas, and not static or hardcoded numbers.
Total sales is a formula multiplying quantity and price together.
And the "Plan Attainment" column is dividing our total sales by our business plan, or what we expected the sales to be.
If we want to highlight these two columns to make it clear that formulas are present in these two columns, we can use conditional formatting to highlight every cell where a formula is present.
All we need to do is select the range that we want to highlight:
And then under conditional formatting (found in the "Home" tab) and click New Rule.
Then, in the New Formatting Rule menu, we'll select "Use a formula to determine which cells to format" and drop in our formula.
= ISFORMULA(INDIRECT(ADDRESS(ROW(), COLUMN())))note: this formula uses INDIRECT and will be slow when evaluating large amounts of cells, you may want to use the alternative formula listed above for larger workbooks
After dropping in the formula, we can click the Format button near the bottom of the menu:
Which will allow us to set specific formatting rules wherever the formula we entered is found to be TRUE. In this case, we are going to fill in the background of the cell in yellow, to highlight any cells that contain a formula.
Click ok, and we're done! Now every cell containing a formula in the selected range will be highlighted in yellow.
