How can I apply conditional formatting in Excel to highlight cells that contain formulas?

By /

Conditional formatting in Excel allows users to apply specific formatting rules to cells based on certain conditions. This feature can be used to highlight cells that contain formulas, making it easier to identify and differentiate them from other cells. By setting up a rule that identifies cells with formulas, the user can choose to apply a different font color, fill color, or other formatting options to make the cells stand out. This can be particularly helpful when working with large datasets, as it helps to quickly identify and verify the presence of formulas in different cells.

Excel: Apply Conditional Formatting if Cell Contains Formula

To apply conditional formatting to cells in Excel that contain a formula, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.

The following example shows how to use this option in practice.

Example: Apply Conditional Formatting if Cell Contains Formula

Suppose we have the following dataset in Excel that shows the number of points scored and minutes played for various basketball players:

Suppose we create a new column called Points per Minute that we calculate using the following formula in cell D2:

=B2/C2

We then click and drag this formula down to each remaining cell in column D:

Now suppose that we would like to apply conditional formatting to any cell in the dataset that contains a formula.

To do so, we can highlight the range A2:D11, then click the Conditional Formatting dropdown menu on the Home tab and then click New Rule:

In the new window that appears, click Use a formula to determine which cells to format, then type =ISFORMULA(A2) in the box, then click the Format button and choose a fill color to use.

Excel apply conditional formatting if cell contains formula

Once we press OK, all of the cells in the range A2:D11 that contain a formula will be highlighted:

No other cells are highlighted because no other cells in the dataset contain a formula.

Note: We chose to use a light green fill for the conditional formatting in this example, but you can choose any color and style you’d like for the conditional formatting.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. How can I apply conditional formatting in Excel to highlight cells that contain partial text?
  2. How can I apply conditional formatting in Excel to highlight dates that are older than 1 year?
  3. How to Apply Conditional Formatting to Blank Cells in Excel?
  4. How can I use conditional formatting in Google Sheets to highlight cells that are greater than or equal to a certain value?
  5. How do I apply conditional formatting to cells using VBA?
  6. How to apply conditional formatting if two cells are not equal
  7. How can I use conditional formatting in Excel to highlight even and odd values in a data set?
  8. How can I apply conditional formatting in Excel to display Yes/No values?
  9. How can I apply conditional formatting to a cell based on the value of an adjacent cell in Excel?
  10. How can I apply conditional formatting to dates within 6 months in Excel?