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. Press with left mouse button on "Conditional formatting" button.
  4. Press with mouse on "Highlight Cells Rules".
  5. Press with left mouse button on "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. Press with left mouse button on 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. Press with left mouse button on the "Conditional Formatting" button.
  3. Press with left mouse button on "New Rule.." to open a dialog box.
  4. Press with left mouse button on "Use a formula to determine which cells to format".
  5. Type the formula. (See below).
  6. Press with left mouse button on "Format..." button and choose a formatting.
  7. Press with left mouse button on 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.

Weekly Blog EMAIL

[newsletter_signup_form id=1]
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