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

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

---

INDEX and MATCH – multiple criteria and multiple results

This article demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel […]

Match two columns

This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]

How to use the DSUM function

The DSUM function adds numbers in a database that meets a condition or criteria. Formula in cell B15: =DSUM(B5:D12, 3, B2:D3) […]

How to use the EXPONDIST function

The EXPONDIST function calculates the exponential distribution representing an outcome in the form of probability. This function is in the compatibility […]

How to use the FORECAST function

The FORECAST function calculates a value based on existing x and y values using linear regression. Use this function to […]

Partial match for multiple strings – AND logic

This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]