See all How-To Articles

Apply Conditional Formatting Based on Adjacent Cell in Excel & Google Sheets

In this tutorial, you will learn how to apply conditional formatting based on the contents of an adjacent cell in Excel and Google Sheets.

 

conditional formatting based on adjacent cell final data

 

Apply Conditional Formatting Based on an Adjacent Cell

In Excel, you can compare two adjacent cells and apply conditional formatting based on the comparison. For the following example, let’s start with two lists of names (in Columns B and C).

 

conditional formatting based on adjacent cell initial data

 

Say you want to highlight (in green) all cells where a cell from Column B has the same value in Column C.

  1. Select a range of data and in the Ribbon, go to Home > Conditional Formatting > New Rule.

 

conditional formatting based on adjacent cell 1

 

  1. In the New Formatting Rule window, (1) select Use a formula to determine which cells to format. In the formula box, (2) enter the formula:
=$B2=$C2

Now (3) click Format. The formula will go row-by-row (down Columns B and C, fixed by the absolute references in the formula) and compare values. If they are equal, the formatting will be applied.

 

conditional formatting based on adjacent cell 2

 

  1. In the Format Cells window, choose a color (green), and click OK.

 

conditional formatting based on adjacent cell 3

 

  1. This takes you back to the New Formatting Rule window; click OK. Before clicking OK, you can Preview and check the formatting.

 

conditional formatting based on adjacent cell 4

 

As a result, all cells that have matching values in Columns B and C are colored green (B3, B6, and B7).

 

conditional formatting based on adjacent cell final data

 

Apply Conditional Formatting Based on an Adjacent Cell in Google Sheets

  1. Select a range of data and in the Menu, go to Format > Conditional formatting.

 

google-sheets-conditional formatting based on adjacent cell

 

  1. In the Conditional format rules window on the right, choose Custom formula is, enter the formula:
=$B2=$C2

Then click Done. This leaves the default formatting (green background color), but if you want to change that, just click on the Fill icon.

 

google-sheets-conditional formatting based on adjacent cell 2

 

Finally, the result is the same as in Excel: All cells with matching values in Columns B and C are green.

 

google sheets conditional formatting based on adjacent cell final data