Highlight Duplicate Values

What it does

Paints every cell in your selection whose value appears more than once. Light red fill, dark red text, easy to spot at a glance. Your data isn't modified, only the cell color changes. This is the read-only counterpart to Remove Duplicates, for when you want to see what's duplicated before deciding what to delete (or whether to delete anything at all).

Real-world example

An accountant is doing a vendor audit before the quarterly payment run. The vendor master sheet has 1,400 rows. He suspects a few vendors have been entered twice over the years (slightly different spellings of the same business name, different tax IDs, who knows). Before he cuts checks, he wants to flag the suspects so the AP clerk can investigate.

The wrong tool here is Remove Duplicates, because he doesn't want to delete anything. He just wants to see them.

The other wrong tool is Excel's conditional formatting, which works but stays "live": the formatting rule recalculates as the data changes, slows down the file, and follows the data when copy-pasted to other workbooks (where it sometimes silently breaks). For a one-time audit he wants static highlighting, not a rule.

He selects the Vendor Name column, runs HighlightDuplicates, gets a count of 47 duplicate cells. He filters by the cell color, exports the suspects to a Slack message for the AP clerk: "investigate these before Friday." Job done.

Realistic time saved on a 1,400-row column: about 15 minutes vs. setting up conditional formatting and remembering to clear it later. Done as a quarterly habit, that's an hour back per year. The bigger win is the file stays clean and there's no formatting rule for someone else to inherit and curse you for.

How to use

First time? See How to run our tools for the 30-second setup.

1. Paste your data into the workbook.
2. Select the column or range you want to check. One column or a wide range, both work.
3. Press Alt + F8 / Option + F8, pick HighlightDuplicates, click Run.
4. Cells whose value appears two or more times get painted. The macro shows the count.

FAQ

How is this different from Excel's conditional formatting for duplicates?

Conditional formatting is a rule that recalculates as data changes. This macro paints the cells statically, no rule attached. That makes it faster to apply, easier to clear (Home → Clear → Clear Formats on the selection), safer to send to colleagues, and impossible to accidentally inherit when someone copies your sheet.

Can I undo it?

Yes, Ctrl/Cmd + Z right after running. To clear later: select the highlighted cells and use Home → Clear → Clear Formats.

Are numbers and text compared the same way?

No. Excel stores numbers and text differently, so cell values 1 (number) and "1" (text) are treated as different. If you want them treated as equal, format the column as text first, or use Data → Text to Columns to normalize.

Does whitespace matter?

Yes. Acme and Acme (trailing space) are treated as different values. If your data has stray whitespace, run Trim Whitespace From All Cells first.

Does it work across multiple columns?

Yes. The macro looks at every cell in the selection regardless of column, so duplicates within the same column or across columns are highlighted the same way. To dedupe by row across multiple columns specifically, use Remove Duplicates by Multiple Columns instead.