To apply conditional formatting in Excel: select the cells, then on the Home tab, in the Styles group, select Conditional Formatting and pick a rule, for example Highlight Cells Rules > Greater Than. Type the value the rule tests against, choose a format, and select OK. Every cell that meets the condition takes the format, and because it is a rule rather than a one-time paint, a cell loses the format again if its value later stops meeting the condition.
This page covers the whole feature: the Highlight Cells Rules for greater-than, between, and text matches, the duplicate and date rules, the visual formats (data bars, color scales, and icon sets), formula rules that read another cell or color a whole row, and how to manage or clear the rules once they stack up. The ribbon path is the same in the desktop app and in Excel for the web, where the rules open in a side pane instead of a dialog box.
How do I highlight cells greater than a value?
Point to Highlight Cells Rules and the submenu lists Greater Than, Less Than, Between, Equal To, and Text that Contains (plus A Date Occurring and Duplicate Values, both covered further down). Choose one, type the value or values it needs, pick a format, and select OK. Between takes two numbers, for example 100 and 200, and shades everything in that span; Text that Contains takes a string, so Contains “Silver” flags every cell with Silver in it.
| A | B | |
|---|---|---|
| 1 | Item | Amount |
| 2 | Paper | 45 |
| 3 | Cables | 90 |
| 4 | Toner | 130 |
| 5 | Monitor | 260 |
The neighboring Top/Bottom Rules menu shades the extremes instead of a fixed cutoff: Top 10 Items, Bottom 10%, Above Average, and Below Average. The count is adjustable from 1 to 1000, and the percentage from 1 to 100, so Top 10 Items can just as easily mark the top 5 or the top 25.
How do I highlight duplicate values or dates?
Both are quick rules under Highlight Cells Rules. For repeats, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, then pick a format; every value that appears more than once in the selection takes it. This only marks the duplicates, it does not delete anything, so it pairs naturally with the delete step in remove duplicates in Excel, which keeps the first occurrence and removes the later matches. If a deduplication run leaves repeats behind, a formatting language that uses double-byte characters can treat halfwidth and fullwidth as the same; Microsoft’s fallback there is a COUNTIF rule, =COUNTIF($A$2:$A$400,A2)>1, written as a formula rule.
For dates, choose Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring and select a span such as Yesterday, Last Month, or Next Week. One catch before you rely on it: a date rule matches real date values, not dates typed as text. A cell showing 3/8/2026 that is actually a text string will not be caught, because the comparison goes by the value Excel holds, not the characters on screen. Whether a cell holds a true date or looks like one is the subject of Excel date formats.
What are data bars, color scales, and icon sets?
These three turn a column of numbers into something you read at a glance. On the Home tab, select Conditional Formatting, then Data Bars, Color Scales, or Icon Sets, and pick a style from the gallery.
- Data bars fill each cell with a horizontal bar sized to its value, so longer bars mark bigger numbers and shorter bars mark smaller ones. It is the fastest way to spot the high and low points in a long list.
- Color scales shade cells on a gradient. A two-color scale slides the shade between two colors from the lowest value to the highest; a three-color scale adds a middle color for the midpoint, so a cell’s color tells you roughly where its value sits in the range.
- Icon sets sort the numbers into three to five bands and put a small icon beside each cell. In the 3 Arrows set, values in the top band get a green up arrow, the middle band a yellow sideways arrow, and the bottom band a red down arrow.
One shared limit: if a cell holds a formula that returns an error, none of these formats apply to it, the same gotcha the formula rules below run into.
How do I format a row based on another cell’s value?
The quick rules judge each cell by its own value. To format a cell based on a different cell, or to light up an entire row, you write the test yourself. Select the range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and type a formula in the box. Microsoft’s one rule for that formula: “You have to start the formula with an equal sign (=), and the formula must return a logical value of TRUE (1) or FALSE (0).”
To color a whole row, apply the rule to every column of the row range and anchor the column you are testing:
| A | B | C | |
|---|---|---|---|
| 1 | Item | Amount | Owner |
| 2 | Paper | 45 | Lee |
| 3 | Cables | 90 | Ford |
| 4 | Toner | 130 | Ray |
| 5 | Monitor | 260 | Kim |
The formula is written as if it lived in the top-left cell of the range (A2 here), and Excel slides it down and across the rest. That makes the anchor the whole game:
Because the test points at column B while the format lands on the whole row, this is also the pattern for formatting one cell based on another: aim the formula at the cell you want to watch. If a watched cell holds a formula that errors, the rule skips it; wrap the source in IFERROR or an IS function when you need erroring cells to format too. What each error code means is covered in Excel errors explained.
How do I manage or clear conditional formatting rules?
Rules pile up, and two of them can land on the same cell. Open Home > Conditional Formatting > Manage Rules to see, edit, reorder, or delete every rule in the selection or sheet. Order decides the outcome when rules conflict: “A rule higher in the list has greater precedence than a rule lower in the list.” New rules go to the top by default, so keep an eye on where they land, and reorder with the Move Up and Move Down arrows. The Stop If True check box halts evaluation at that rule, which mostly matters for compatibility with older Excel versions that cap the number of rules per range.
To take formatting off, select Home > Conditional Formatting > Clear Rules, then Clear Rules from Selected Cells or Clear Rules from Entire Sheet. To drop a single rule while keeping the others, delete it inside Manage Rules. In Excel for the web the commands sit in the same place, under Home > Styles > Conditional Formatting, but the rules open and are managed in a side pane rather than the dialog boxes.
The short version
- Apply: select the cells, then Home > Conditional Formatting, and pick a rule; the format is live, so cells gain and lose it as their values change.
- Highlight Cells Rules: Greater Than, Less Than, Between, Equal To, and Text that Contains; Top/Bottom Rules shades the extremes (Top 10, Above Average).
- Duplicates and dates: Highlight Cells Rules > Duplicate Values marks repeats without deleting them, and A Date Occurring flags spans like Last Month; a date rule ignores dates stored as text.
- Visual formats: Data Bars size a bar to each value, Color Scales shade a gradient, Icon Sets drop an arrow or light beside each cell; for a mini line or column chart in the cell instead, see sparklines.
- Formula rules: New Rule > Use a formula to determine which cells to format;
=$B2>100across a row colors the whole row, so lock the column and free the row. - Manage and clear: Manage Rules sets order and precedence, Clear Rules removes formatting from the selection or the whole sheet.
