How to Highlight Duplicate Values in Excel (Four Methods Compared)

The "show me the duplicates" question

You're looking at a column. You suspect there are duplicates. You don't want to delete them yet, you want to see them first. Maybe they're legitimate (same customer with two orders). Maybe they're errors (same record imported twice). Either way, the visual is the deciding factor before you act.

This guide compares the four ways to highlight duplicates in Excel. Each has a use case where it's the right answer.

TL;DR — Key takeaways

• Conditional Formatting is fast and built-in but the rule lingers in the workbook and recalculates on every edit.
• =COUNTIF() with a fill is precise but leaves a helper column you have to clean up.
• Power Query is overkill for a one-off but powerful for recurring cleanup.
• A VBA macro is the right answer for a one-shot highlight on a snapshot, with clean Ctrl+Z to revert.
• All four methods are case-insensitive by default. To compare with case sensitivity you need an extra step.

Method 1: Conditional Formatting (fastest, but stays around)

Excel's built-in highlight-duplicates rule.

Steps:

1. Select the range you want to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Pick a color from the dropdown.
4. Click OK.

Every duplicate cell turns the color you picked.

The catch: the rule sticks around. If you edit the data later, Conditional Formatting re-applies on the fly. Sometimes that's what you want; often it's confusing because the highlight changes as you type. To remove the rule, go to Conditional Formatting > Manage Rules and delete it (which is two extra clicks most people skip).

Method 2: COUNTIF with a fill (precise, leaves a helper column)

Add a helper column with =COUNTIF() and fill colors based on the result.

Steps:

1. Insert a helper column next to your data.
2. In the first row, drop =COUNTIF($A$2:$A$1000, A2). Adjust the range to match your data.
3. Fill down.
4. Apply Conditional Formatting to the helper column: highlight cells where value is greater than 1.
5. (Optional) Apply the highlight to the original column instead by using Conditional Formatting with a formula rule referencing the helper.

This is more typing but lets you see the count of each duplicate, not just whether the cell is a duplicate. You'll know if a value appears 2, 3, or 5 times.

The catch: you have a helper column you need to delete or hide. Forgotten helper columns are a top cause of "what's this column for" confusion when someone else opens your workbook six months later.

Method 3: Power Query group-and-count

Power Query (Data > Get & Transform > From Table/Range) has a Group By step that gives you a count per value. Filter to count > 1 and you have your duplicates as a fresh table.

This is the right answer if you have a recurring duplicate-detection workflow that processes new data weekly. Set up the query once, refresh it whenever new data arrives.

The catch: for a one-off check, building a Power Query is overkill. The setup time is longer than just running Conditional Formatting.

Method 4: A free VBA macro (one click, plain values, easy to revert)

Download Highlight Duplicate Values. It's a free .xlsm with one macro.

Steps:

1. Select the cells to check.
2. Press Alt + F8, pick HighlightDuplicates, click Run.
3. Every duplicate cell gets a light red fill with dark red text.
4. Done. Hit Ctrl/Cmd + Z to revert when you're done eyeballing.

The macro runs once, applies static colors, and doesn't leave a Conditional Formatting rule lying around. The Ctrl+Z revert is what makes the workflow clean: highlight, look, decide what to do, undo. No helper column, no leftover rules.

Comparison: which method to use

Method	Setup time	Result type	Lingers in workbook	Best for
Conditional Formatting	10 sec	Live rule	Yes	Quick check on data you'll keep editing
COUNTIF + fill	1 min	Helper column	Yes	When you need the actual count per value
Power Query	5 min initial	Separate query result	Yes	Recurring weekly/monthly checks
VBA macro	5 sec (after install)	Static color	No (Ctrl+Z reverts)	One-off snapshot audits

What "duplicate" actually means in Excel

By default, every method above is case-insensitive. So Acme and ACME are duplicates. This is almost always what you want for human-entered data where capitalization is inconsistent.

If you need case-sensitive matching:

• Conditional Formatting: use a custom formula like =EXACT($A2, ANY_OTHER_CELL) instead of the built-in duplicate rule.
• COUNTIF: swap to =SUMPRODUCT(--EXACT($A$2:$A$1000, A2)) > 1. SUMPRODUCT with EXACT is case-sensitive.
• Macro: edit the .bas source and change seen.CompareMode = 1 (vbTextCompare) to seen.CompareMode = 0 (vbBinaryCompare).

After highlighting: what to do with the duplicates

Highlighting is step 1. Step 2 depends on whether the duplicates are legitimate or errors.

• Legitimate duplicates (same customer, two orders): leave them. The highlight was a sanity check.
• Error duplicates (same record imported twice): delete them. Use Remove Duplicates by Multiple Columns for the dedup, especially if the rows aren't byte-identical (a phone number changed, a timestamp drifted).
• Whitespace-corrupted duplicates (Acme vs. Acme ): trim the whitespace first. Run Trim Whitespace From All Cells, then re-highlight.

Frequently asked questions

Does the macro highlight the first occurrence or only repeats?

Both. Every cell whose value appears two or more times gets highlighted. So if a value appears 3 times, all 3 instances are colored. This matches Conditional Formatting's default behavior. To highlight only repeats (skipping the first occurrence), you'd need a more complex rule or a custom macro.

Will it highlight blanks?

No. The macro skips empty cells. So if you have 50 blank cells in a column, they aren't all flagged as duplicates of each other. Conditional Formatting's built-in rule has the same exclusion.

What if my data has multiple duplicate categories I want to color differently?

The macro paints all duplicates the same color. For category-based coloring (e.g., red for duplicates of "Acme", yellow for duplicates of "Beta"), use Conditional Formatting with separate rules per category.

Does it work across multiple columns or only within one?

Within whatever you select. If you select 3 columns, the macro treats all the cells in those columns as one pool when checking for duplicates. So Acme in column A and Acme in column C count as duplicates. To restrict to within-column duplicates, run the macro one column at a time.

Can I undo the highlight?

Yes. Hit Ctrl/Cmd + Z right after running. The macro applies static colors, so undo cleanly removes them in one step. This is one of the macro's main advantages over Conditional Formatting.

What to do next

If you have duplicates and want to delete them, head to Remove Duplicates by Multiple Columns for the dedup workflow that's smarter than Excel's built-in. If your duplicates are caused by whitespace, run Trim Whitespace From All Cells first and then re-check. For a one-shot visual audit, the Highlight Duplicate Values macro is the fastest path.