Advanced Conditional Formatting in Excel with Formula-Based Rules

Most people use conditional formatting for pretty colours. Used properly, it’s
an analytical tool — it makes problems visible before anyone goes looking for them.

In this video we go deep into formula-based rules. Once you understand that a
rule fires whenever your formula returns TRUE, you can highlight almost anything.
Six real examples, built step by step.

Watch: six formula-based rules in 11 minutes

Prefer reading? The same steps are written out below the video.

Step-by-step

Built-in conditional formatting covers the basics. Formula-based rules let you highlight anything you can describe with a formula. The examples use a sales table in A2:G21, with the sales amount in column G.

How every rule is created

Select the range, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Type the formula, click Format to pick a fill or font, and click OK. The format is applied wherever the formula returns TRUE. Write the formula for the first row of the range. A dollar sign before the column letter makes every cell in a row check the same column.

1. Highlight rows with sales over a target

Select A2:G21 and use:

=$G2>10000

Pick a light green fill. Every row with sales above 10,000 stands out.

2. Highlight duplicate rows

To flag rows where every column matches another row, count the matching rows with COUNTIFS:

=COUNTIFS($A$2:$A$21,$A2,$B$2:$B$21,$B2,$C$2:$C$21,$C2,$D$2:$D$21,$D2,$E$2:$E$21,$E2,$F$2:$F$21,$F2,$G$2:$G$21,$G2)>1

Each pair checks one column. If more than one row matches on all of them, the row is a duplicate.

3. Add zebra stripes

Shade every other row so the table is easier to read:

=MOD(ROW(),2)=0

ROW returns the row number, and MOD(…,2)=0 is TRUE for even rows. A light grey fill works well.

4. Flag overdue dates

Select the date column (A2:A21) and use:

=AND(ISNUMBER(A2),A2<TODAY())

ISNUMBER makes sure the cell holds a date (dates are numbers in Excel), so blanks and text are ignored. The second test checks whether the date is in the past. AND needs both to be true. Pick a light orange fill.

5. Highlight the top 10% of sales

=$G2>=PERCENTILE($G$2:$G$21,0.9)

The 90th percentile is the value that separates the top 10% from the rest. Any sale at or above it is highlighted, here with bold text and a green fill.

6. Combine conditions

Use AND (all must be true) or OR (any can be true). For example, to highlight East-region rows with sales of 3,000 or more, where the region is in column C:

=AND($G2>=3000,$C2="East")

Change the column letters to match your own sheet.

7. Manage your rules

Go to Conditional Formatting > Manage Rules to view, edit, delete or reorder rules, and to change the range each one applies to.

Go further

My Excel Tips & Shortcuts: 100+ Powerful Hacks course collects a hundred time-saving techniques like this one.