Highlighting With Conditional Formatting: Duplicates, Color Coding & Alternate Rows

The highlight group of conditional rules: spotlight duplicates, color-code categories, and shade alternate rows. You can build all three by hand — or ask AI to do it in one pass. Everything updates live as your data changes.

Table of Contents

• Highlight Duplicates
• Color Coding
• Alternate Rows
• Do It With AI

Highlight Duplicates

This is the classic duplicate finder:

1. Select the column containing your values.
2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
3. Confirm the highlight color and click OK.

Every value that appears more than once gets colored instantly. To highlight the opposite — the values that appear only once — open the same dialog and change the dropdown to Unique.

To compare across two columns, select both columns' ranges before opening the rule — the duplicate check runs across them.

Color Coding

Color coding uses a text rule per category, so each value gets its own color:

1. Select the column containing the categories.
2. Go to Home → Conditional Formatting → Highlight Cells Rules → Text that Contains.
3. Type the value, for example urgent, and pick a fill color.
4. Repeat for each category you want to color.

The result is a quick visual legend: red for urgent, green for approved, and so on.

For color by a number instead of text, use a formula rule:

1. Go to New Rule → Use a formula to determine which cells to format.
2. Enter a formula like =B2>100 (the formula refers to the top-left cell of your selection).
3. Click Format to pick the fill, then OK.

A formula rule lets you decide exactly which cells qualify.

Alternate Rows

Shade every other row for scanability using a simple formula rule:

1. Select the whole data range (values only, no header).
2. Go to New Rule → Use a formula to determine which cells to format.
3. Enter the formula =MOD(ROW(),2)=0.
4. Click Format, pick a light fill, then OK.

MOD(ROW(),2) returns the remainder when the row number is divided by 2 — so even rows match and get shaded, odd rows stay plain. Unlike a manual paint job, the rule stays correct if the data moves. Keep in mind the rule only covers the Applies To range — to shade rows you add later, widen that range in the Rules Manager.

Do It With AI

All three highlights are natural to describe, so Copilot can build them in one pass:

"Highlight duplicate IDs in column A." "Color-code the Status column — green for Done, yellow for Pending." "Shade alternate rows in this range."

Just say what should stand out; Copilot turns each sentence into a rule. You can still inspect and tweak the results in the Rules Manager, or ask "also highlight unique IDs" to adjust the existing rule.