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. Click on the "Conditional Formatting" button.
  4. Click on "New Rule..."
  5. Type the formula and then click on "Format..." button
  6. Click on tab "Fill"
  7. Pick a color
  8. Click OK
  9. Click OK button

Download 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