How to Find Duplicates in Excel: Every Method From Conditional Formatting to Power Query

Duplicates sneak into spreadsheets faster than you'd think. One pasted column, one merged export, one lazy copy from a colleague, and suddenly your customer list has the same email three times. The good news? Excel gives you a whole toolbox for spotting repeats before they wreck a report. The trick is knowing which tool fits which job.

This guide walks you through every reliable way to find duplicates in Excel, from a two-click visual highlight all the way to Power Query group-by logic. You'll learn the formulas, the menu paths, and the gotchas that trip people up, like trailing spaces and case sensitivity.

By the end, you'll pick the right method in seconds. Before you pick a method, ask yourself two questions. Do you want to see the duplicates or act on them? Highlighting is great for review.

Formulas and Power Query are better when you need to filter, count, or feed the result into another step. Are you matching on one column or several? Multi-column matches need COUNTIFS or Power Query, not the basic Conditional Formatting rule.

Let's start with the method nine out of ten people reach for first, because it's fast, visual, and lives one ribbon click away. Conditional Formatting paints duplicates in a color of your choice and keeps them painted as you scroll.

It's perfect for a quick audit on a list under 50,000 rows, and it's the right answer when your boss asks for a duplicate check in the next ten minutes. The visual nature also means anyone looking at the sheet can immediately see what's going on.

The downside? It only works on one column at a time and slows down on bigger ranges. That's why pros always have a few backup methods ready, and why this guide covers ten more.

Conditional Formatting is the headline feature, but it's not the whole story. The Duplicate Values rule only looks at one column at a time. If you need to find rows that match across two or three columns, like first name plus last name plus email, you need a formula.

That's where COUNTIFS earns its keep. Think of it as COUNTIF's smarter sibling that accepts pairs of range and criteria, letting you stack as many conditions as you have columns.

The syntax feels long the first time you write it, but it's the same pattern repeated. Once you've used it twice it sticks, and you'll wonder how you ever managed without it.

Comparing two columns or two sheets is a daily task for anyone reconciling data. Maybe you've got last month's customer export in column A and this month's in column D, and you want to know who's in both lists.

The MATCH function paired with ISNUMBER does this in a single line. Drop =IF(ISNUMBER(MATCH(A2, $D:$D, 0)), "In both", "Only in A") next to your column A and drag down. Repeat the formula on the other side to catch entries unique to column D.

VLOOKUP works for the same task, but it's chattier and returns errors when nothing matches. MATCH plus ISNUMBER is cleaner because you skip the IFERROR wrapper.

If you're new to checking lists for repeats, our guide on how to check for duplicates in Excel has a step-by-step walkthrough with screenshots, including how to handle the awkward case where the two lists live in completely separate workbooks.

Why the prep work? Because Excel treats john@email.com and john@email.com (with a trailing space) as completely different values. So does Apple and apple with a space.

You'll think your formula is broken when really the data just has invisible junk. A quick TRIM and CLEAN pass on your source columns saves hours of head-scratching later.

The CLEAN function in particular is underrated. It strips out the non-printable characters that often hitch a ride on data pasted from PDFs, web pages, or legacy database exports.

Wrap your text in =TRIM(CLEAN(A2)) and you've solved roughly a third of the duplicate-detection problems people complain about online before you even pick a finding method.

Now let's talk Advanced Filter, an underrated tool that's been in Excel since the 90s. Hit Data → Advanced, point it at your range, tick Unique records only, and choose to filter in place or copy to a new location.

The output is your deduped list. It doesn't tell you which rows were duplicates, but it's lightning fast for extracting a clean unique list from a messy column.

Advanced Filter shines when you want a quick unique export without touching the source data. Combine it with sorting and you've got a one-shot cleanup tool.

It's not the right pick when you need to count duplicates or flag them for review, since it just hides or copies, it doesn't annotate. But for the classic ask of give me the unique list of customers from this messy export, nothing beats it for speed.

Speaking of Remove Duplicates, a quick word of warning. The button lives at Data → Remove Duplicates and it's tempting to click straight away. But the tool is permanent.

It deletes rows without showing you what it removed and there's no undo once you save and close. Always work on a copy or duplicate the sheet first.

If your goal is cleanup, our guide on how to delete duplicates in Excel covers the safe sequence. The pattern is always the same: copy the sheet, find first, review, then remove on the copy.

Keep the original intact so you've got something to roll back to if your boss decides those duplicates were actually meaningful after all.

What about the visual approach beyond the basic rule? Conditional formatting in Excel isn't just for duplicates. You can layer rules to show duplicates in red and unique values in green, or create a heat map of how often each value appears.

Combine the Duplicate Values rule with a separate Format only cells that contain rule keyed to your COUNTIF helper, and you get a multi-color report at a glance.

Want triplicates highlighted differently from doubles? Build a custom rule with a formula like =COUNTIF($A:$A, A1)>2 and apply a darker color.

Now your eye can instantly tell which entries are mild duplicates and which are repeated five or six times, without doing any math in your head. That's a small upgrade with a huge payoff in review speed.

COUNTIF wins on flexibility. You can filter on the count, sort, copy the result somewhere else, and combine with other formulas. Conditional Formatting wins on speed and visual appeal.

Most pros use both: Conditional Formatting for the first look, COUNTIF when they need to do something with the duplicates beyond just seeing them.

The two methods complement each other nicely. Run the visual rule first to get a sense of how bad the problem is, then drop in a COUNTIF helper if you need to filter, count totals, or build a report.

There's no rule against having both active on the same sheet, and the helper column doubles as documentation that you actually checked. Auditors love that kind of thing.

Find & Replace is the manual approach and it works for tiny lists. Press Ctrl+F, type the value you suspect is duplicated, click Find All, and Excel shows you every cell containing that value.

It's clunky for anything bigger than 50 rows, but it's perfect when you just want to verify one specific entry appears twice.

For pure visual scanning, sorting your column alphabetically or numerically also brings duplicates next to each other. It's the oldest trick in the book and it still works.

The sort approach is also the only way to spot near-duplicates: entries like Robert Smith and Bob Smith that aren't exact matches but probably refer to the same person. Software can't catch those reliably, but a human scanning a sorted list usually can.

Let's tackle the case-sensitivity question head-on, because it bites everyone eventually. Imagine you're cleaning a product code list with entries like AB123 and ab123. Are they the same product or two different SKUs?

Excel's default tools say they're identical. If your business logic says they're different, you need EXACT.

The formula =SUMPRODUCT((--EXACT($A$2:$A$1000, A2)))>1 returns TRUE when there's a case-sensitive duplicate of A2 anywhere in the range.

The double negative converts TRUE/FALSE to 1/0, SUMPRODUCT adds them up, and if the total is greater than 1, you've got a repeat. It's heavy on big ranges, so use Power Query for anything over 100,000 rows.

Here's a battle-tested workflow for messy real-world data. Step one, copy your source column into a new sheet so the original stays safe.

Step two, build a clean version next to it: =TRIM(CLEAN(UPPER(A2))) normalizes case, strips spaces, and removes invisible characters.

Step three, run COUNTIF on the clean column. Step four, filter the helper to show counts greater than 1 and review. Step five, decide what to do with the matches.

This four-formula combo catches almost every duplicate that the basic tools miss. Most data pros build this exact pattern into a template and reuse it on every new import.

Anchored ranges deserve special mention. If you write =COUNTIF(A2:A1000, A2) without dollar signs, the range shifts as you drag the formula down.

Row 100 will check A100:A1098, missing earlier values. Always lock the range: =COUNTIF($A$2:$A$1000, A2). The second argument stays relative so it points at the current row.

This is the single most common bug in duplicate-finding formulas, and it's silent: your sheet won't error, it'll just under-report duplicates.

The fix is muscle memory. Press F4 right after you select the range to add the dollar signs automatically, every single time. Build the habit and you'll never debug an unanchored range again.

What if you have a million rows? At that scale, Conditional Formatting will freeze your workbook and COUNTIF will take minutes per recalculation.

Power Query handles the load comfortably because it processes data outside the workbook grid. Load your data as a connection, run Group By with Count Rows, filter to counts greater than 1, and load the result back as a table.

The whole operation runs in seconds even on huge datasets. The trick at this scale is to load as a connection only, not as a worksheet.

The full million rows don't need to live in your grid; you only need to see the duplicates. Loading just the filtered output keeps your file small and your scrolling smooth, while the underlying query still has access to every row when it refreshes.

One more workflow worth mentioning: the audit trail. When you find duplicates in a shared workbook, don't just delete them silently.

Add a comment or a note in a status column explaining what you found, what you removed, and when. Future-you (or future-colleague) will thank you when the question comes up six months later.

A simple format like 2026-05-10 NB: removed 47 duplicate customer rows after TRIM cleanup in a notes tab is enough. It's the difference between defensible data work and mystery deletions.

Audit trails also save your bacon when someone asks why the row count dropped. You can point to your notes and the saved backup file rather than scrambling for an explanation.

Pulling it all together: the right method depends on size, columns, and what you'll do next. For a quick visual check on a small sheet, Conditional Formatting wins. For action on medium datasets, COUNTIF or COUNTIFS.

For huge data or repeatable workflows, Power Query. For case-sensitive precision, EXACT with SUMPRODUCT. Master those four and you'll handle any duplicate-finding task Excel throws at you.

The methods stack, too. Start with a Conditional Formatting pass to spot obvious repeats. Add a COUNTIF helper column for filtering. Move to Power Query when the data outgrows the grid.

Each tool builds on the last, so the time you spend learning one feeds into the next. Keep this guide bookmarked and you'll never stare blankly at a messy spreadsheet again. The duplicates won't stop coming, but at least you'll have a plan.

One last thought: practice on safe data before you trust your skills with real work. Build a sample sheet with 200 rows of fake customer info, deliberately duplicate twenty entries, then run each method against it. You'll feel the speed differences, hit the case-sensitivity gotchas, and see how Power Query refreshes when you tweak the source. Twenty minutes of practice on dummy data saves twenty hours of fixing real data later. That's the ratio every Excel pro learns the hard way.

And if you ever feel stuck, remember the universal escape hatch: copy your column to a fresh sheet, sort it, and scroll. Your eyes will catch what formulas miss, especially the soft duplicates like name variations and abbreviated company names. Software is a tool, but human pattern matching is still the gold standard for messy data review. Combine both and you'll never miss a repeat that matters.

Finally, share what you learn. The next person on your team to inherit a duplicate-riddled sheet will thank you for a quick wiki page or pinned Slack message documenting which method works for which scenario. Excel knowledge spreads slowly inside teams because nobody wants to admit they didn't know about Power Query. Be the one who shares the shortcut, the formula, and the warning about Remove Duplicates. That habit pays back across every team you'll ever join.