Author: Oscar Cronquist Article last updated on November 05, 2018

Excel lets you easily highlight values based on a condition you specify, with a built-in formatting or a custom formatting.

How to apply Conditional Formatting to values equal a condition

  1. Select cell range containing values you want to highlight.
  2. Go to tab "Home" on the ribbon if you are not already there.
  3. Click "Conditional formatting" button.
  4. Click on "Highlight Cells Rules".
  5. Click "Equal to..."
  6. A dialog box appears that lets you specify the date condition and the formatting.
  7. Pick a prebuilt formatting or use a custom format to create a new one.
    • Light red Fill with dark red text
    • Yellow fill with dark yellow text
    • Green Fill with dark green text
    • Light red fill
    • Red text
    • Red border
    • Custom format...
  8. Click OK button.

Highlight rows equal to a condition

You need to use a formula instead of the prebuilt ones in order to highlight the entire row if a cell in column B meets the condition in cell F4.

  1. Go to tab "Home" on the ribbon.
  2. Click the "Conditional Formatting" button.
  3. Click "New Rule.." to open a dialog box.
  4. Click "Use a formula to determine which cells to format".
  5. Type the formula. (See below).
  6. Click "Format..." button and choose a formatting.
  7. Click OK button twice.

Conditional formatting formula

=$B3=$F$4

Explaining conditional formatting formula

The CF formula changes from cell to cell depending on how you set it up, a cell reference may be absolute or relative.

The dollar sign makes a cell reference locked (absolute), however, a cell reference may also have two dollar signs locking both the column and row. This is the case with $F$4, the condition cell reference never changes.

Cell reference $B3 is only locked to column B, when the CF moves to cell C3 cell ref $B3 is still $B3. This highlights cell C3, see example image above. The next cell is D3 and $B3 is still $B3 highlighting cell D3.

Cell B4 is on the next row and $B3 changes to $B4, cell $B4 is not equal to the condition in cell F4 so this cell is not highlighted and so on.

Related articles