Conditional Formatting in Excel: Built-in Rules, Formulas and Whole Rows

Conditional formatting changes a cell's fill, font or border based on its value, and updates as soon as the value changes. To apply it, select the cells, click Home > Conditional Formatting and pick a rule, for example Highlight Cells Rules > Greater Than.

The menu rules cover simple cases. As soon as the colour has to depend on another cell (highlighting the whole row of an overdue invoice, for instance), you need a formula rule, explained further down.

The example table

An invoice tracker. All the results below assume today is 2 November 2026.

Built-in rules

Highlight values above a threshold

1. Select C2:C8.
2. Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
3. Type 2000 in the left box, pick a format from the list on the right, then click OK.

C3 (3,400), C5 (2,100) and C6 (4,750) are highlighted. C7 (1,980) stays as it is. The same submenu offers Less Than, Between, Equal To, text that contains a word, dates (yesterday, this week, last month…) and duplicate values.

The threshold can come from a cell: instead of typing 2000, click a cell such as G1. Change G1 and the highlighting follows.

Top values and above average

The Top/Bottom Rules submenu highlights the N largest or smallest values, or those above or below the average. On C2:C8:

• Top 10 Items, with the number changed to 3 in the dialog: C6, C3 and C5;
• Above Average: the average is £2,137.14 (14,960 / 7), so only C3 and C6 are highlighted. C5 (2,100) falls just short.

Spot duplicates

Select B2:B8, then Highlight Cells Rules > Duplicate Values. Harper Ltd (B2, B6) and Clarke & Sons (B3, B8) are highlighted: these clients have more than one invoice. The rule highlights every occurrence, including the first. To delete duplicate rows rather than just spot them, see our guide to removing duplicates in Excel.

Data bars, colour scales and icon sets

These three rules do not pick out certain cells: they show every value in the range relative to the others.

• Data Bars: a bar in each cell, proportional to the value. On C2:C8, the bar in C6 (4,750) fills the cell and the one in C8 (620) is a small fraction of it. A mini chart without leaving the table.
• Colour Scales: a gradient, for example from red (lowest value) to green (highest). Handy on a grid of monthly figures to spot the weak months at a glance.
• Icon Sets: arrows, traffic lights or symbols based on thresholds. By default Excel splits the range into thirds by percentage; for exact thresholds (under 1,000, over 3,000), edit the rule in Manage Rules.

Formula rule: highlight an entire row

Goal: highlight the whole row of every invoice past its due date.

1. Select A2:E8 starting from A2 (the active cell, the one that stays white inside the selection, must be A2).
2. Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. In Format values where this formula is true, type:

   =$D2<TODAY()

5. Click Format, go to the Fill tab, choose a colour, then click OK twice.

Rows 2, 3, 5 and 7 are highlighted (due dates 15/10, 28/10, 20/10 and 01/11, all before 02/11/2026).

Why it works: Excel reads the formula as written for the active cell (A2), then adjusts it for every cell in the range, as if it had been copied. The $ before D locks the column: from A2 to E2, every cell tests D2. The row number is not locked, so on row 5 the formula becomes =$D5<TODAY().

Add a second condition

Rows 2 and 7 are highlighted even though those invoices are paid. To keep only the ones that need chasing, combine both tests with AND:

=AND($D2<TODAY(),$E2<>"Paid")

Only rows 3 (INV-102) and 5 (INV-104) stay highlighted.

Due within the next 7 days

Add a second rule on the same range, with another colour (orange, say):

=AND($D2>=TODAY(),$D2<=TODAY()+7,$E2<>"Paid")

It highlights row 4 (INV-103, due 05/11). INV-105 (30/11) and INV-107 (10/12) are too far off, and INV-106 (01/11) is already past its due date. To show in a nearby column how many days or months an invoice is overdue, calculate the gap between the due date and today with DATEDIF.

Every other row

To shade every other row and make a long list easier to read, the formula is =MOD(ROW(),2)=0. If your data is formatted as a table (Home > Format as Table), banded rows are already there, with no rule needed.

Manage, edit and delete rules

All rules are listed under Home > Conditional Formatting > Manage Rules, which opens the Conditional Formatting Rules Manager.

• At the top, Show formatting rules for: choose This Worksheet to see every rule, not only those on the current selection.
• The Applies to column shows each rule's range. To extend a rule to new rows, change it here (=$A$2:$E$200 instead of =$A$2:$E$8).
• Rules run from top to bottom. If two rules format the same cell in different colours, the one higher in the list wins. The arrows move the selected rule.
• Stop If True: when ticked and the rule is true, Excel does not evaluate the rules below it.

To delete, use Conditional Formatting > Clear Rules, then Clear Rules from Selected Cells or Clear Rules from Entire Sheet. For a single rule, select it in the Rules Manager and click Delete Rule.

When the formatting does not work as expected

Only one column is highlighted, or the colours are shifted

It is nearly always the $. Without it (=D2<TODAY()), cell B2 tests E2, C2 tests F2, and so on. Another cause: a formula written for row 1 (=$D1) when the selection starts on row 2. Every highlight then lands one row off. The formula must always refer to the first row of the range.

Blank rows are highlighted

An empty date cell counts as 0 in Excel, so it is "before today". If the range runs to row 200 to take future invoices, exclude blanks:

=AND($D2<>"",$D2<TODAY(),$E2<>"Paid")

Nothing is highlighted with a text condition

The text must be in straight double quotes and match the cell exactly: "Paid" does not match "Payed" or "Paid" followed by a space. Upper and lower case, on the other hand, make no difference.

Dates or numbers stored as text

A date imported as text (left-aligned) is not compared as a date. Check with =ISNUMBER(D2): FALSE means you need to convert it first.

These rules are often used to make the key figures of an Excel dashboard stand out. To show the same figures visually next to the table, see how to make a chart in Excel. Microsoft reference: Use conditional formatting to highlight information.