Table of Contents
Excel is one of the most powerful and versatile tools available for data analysis. It is used by businesses of all sizes to store, analyze, and visualize information. One of the most important features in Excel is the ability to apply conditional formatting if a cell contains a formula. This feature enables users to quickly and easily identify cells that contain formulas, which can be extremely useful when performing data analysis tasks. Conditional formatting can help users quickly identify mistakes, visualize trends, and create dynamic reports. In this article, we will discuss the importance of applying conditional formatting if a cell contains a formula, as well as how to do it.
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.
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.