![How to use conditional formatting to highlight cells containing formulas](https://static.wixstatic.com/media/2d24e3_d7e2bcf7779e439eae50f1b2ab6270db~mv2.png/v1/fill/w_479,h_201,al_c,q_85,enc_avif,quality_auto/2d24e3_d7e2bcf7779e439eae50f1b2ab6270db~mv2.png)
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:
![How to highlight cells with formula in them](https://static.wixstatic.com/media/2d24e3_b11651d04909485a916a3b7fbc18a75e~mv2.png/v1/fill/w_465,h_190,al_c,q_85,enc_avif,quality_auto/2d24e3_b11651d04909485a916a3b7fbc18a75e~mv2.png)
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.
![Conditional formatting, highlighting all cells in range](https://static.wixstatic.com/media/2d24e3_6319a4e75808484dac10a01e05ab070b~mv2.png/v1/fill/w_465,h_53,al_c,q_85,enc_avif,quality_auto/2d24e3_6319a4e75808484dac10a01e05ab070b~mv2.png)
And the "Plan Attainment" column is dividing our total sales by our business plan, or what we expected the sales to be.
![Conditional formatting to highlight cells with formulas in them](https://static.wixstatic.com/media/2d24e3_0d941bc93d8a45dea4a4e3cb024af58f~mv2.png/v1/fill/w_464,h_56,al_c,q_85,enc_avif,quality_auto/2d24e3_0d941bc93d8a45dea4a4e3cb024af58f~mv2.png)
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:
![Example data table with formulas and hard coded values](https://static.wixstatic.com/media/2d24e3_fd5cd7a7b89f431eb977bb64c11ee405~mv2.png/v1/fill/w_577,h_305,al_c,q_85,enc_avif,quality_auto/2d24e3_fd5cd7a7b89f431eb977bb64c11ee405~mv2.png)
![How to add custom formula to conditional formatting](https://static.wixstatic.com/media/2d24e3_c38fd893f6ce4593b25aedbf789b736d~mv2.png/v1/fill/w_210,h_427,al_c,q_85,enc_avif,quality_auto/2d24e3_c38fd893f6ce4593b25aedbf789b736d~mv2.png)
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:
![Custom formula to highlight all cells containing formulas](https://static.wixstatic.com/media/2d24e3_05fcb505cab141ef89e2d50435e94a9d~mv2.png/v1/fill/w_577,h_416,al_c,q_85,enc_avif,quality_auto/2d24e3_05fcb505cab141ef89e2d50435e94a9d~mv2.png)
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.
![Conditional formatting cells to fill in cell background](https://static.wixstatic.com/media/2d24e3_db47878ca3484088a3d1e0af40dda12b~mv2.png/v1/fill/w_584,h_551,al_c,q_85,enc_avif,quality_auto/2d24e3_db47878ca3484088a3d1e0af40dda12b~mv2.png)
Click ok, and we're done! Now every cell containing a formula in the selected range will be highlighted in yellow.
![Finished example of all cells containing formulas being highlighted in Excel](https://static.wixstatic.com/media/2d24e3_3b3713e04ebd4ead90589e368fc45e3c~mv2.png/v1/fill/w_479,h_201,al_c,q_85,enc_avif,quality_auto/2d24e3_3b3713e04ebd4ead90589e368fc45e3c~mv2.png)