To shade a cell in Excel based on the value of another cell, 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: How to Shade a Cell Based on Another Cell Value in Excel
Suppose we have the following dataset in Excel that contains information about points scored by various basketball players:
Suppose we would like to shade each cell in the Team column where the corresponding cell in the Points column is greater than 25.
To do so, we can highlight the cells in the range A2:A11, 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 =$B2>25 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 Team column that have a corresponding value in the Points column greater than 25 will be shaded:
Note that in this example we use the formula =$B2>25 to shade all cells in column A where the corresponding value in column B was greater than 25.
However, we could also shade values in column B based on text values in column A.
For example, we could use the formula =$A2=”Mavs” to shade all cells in column B where the corresponding value in column A was equal to Mavs.
Note: We chose to use a light green fill for the shading in this example, but you can choose any color and style you’d like.
The following tutorials explain how to perform other common operations in Excel: