How to highlight cells in Excel based on value
To highlight cells in Excel based on their value, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules, then pick a rule such as Greater Than. The color follows the value, so it changes when the number changes. This guide covers the built-in rules, rules that use a formula, and how to highlight a whole row.
1. Use a built-in rule
Select the cells, open Home > Conditional Formatting > Highlight Cells Rules, and choose Greater Than, Less Than, Between, Equal To, Text that Contains, or Duplicate Values. Type the value, choose a color, and click OK. Top/Bottom Rules highlight the highest or lowest values, or the values above average, without you typing a number.
2. Show size with bars and color scales
Data Bars draw a bar inside each cell whose length follows the value. Color Scales shade the cells from one color to another, for example green for high and red for low. Icon Sets add arrows or traffic lights. These work well on a column of numbers where the reader wants to scan for the high and low ones.
3. Write your own rule with a formula
For anything the built-in rules cannot do, choose Conditional Formatting > New Rule > Use a formula to determine which cells to format. Write a formula for the first cell of the selection that returns TRUE or FALSE. Excel applies the format to every cell where it is TRUE and adjusts the references for the others.
Example: Sales above target
| A | B | C | |
|---|---|---|---|
| 1 | Sales | Target | Highlight? |
| 2 | 120 | 100 | TRUE |
120 is above 100, so this rule is TRUE and the cell gets the color. Use the same formula as the rule for the selection that starts in A2.
4. Highlight a whole row
To color the whole row when one column meets a condition, select all the rows of the table and write the rule with a dollar sign in front of the column letter, such as =$B2="Late". The $ locks the column so every cell in the row looks at column B, while the row number moves down. This mixed reference is the same idea as in the cell references guide.
5. Find repeated values
A formula rule can flag values that appear more than once. COUNTIF counts how many times the value occurs in the range, and a result above 1 means a repeat.
Example: Is this name repeated?
| A | B | C | |
|---|---|---|---|
| 1 | Name | Repeated? | |
| 2 | Ana | TRUE | |
| 3 | Ben | ||
| 4 | Ana | ||
| 5 | Cam | ||
| 6 | Ben | ||
| 7 | Dee |
Ana appears twice in the range, so the count is 2 and the rule is TRUE.
Common mistakes
Writing the rule for the wrong cell. The formula is read as if it sat in the top-left cell of the selection. A rule written for another cell highlights the wrong rows.
Fix: Select the range first, and write the formula for its first cell.
Missing dollar signs. Without $, a row rule slides to the right and checks the wrong column.
Fix: Lock the column with $, as in $B2, and leave the row free.
Rules piling up after copy and paste. Pasting cells copies their rules too, and the list becomes long and hard to read.
Fix: Open Conditional Formatting > Manage Rules to read, reorder or delete rules. Use Clear Rules to remove them all.
Frequently asked questions
How do I highlight cells that contain specific text?
Choose Highlight Cells Rules > Text that Contains and type the text. For more control, use a formula rule with COUNTIF or SEARCH.
Does the highlight change when the value changes?
Yes. Conditional formatting is recalculated with the sheet, unlike a fill color you apply by hand.
How do I highlight blank cells?
Choose New Rule > Format only cells with > Blanks. Or use a formula rule such as =A2="".
Related functions
- COUNTIFCOUNTIF counts the cells in a range that meet one condition: a text, a number, a comparison like ">100" or a wildcard.
- ANDAND returns TRUE only when every condition is true.
- OROR returns TRUE when at least one condition is true.
- IFIF checks a condition and returns one value if it's true and another if it's false.
More on conditional formatting
The guide covers the rules and formula rules. This piece adds the order in which rules are applied, how to highlight dates, and how to build one rule that uses another cell as its threshold.
What happens when rules overlap
Several rules can apply to one cell. Open Conditional Formatting > Manage Rules to see the list from top to bottom. When two rules set the same property, such as the fill color, the rule higher in the list wins. Move the most important rule to the top with the arrows. Tick Stop If True on a rule when later rules should be ignored for the cells it matches.
Dates and thresholds that can change
Hard-coding a number such as 100 in a rule makes it hard to change later. Put the threshold in a cell, such as F1, and refer to it with a locked reference. Then changing F1 recolors the whole table.
=A2>$F$1Dates work the same way. A rule such as =$C2<TODAY() highlights rows whose due date is already past, and it recalculates every day. For the next seven days, use two conditions with AND.
Keep it readable
Too many colors teach the reader to ignore them. Use one highlight color per meaning, and write the meaning in a note near the table. Check the result in black and white, because many readers cannot tell red from green. The lesson on [conditional counting](/learn/conditional-counting) shows how to count the highlighted rows with a formula.