Author: Oscar Cronquist Article last updated on October 02, 2018

The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B and C.

Conditional Formatting formula applied to cell range F3:Y22:

=COUNTIFS($B$3:$B$13,F$2,$C$3:$C$13,$E3)

The first coordinate in cell range B3:C3 is column 1 and row 4, the conditional formatting formula highlights cell F6 because it is column 1 determined by the value in F2 and row 4 based on value in E6.

Explaining Conditional Formatting formula in cell F3

The COUNTIFS function counts the number of records in B3:C13 that match both the column (F$2) and row ($E3) value.

F$2 is 1 and $E3 is 1, no record in B3:C13 matches so cell F3 is not highlighted.

F$2 is locked to row 2 and $E3 is locked to column E so the cell references changes to F$2 and $E4 in next cell below. The dollar sign $ determines if a cell reference is absolute (locked) or relative.

How to apply conditional formatting formula to a cell range

  1. Select cell range F3:Y22.
  2. Go to tab "Home" on the ribbon.
  3. Press with mouse on the "Conditional Formatting" button.
  4. Press with mouse on "New Rule..."
  5. Type the formula and then press with left mouse button on "Format..." button
  6. Press with mouse on tab "Fill"
  7. Pick a color
  8. Press with left mouse button on OK
  9. Press with left mouse button on OK button

Get Excel *.xlsx file

Highlight cells based on coordinates.xlsx

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles