Table of Contents
Conditional formatting is a feature in Microsoft Excel that allows users to automatically format cells based on certain criteria. One way to apply conditional formatting is by setting a rule that formats a cell based on the value of an adjacent cell. This means that if the value in a specific cell meets a certain condition, the formatting will be applied to that cell. This feature can be useful for visually highlighting important or critical information in a spreadsheet. Users can easily set up and customize these rules to suit their specific needs in order to efficiently manage and analyze data in Excel.
Excel: Apply Conditional Formatting Based on Adjacent Cell
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.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: