How to Apply Conditional Formatting Based on Adjacent Cell in Excel?

By /

To apply conditional formatting to cells in Excel based on the values in adjacent cells, you can use the New Rule option under the Conditional Formatting dropdown menu within the Home tab.

The following examples shows how to use this option in practice with two different scenarios:

1. Apply conditional formatting based on text value in adjacent cell

2. Apply conditional formatting based on numeric value in adjacent cell

Let’s jump in!

Example 1: Apply Conditional Formatting Based on Text Value in Adjacent Cell

Suppose we have the following dataset in Excel that shows the position and points scored by various basketball players:

Now suppose we would like to apply conditional formatting to each cell in the Points column where the adjacent cell in the Position column is equal to “Forward”.

To do so, we can highlight the cells in the range B2:B12, 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 =$A2=”Forward” 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 B2:B12 that have a value of “Forward” in the adjacent cell in the range A2:A12 will be highlighted:

Example 2: Apply Conditional Formatting Based on Numeric Value in Adjacent Cell

Now suppose we would like to apply conditional formatting to each cell in the Position column where the adjacent cell in the Points column is greater than 20.

To do so, we can highlight the cells in the range A2:A12, 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>20 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:A12 that have a value greater than 20 in the adjacent cell in the range B2:B12 will be highlighted:

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.

x

Related terms:

  1. How to apply conditional formatting to a cell based on the value in another cell?
  2. Excel: Apply Conditional Formatting if Cell Contains Partial Text
  3. Excel: Apply Conditional Formatting if Cell Contains Formula
  4. What is Excel: Apply Conditional Formatting if Cell Contains Text
  5. Excel: Apply Conditional Formatting to Second Highest Value
  6. Excel: Apply Conditional Formatting to Yes/No Values
  7. Excel: Apply Conditional Formatting to Dates Older Than 1 Year
  8. How to Apply Conditional Formatting to Dates within 6 Months in Excel
  9. How to Apply Conditional Formatting to Blank Cells in Excel?
  10. How to Apply Conditional Formatting if Between Two Values