Excel: Highlight Cells Automatically with Conditional Formatting

You want cells to color themselves based on what’s in them — flagging overdue dates, large numbers, or duplicates at a glance.

Why: Conditional formatting only changes the appearance of cells, never the data, so it’s safe to experiment with. Excel re-checks the rules every time the values change, so the highlighting stays current on its own.

Step 1: Highlight cells by value or text

These built-in rules cover the most common cases.

1. Select the range you want to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules.
3. Pick the test you need:
   • Greater Than — shade cells above a number (for example, amounts over 1000).
   • Between — shade cells inside a range.
   • Text that Contains — shade cells holding a word (for example, “URGENT”).
   • A Date Occurring — shade dates like Last week or Yesterday.
4. Enter the value, choose a fill from the dropdown (for example, Light Red Fill with Dark Red Text), and click OK.

For ranking, use Home > Conditional Formatting > Top/Bottom Rules to shade the Top 10 Items, Bottom 10%, or values Above Average.

Step 2: Highlight duplicate values

Spot repeated entries without deleting anything.

1. Select the range.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Leave the first box on Duplicate (switch to Unique to flag one-offs instead), choose a format, and click OK.

Every repeated value is now shaded so you can review it.

Step 3: Write your own rule with a formula

When the built-in rules don’t fit, drive the formatting with a formula that returns TRUE/FALSE.

1. Select the range — say A2:A100.

2. Go to Home > Conditional Formatting > New Rule.

3. Choose Use a formula to determine which cells to format.

4. Enter a formula relative to the top-left cell of your selection. For example, to highlight values that appear more than once:

   =COUNTIF($A$2:$A$100,A2)>1

5. Click Format, pick a fill, and click OK twice.

The formula is evaluated for every cell in the range, shifting the relative reference (A2) down each row while the absolute range ($A$2:$A$100) stays fixed.

COUNTIF treats * and ? in the criterion as wildcards. The example above is not literal matching for values containing those characters; use the literal rule in the FAQ when needed.

Step 4: Edit or clear the rules

• Adjust a rule: Home > Conditional Formatting > Manage Rules, select the rule, and click Edit Rule.
• Remove rules: Home > Conditional Formatting > Clear Rules, then Clear Rules from Selected Cells or Clear Rules from Entire Sheet.

FAQ

Can I show bars or color gradients instead of a solid fill? Yes — use Home > Conditional Formatting > Data Bars, Color Scales, or Icon Sets to visualize relative size.

My duplicate rule treats * or ? oddly. COUNTIF also uses those wildcards, so switching to the unescaped COUNTIF example does not solve literal matching. For case-sensitive literal duplicates in A2:A100, use =AND(A2<>"",SUMPRODUCT(--EXACT($A$2:$A$100,A2))>1) as the formula rule. This ignores blanks and compares * and ? literally. If using COUNTIF instead, escape a literal tilde as ~~, an asterisk as ~*, and a question mark as ~?; COUNTIF is case-insensitive.

The wrong cells are highlighted in my formula rule. The formula must be written for the active (top-left) cell of the selection, and you usually want relative row references (like A2, not $A$2) so it shifts down each row. Check your $ signs.

Sources: Microsoft Support — Use conditional formatting to highlight information in Excel

Additional sources: Microsoft — COUNTIF wildcards and escaping