Conditional Formatting With a Formula in Excel
By ExcelReady TeamUpdated 5 min read
The short answer
To use a conditional formatting formula in Excel, select the cells, go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format. Type a formula that returns TRUE or FALSE for the top-left cell, such as =$E2>5, and pick a format. Excel colours every cell where the formula is TRUE.
Key takeaways
- The formula must return TRUE or FALSE, and you write it for the top-left cell of the selection.
- Lock the column with $ (=$E2) to colour whole rows; lock both ($H$1) for one fixed input cell.
- AND, OR, COUNTIF, SEARCH and TODAY all work inside a rule.
- Test the formula in a spare column first: if it shows TRUE in the right rows, the rule will work.
- Manage Rules lets you edit the formula, the Applies to range and the rule order.
On this page+
A conditional formatting formula lets you colour cells by any rule you can write in Excel, not just the ready-made ones like "greater than". The formula returns TRUE or FALSE, and Excel applies the format wherever it's TRUE. This guide shows how to set one up, where the $ signs go, and a set of formulas you can copy for common tasks.
How a formula rule works#
A formula rule asks one question per cell: "is this formula TRUE here?" You write the formula once, for the top-left cell of the range you selected. Excel then adjusts it for every other cell, exactly as if you had copied the formula across and down.
So if you select A2:E9 and type =$E2>5, Excel checks $E2>5 for row 2, $E3>5 for row 3, and so on. If you are new to conditional formatting, start with the beginner's guide to conditional formatting.
The examples use this task list in A1:E9:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Task | Owner | Due | Status | Hours |
| 2 | T-101 | Jamal | 4-Mar-2024 | Open | 6 |
| 3 | T-102 | Sara | 6-Mar-2024 | Done | 3 |
| 4 | T-103 | Wei | 8-Mar-2024 | Open | 2 |
| 5 | T-104 | Jamal | 11-Mar-2024 | Open | 9 |
| 6 | T-105 | Sara | 13-Mar-2024 | Done | 7 |
| 7 | T-106 | Wei | 15-Mar-2024 | Open | 4 |
| 8 | T-107 | Jamal | 18-Mar-2024 | Done | 5 |
| 9 | T-108 | Sara | 20-Mar-2024 | Open | 8 |
How to create a conditional formatting formula rule#
- Select the cells to format, for example A2:E9. Start at row 2 so the header isn't included.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- In the box, type
=AND($D2="Open",$E2>5). - Click Format, pick a fill colour on the Fill tab, and click OK.
- Click OK again.
Rows 2, 5 and 9 turn coloured: T-101, T-104 and T-108 are open and take more than 5 hours. The steps and menu names are the same in Excel for Mac.
Where do the $ signs go?#
The $ signs decide which part of the formula moves as Excel applies it to each cell. This is the same idea as absolute and relative references in normal formulas.
| You want to | Formula pattern | Why |
|---|---|---|
| Colour whole rows based on one column | =$E2>5 | Column E locked, row moves |
| Colour each cell by its own value | =E2>5 | Both move, each cell checks itself |
| Compare every row with one input cell | =$B2=$H$1 | H1 fully locked |
| Compare with a total or average | =E2>AVERAGE($E$2:$E$9) | The range stays fixed |
The row pattern, $E2, is the one people get wrong most often. Without the $, the cell in column A checks A2, column B checks B2, and so on, so only some cells in each row change colour. The guide to highlighting a whole row based on a cell value covers this in depth.
Conditional formatting formula examples#
These formulas all assume the selection starts at row 2. Change the column letters to match your data.
| Goal | Formula | Result on the task list |
|---|---|---|
| Open tasks | =$D2="Open" | 5 rows |
| Open and over 5 hours | =AND($D2="Open",$E2>5) | T-101, T-104, T-108 |
| Done or under 3 hours | =OR($D2="Done",$E2<3) | 4 rows |
| Hours above average (5.5) | =E2>AVERAGE($E$2:$E$9) | 6, 9, 7 and 8 |
| Overdue and not done | =AND($C2<TODAY(),$D2<>"Done") | Depends on today's date |
| Owner matches a name in H1 | =$B2=$H$1 | Rows for that person |
| Repeated task IDs | =COUNTIF($A$2:$A$9,$A2)>1 | None in this list |
| Every other row | =MOD(ROW(),2)=0 | Rows 2, 4, 6, 8 |
| Empty cells | =B2="" | None in this list |
| Text contains a word | =ISNUMBER(SEARCH("urgent",$A2)) | Rows with "urgent" anywhere |
For the overdue rule, if today were 12 March 2024, T-101, T-103 and T-104 would be coloured: they're due before today and still open. TODAY updates each day, so the rule keeps up on its own. For repeated values, the guide to highlighting duplicates has more variations.
Test the formula in a cell first#
The easiest way to debug a rule is to type the same formula in a spare column. In F2, type:
=AND(D2="Open",E2>5)It shows TRUE. Fill it down to row 9 (Windows: Ctrl+D; Mac: ⌘D). You'll see TRUE in rows 2, 5 and 9 and FALSE everywhere else. If the TRUE values are in the right rows, the formula is right. Copy it into the rule, add the $ signs where needed, and delete the test column.
How to edit or delete a formula rule#
Go to Home > Conditional Formatting > Manage Rules. Set Show formatting rules for to This Worksheet to see every rule on the sheet. From here you can:
- Edit Rule to change the formula or the format.
- Change Applies to to make the rule cover more rows, such as
=$A$2:$E$200. - Use the arrows to change the order. Rules higher in the list win when two set the same format.
- Tick Stop If True (Windows) so lower rules don't apply when a rule matches.
- Delete Rule to remove it.
To remove all rules from a range, select it and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Why isn't my conditional formatting formula working?#
Check these common mistakes in order:
- Written for the wrong row. If the selection starts at row 2 but the formula says
$E1, every result is shifted by one row. Write it for the first selected row. - Missing or extra $.
=E2>5on a row rule colours single cells, not rows.=$E$2>5checks only E2, so every row gets the same result. - Formula in quotes. If you type
="=$E2>5"or Excel adds quotes after editing, the rule compares text. Remove the quotes. - Text compared without quotes.
=$D2=Openfails. Use=$D2="Open". - Numbers stored as text.
"9"stored as text isn't greater than 5 in the way you expect. Convert the column to numbers. - A higher rule wins. Another rule sets the same fill first. Check the order in Manage Rules.
For colour based on size rather than a test, see data bars, colour scales and icon sets.
Practise it#
Formula rules combine two skills that tests check all the time: logical formulas and the $ sign. Practise conditional formatting in a live spreadsheet, including colouring whole rows with a formula rule. Then practise absolute references until the $ signs feel natural. Try the rule-test exercise on this page first.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Before making a rule, test its formula. In F2, type a formula with AND that returns TRUE when the status in D2 is Open and the hours in E2 are more than 5.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rule test | ||||||
| 2 | |||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 | |||||||
| 9 | |||||||
| 10 | |||||||
| 11 | |||||||
| 12 |
Tip: AND takes the two tests separated by a comma: D2="Open" and E2>5.
Practise this in a lesson
Frequently asked questions
How do I use a formula in conditional formatting?
Select the cells, go to Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, type a formula that returns TRUE or FALSE, set the format and click OK.
Why is my conditional formatting formula not working?
The most common causes are writing the formula for the wrong row, missing or extra $ signs, and typing the formula in quotes. Write it for the first cell of the selection and check the $ signs.
How do I use IF in conditional formatting?
You don't need IF. The rule already means if this is TRUE, apply the format. Write just the test, such as =$D2="Open" instead of =IF($D2="Open",TRUE,FALSE).
Can conditional formatting use two conditions?
Yes. Use AND when both must be true, such as =AND($D2="Open",$E2>5), or OR when either one is enough, such as =OR($D2="Open",$E2>5).
How do I apply conditional formatting based on another cell?
Refer to that cell in the formula. To compare with one fixed cell, lock it fully, as in =$B2=$H$1. To compare with a cell in the same row, lock only the column, as in =$E2>$F2.