Conditional formatting changes how a cell looks (fill, font colour, border) when a condition is true. Select the cells, go to Home > Conditional Formatting, and pick a preset rule, or choose New Rule > Use a formula to determine which cells to format and type a formula such as =$C2>100, which colours every row whose amount in column C is over 100.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Due | Status |
| 2 | 1001 | North | 120 | 2026-03-02 | Paid |
| 3 | 1002 | South | 85 | 2026-03-10 | Open |
| 4 | 1003 | North | 240 | 2026-03-12 | Open |
| 5 | 1004 | East | 60 | 2026-03-20 | Paid |
| 6 | 1005 | South | 150 | 2026-03-25 | Open |
| 7 | 1006 | East | 95 | 2026-03-08 | Open |
Rows 2, 4 and 6 are highlighted. Change C3 to 100 and nothing happens, because the rule says greater than 100; change it to 101 and row 3 lights up. The rule is shown under the grid, and you can click it and edit it.
How to add a conditional formatting rule
The menu has ready-made rules for the common cases:
- Highlight Cells Rules: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values.
- Top/Bottom Rules: Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average (the 10 can be changed).
- Data Bars, Color Scales and Icon Sets: they shade every cell by its size instead of testing a condition.
For anything else, write a formula rule:
- Select the cells to format, starting from the top left cell, so it is the active cell. For the sheet above that is A2:E7.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- Type the formula for the active cell. It must return TRUE or FALSE:
=$C2>100. - Click Format, choose a fill or font colour, and press OK twice.
Excel moves the formula to each cell of the range like a formula filled down and across, so the dollar signs decide what each cell looks at. $C2 means "column C, this row": the column is fixed, the row moves. The rules on this page show the exact same formulas you would type in that box.
Highlight cells greater than a value in another cell
Pointing the rule at a cell instead of typing the number makes the threshold easy to change. The rule below colours only the Amount cells, and compares each with F2; the $ signs in $F$2 keep every cell looking at F2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Limit | ||
| 2 | 1001 | North | 120 | 100 | ||
| 3 | 1002 | South | 85 | |||
| 4 | 1003 | North | 240 | |||
| 5 | 1004 | East | 60 | |||
| 6 | 1005 | South | 150 | |||
| 7 | 1006 | East | 95 |
C2, C4 and C6 are coloured. Type 90 in F2 and C7 joins them; type 200 and only C4 is left. The preset rule Highlight Cells Rules > Greater Than accepts a cell reference in its box too (=$F$2).
Highlight a row based on text
A text condition goes in quotes. =$B2="North" colours each row whose region is North. Comparisons with = ignore upper and lower case, so north matches too. To match text that only contains a word, use SEARCH inside ISNUMBER: SEARCH returns the position of the word, or an error when it is missing, and ISNUMBER turns that into TRUE or FALSE.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Region | Amount | Note |
| 2 | 1001 | North | 120 | Paid on time |
| 3 | 1002 | South | 85 | Late, called twice |
| 4 | 1003 | North | 240 | |
| 5 | 1004 | East | 60 | Paid late |
| 6 | 1005 | South | 150 | Waiting for invoice |
| 7 | 1006 | East | 95 |
Rows 2 and 4 are coloured for North, and D3 and D5 contain "late" (one as Late). Change B7 to North and row 7 joins in. The preset rule Highlight Cells Rules > Text that Contains does the same as the SEARCH rule for the selected cells.
Highlight overdue dates
An open order is overdue when its due date is before today. In Excel the rule is =AND($E2="Open",$D2<TODAY()), and the colours update every day. The sheet below uses a fixed date in G2 instead of TODAY(), so the example looks the same whenever you read it: =AND($E2="Open",$D2<$G$2).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Due | Status | Today | |
| 2 | 1001 | North | 120 | 2026-03-02 | Paid | 2026-03-15 | |
| 3 | 1002 | South | 85 | 2026-03-10 | Open | ||
| 4 | 1003 | North | 240 | 2026-03-12 | Open | ||
| 5 | 1004 | East | 60 | 2026-03-20 | Paid | ||
| 6 | 1005 | South | 150 | 2026-03-25 | Open | ||
| 7 | 1006 | East | 95 | 2026-03-08 | Open |
With 2026-03-15 in G2, rows 3, 4 and 7 are overdue. Row 2 has an earlier date but is paid, so AND keeps it plain. The second rule colours D cells due in the next seven days: none yet. Change G2 to 2026-03-20 and D6 (due 2026-03-25) is coloured by it. Change E3 to Paid and row 3 drops out.
For "due this week" or "last month" without a formula, the preset Highlight Cells Rules > A Date Occurring has those options.
Highlight blank cells, or rows with a missing value
=B2="" is TRUE for an empty cell (and for a formula that returns empty text). Put a $ before the column to colour the whole row when one cell in it is empty: =$C2="". The preset is New Rule > Format only cells that contain > Blanks.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Region | Amount | Status |
| 2 | 1001 | North | 120 | Paid |
| 3 | 1002 | South | Open | |
| 4 | 1003 | North | 240 | Open |
| 5 | 1004 | East | Paid | |
| 6 | 1005 | South | 150 | Open |
| 7 | 1006 | East | 95 | Open |
Rows 3 and 5 are coloured. Type an amount in C3 and its row goes back to plain. =ISBLANK($C2) works here too, but ISBLANK is FALSE for a cell holding a formula that returns "", while =$C2="" is TRUE for both.
Count what a rule colours
A rule never gives a number, but the same condition in COUNTIFS or SUMPRODUCT does. Counting the overdue open orders from the section above, with the date in G2, takes two conditions: Status is Open, and Due is before G2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Due | Status | Today | |
| 2 | 1001 | North | 120 | 2026-03-02 | Paid | 2026-03-15 | |
| 3 | 1002 | South | 85 | 2026-03-10 | Open | ||
| 4 | 1003 | North | 240 | 2026-03-12 | Open | Overdue | |
| 5 | 1004 | East | 60 | 2026-03-20 | Paid | ||
| 6 | 1005 | South | 150 | 2026-03-25 | Open | ||
| 7 | 1006 | East | 95 | 2026-03-08 | Open |
Your turn: In G4, count the open orders whose due date is before the date in G2.
The answer is 3, the number of coloured rows. The criterion "<"&G2 joins the less-than sign to the date in G2; writing "<G2" would compare with the text G2. More on criteria like this on the COUNTIFS page.
Color scales, data bars and icon sets
These three shade every cell by its value instead of switching on a condition. Select the numbers and pick one from Home > Conditional Formatting:
- Color Scales run from one colour for the lowest value to another for the highest (the first one in the gallery colours the highest values green, the middle yellow and the lowest red). Good for a heat map of a table of numbers.
- Data Bars draw a bar inside each cell, as long as its value relative to the others. Under More Rules you can tick Show Bar Only to hide the number.
- Icon Sets put an arrow, flag or traffic light in front of the value, by default in thirds of the range.
To change the thresholds of any of them, go to Home > Conditional Formatting > Manage Rules > Edit Rule, where each colour or icon can be tied to a number, a percent, a percentile or a formula.
Common mistake: the wrong dollar signs
Three versions of the same rule, applied to A2:E7, do three different things:
| Rule | What each cell checks | Result |
|---|---|---|
=$C2>100 | column C of its own row | whole rows coloured by amount |
=C2>100 | the cell two columns to its right: A2 checks C2, B2 checks D2 | only column A follows the amount; B and C are always coloured (a date and a text count as more than 100), D and E never |
=$C$2>100 | always C2 | every row coloured, or none |
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Region | Amount | Due | Status |
| 2 | 1001 | North | 120 | 2026-03-02 | Paid |
| 3 | 1002 | South | 85 | 2026-03-10 | Open |
| 4 | 1003 | North | 240 | 2026-03-12 | Open |
| 5 | 1004 | East | 60 | 2026-03-20 | Paid |
| 6 | 1005 | South | 150 | 2026-03-25 | Open |
| 7 | 1006 | East | 95 | 2026-03-08 | Open |
Every cell is coloured because C2 is 120. Change C2 to 50 and every colour goes away at once. Click the rule and delete the $ before 2 to get =$C2>100, and the colours follow each row again. The same rule about which part to lock applies to formulas you fill down, explained on the absolute reference page.
Frequently Asked Questions
How do I apply conditional formatting based on another cell?
Select the cells to colour, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and write the formula for the first selected cell, pointing at the other cell: =$C2>100 colours the row when C in that row is over 100.
How do I highlight an entire row with conditional formatting?
Select the whole table, not one column, and put a $ before the column letter of the cell you test: =$E2="Open". The column stays fixed while the row number moves, so every cell in a row checks the same cell.
How do I use conditional formatting with multiple conditions?
Combine them with AND or OR inside one formula rule: =AND($E2="Open",$D2<TODAY()) colours unpaid orders past their due date. Several separate rules on the same range all apply; when two set the same format, such as the fill, the one higher in Home > Conditional Formatting > Manage Rules wins.
How do I highlight cells that contain specific text?
Use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, or a formula rule =ISNUMBER(SEARCH("late",A2)), which is TRUE when A2 contains late in any case.
Why is my conditional formatting formula not working?
Usually the dollar signs: =$C$2>100 checks only C2 for every cell, and =C2>100 on a whole row checks a different column in each cell. Also check that the formula was written for the first cell of the range listed under Applies to in Manage Rules.
