The Quick Answer: Which Method to Use
Match the method to the cost of being wrong. If the sheet is a scratch copy you can regenerate, the button is fine. If the sheet is the only copy of something — a customer export, a month-end order file, a payroll feed — do not use the button. Use Power Query, which filters duplicates in a step you can re-run and audit, or a COUNTIF helper column, which flags rows without touching them.
Here is the decision rule I give analysts. One-off cleanup on disposable data: Data > Remove Duplicates. Recurring cleanup that arrives monthly and looks the same every time: Power Query. You need to review each flagged row before deciding: COUNTIF helper column, or Conditional Formatting if you just want to eyeball it. You need a live non-destructive list that updates itself when the source grows: the UNIQUE function on Microsoft 365.
Method 1: The Remove Duplicates Button
This is the tool most people reach for, and for good reason — it is fast and it is built in. The important detail is the dialog: Excel asks which columns should be compared. Tick one column to judge duplicates by that column alone. Tick several to judge by the combination, which means two rows only count as duplicates if every ticked column matches. That choice changes your result completely.
Two things to do before you click OK. First, save a copy of the workbook, or at minimum insert a backup sheet with a full paste of the range. Second, select the whole table, not just the column. If you select only column B and click Remove Duplicates, Excel deletes the duplicate cells in B and shifts the neighboring rows up, scrambling your records. That one has bitten almost everyone once.
Save the workbook with a new name, or right-click the sheet tab and choose Move or Copy > Create a copy. If you skip this, there is no undo path — Ctrl+Z does not reverse a Remove Duplicates pass after you close and reopen the file.
Click any cell inside your Customer List, then press Ctrl+A to grab the whole table, or click the top-left cell and Shift+click the bottom-right. Verify the Name Box shows the full range, something like A1:F5001 for 5,000 data rows plus a header.
Go to the Data tab and click Remove Duplicates in the Data Tools group. The dialog appears with every column listed and all of them ticked by default. If your header row was detected, you will see a My data has headers checkbox — leave it ticked.
Untick everything you do not want compared, then tick just the key columns. For a Customer List keyed on email, tick Email alone. To treat a customer as duplicate only when both email and company match, tick Email and Company. Click OK.
Excel reports something like: 312 duplicate values found and removed; 4,688 unique values remain. Write that number down. That message is your only evidence, and it vanishes the moment you touch another dialog. Then scroll a few rows and spot-check that the surviving record looks right.
A common mistake is running the button on data that has not been cleaned. If your Email column holds 'john@x.com' and 'john@x.com ' with a trailing space, Excel treats them as two different values and keeps both. Standardize first with a TRIM/LOWER helper column, then de-duplicate. I also keep a Rejected sheet where I paste the original range before deleting anything, so I can always answer 'what did we lose?'
Method 2: Conditional Formatting to Look Before You Delete
If you are not certain your duplicate rule is correct, do not delete anything yet. Highlight the duplicates and count them. This costs a minute and prevents the worst outcome, which is deleting rows you actually needed. It is my default first move on any unfamiliar file.
Highlight the range you want checked — for example A2:A5001 of the Email column in your Customer List. Do not include the header row, or the header will be flagged if any data value happens to match it.
Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, choose Duplicate (not Unique) and pick a fill color. Click OK. Every repeated email is now shaded, including all copies of a value, not just the second one.
Turn on the filter with Ctrl+Shift+L, click the filter arrow on Email, and use Filter by Color to show only the shaded cells. The status bar shows a count — if it reads 624, you have 312 duplicated pairs. Compare that against what you expect before deleting a single row.
Work through the flagged rows and decide which copy survives. Sort by Email so duplicates sit next to each other, then compare the other columns — usually the later date or the more complete record is the keeper. Delete the losers, then clear the rule from Conditional Formatting > Clear Rules.
Method 3: COUNTIF Helper Column to Flag and Keep the First Record
This is the method I recommend when you need to keep exactly one row per group and you want to control which one. A helper column tells you how many times each value appears, and a second formula can number the copies so you keep the first and drop the rest. Nothing is deleted until you say so, and the whole thing is auditable.
Put this in F2 of your Customer List, assuming Email is in column A and data runs from row 2 to 5001: =COUNTIF($A$2:$A$5001,A2)>1. The dollar signs lock the range so it stays fixed as you fill down; the second reference A2 stays relative and follows the row. Copy it to F5001. TRUE means that email appears more than once somewhere in the column.
In F1 type the header DuplicateFlag. In F2 enter =COUNTIF($A$2:$A$5001,A2)>1 and press Enter. Double-click the fill handle in the corner of F2 to fill the formula down to the last row, or select F2:F5001 and press Ctrl+D.
In G1 type CopyNumber. In G2 enter =COUNTIF($A$2:A2,A2). Notice only the first A2 is locked — as you fill down, the range grows, so the first appearance of an email gets 1, the second gets 2, and so on. That number is exactly what you need to keep one record per email.
Press Ctrl+Shift+L, filter CopyNumber to keep only values greater than 1. Those are the extra copies. Check the row count in the status bar against the number you expect — 312 if your flag column returned 624 TRUE values across 5000 rows.
Select the visible rows, right-click a row header, and choose Delete Row. Then clear the filter. If you would rather not lose anything, do not delete — instead copy these rows to a Rejected sheet and leave the master intact.
Delete columns F and G once the cleanup is verified, or keep them if the workbook is refreshed monthly. If you keep them, remember to extend the ranges before new data lands — a helper column that still points at A5001 will report zero duplicates for anything past row 5001.
This will break if you forget the absolute references. Write =COUNTIF(A2:A5001,A2)>1 without the dollar signs and the range slides down as you fill, so lower rows check shrinking windows and report FALSE for genuine duplicates. The symptom is a flag count that keeps dropping as you scroll. Both COUNTIF ranges above use $A$2:$A$5001 for the full column and $A$2:A2 for the running count — that difference is deliberate.
Method 4: Power Query to Make De-duplication Repeatable
If new data lands every month and the cleanup is always the same, stop doing it by hand. Load the file into Power Query, remove duplicates there, and from then on you refresh rather than rebuild. The rules live in the query, not in your memory. This is the method I use for anything recurring, and it pairs well with the setup covered in our Power Query guide.
Power Query's Remove Rows > Remove Duplicates works on the rows selected in the current step, and it keeps the first row encountered — same rule as the built-in button, but with a recorded step you can inspect, edit, or delete. Add a Date column and you can refresh next month without opening a single dialog.
Click any cell in your Customer List and go to Data > From Table/Range. Confirm the range, tick My table has headers, and click OK. The Power Query Editor opens with your 5,000 rows listed as a query step on the right.
Click the Email column header, then Ctrl+click Company if a customer should only count as duplicate when both match. Hold Shift instead if the columns sit next to each other and you want a contiguous block.
On the Home tab, click Remove Rows > Remove Duplicates. A new Removed Duplicates step appears in the Applied Steps pane. Click it and watch the row count drop in the bottom-left status bar — it should land on 4,688 if you had 312 duplicate rows.
Power Query keeps the first row it meets, so sort first if your rule is different. Click the Date column arrow and choose Sort Descending, which puts the latest record on top. Then apply Remove Duplicates and the newest copy is the one that survives.
Click Close & Load to drop the cleaned rows into a new sheet. Next month, replace the source file, right-click the query output, and choose Refresh. The de-duplication re-runs on the new data with no manual steps.
Method 5: The UNIQUE Function on Microsoft 365
UNIQUE is the modern, non-destructive option. It spills a new list of distinct values and leaves your source rows untouched. If you have Microsoft 365 or Excel 2021 or later, this is often the cleanest way to produce a deduplicated list without editing the original data.
The basic form is =UNIQUE(A2:A5001), which returns every distinct email in the range. Add the second argument to control what counts as duplicate: =UNIQUE(A2:B5001,,FALSE) compares whole rows when you need the combination of Email and Company. Set the third argument to TRUE if you only want values that appear more than once — useful for a quick duplicates report.
Click a cell with plenty of clear space to its right and below, such as H2 on a sheet separate from the raw data. UNIQUE spills downward and to the right, so anything in the spill path returns a #SPILL! error and blocks the formula.
Type =UNIQUE(A2:A5001) and press Enter. Excel spills a single column of distinct emails. For full records rather than one column, use =UNIQUE(A2:F5001) — but check the row order, because it keeps the first occurrence of each distinct row.
Wrap it: =SORT(UNIQUE(A2:A5001)). You get the same distinct values in alphabetical or numeric order. This is the version I put in dashboards because the list stays stable when new rows are added to the source — the range just needs to extend past row 5001.
Select the spill range and read the row count in the status bar. If your 5,000-row Customer List contains 312 duplicate rows, the spill should show 4,688 values. A different number means you are comparing the wrong columns or the data still has fake duplicates.
Which Row Survives? The Part Most Guides Skip
Every method above keeps one row and discards the rest, and they do not all choose the same way. Get this wrong and you quietly keep a stale record while deleting the current one.
Data > Remove Duplicates keeps the first occurrence in the sheet's current order — the topmost row wins. That is fine if your data is sorted newest-first. If it is sorted oldest-first, you keep the oldest record. Power Query behaves the same way: first row encountered, which in practice means the first row after whatever sort step you applied above it. UNIQUE matches this too, keeping the first occurrence of each distinct value.
There is a second wrinkle with multi-column keys. When you tick several columns, Excel does not sort your data first — it evaluates rows in sheet order and keeps the first row whose combination is new. So if you tick Email and Company on a Customer List where the same email appears under two company names, you will keep whichever row sits higher, and you cannot predict which that is unless you sort deliberately. When the surviving row matters, sort the range on the column that defines 'best' — usually the latest Date — before you de-duplicate.
Fake Duplicates: Case, Spaces, and Hidden Characters
The count that surprises people is not 312. It is 500. That happens when Excel does not recognize rows as duplicates because of differences you cannot see on screen. Remove Duplicates, COUNTIF, and UNIQUE all compare values exactly — case-insensitively for letters, but spaces and non-printing characters count.
'John@x.com ' with a trailing space is a different value from 'john@x.com'. So is a cell imported from a CRM that ends with a non-breaking space, which looks identical and defeats TRIM in older versions. Two customers with the same email but different capitalization are usually treated as one, since Excel's comparison is case-insensitive — but if you export those strings into SQL later, 'John@x.com' and 'john@x.com' become two distinct rows. Normalize before you compare.
In H1 type EmailClean. In H2 enter =TRIM(LOWER(A2)). TRIM strips leading, trailing, and repeated internal spaces; LOWER forces everything to lowercase. Fill down to H5001. Now compare values on the clean column instead of the raw one.
If duplicates still do not match, the culprit is usually a non-breaking space. Use =LEN(A2)-LEN(TRIM(A2)) to count stray spaces, or swap in =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) which replaces the non-breaking space before trimming. CHAR(160) is the character you cannot see.
Run =COUNTIF($H$2:$H$5001,H2)>1 against the normalized column. If the TRUE count jumps — say from 312 to 480 — that difference was fake duplicates hiding behind whitespace. Now you have a decision: clean the source values, or keep them and accept the inflated count.
You can paste the clean column over the original with Paste Special > Values, but only after confirming nothing downstream depends on the original formatting. I usually keep the raw column and do all matching on the helper column instead, so the source stays auditable.
Do the same normalization on the other side of the join. If you plan to hand this data to a database, the SQL data-cleaning workflow normalizes with TRIM and LOWER inside the query, and catching mismatches in Excel first saves you a debugging session later. Whichever tool does the final match, it has to see the same string. I've lost an afternoon to a trailing space that only existed in the export.
When You Should NOT Remove Duplicates
Duplicates are not always errors. In an Orders table, the same Customer ID appearing eight times is not a data quality problem — it is eight purchases, and deleting the extra rows destroys your revenue numbers. The question to ask is never 'are there duplicates?' but 'is this column supposed to be unique?'
An Order ID column should be unique, and a duplicate there is a real bug. A Category ID column absolutely should not be unique — deduplicating it would collapse 5,000 order lines into 12 categories and quietly wipe out the report. Same word, opposite fixes. Check what the column means in the business before you touch it.
The safe pattern is to separate the two jobs. Keep the Orders detail table exactly as it landed, duplicates and all, and build a separate unique Customer List from it with UNIQUE or Power Query. That way your transaction history stays whole and you still get one row per customer for segmentation and mailings. If you are new to cleaning spreadsheets and want the wider workflow before touching a real file, the steps in our Excel data cleaning guide cover the order to do things in.
A key — Order ID, Invoice Number, Employee ID — must be unique, and a duplicate there means two records were created by mistake. A value or foreign key — Customer ID, Category ID, Region — repeats by design. Sort by the column and count how often values recur before assuming the repetition is a problem.
Leave the transaction table alone. On a new sheet, use =SORT(UNIQUE(Orders[Customer ID])) to produce the list you actually need for mailings or lookups. You get the distinct set without altering the record of what happened.
Put a note in A1 of the cleaned sheet: 'De-duplicated on Email, kept first row by Date descending, run 2026-09-15.' Six months from now, when someone asks why a customer is missing, that one line answers it and saves a re-investigation.
Excel vs SQL: Where De-duplication Belongs
Excel is the right tool when the file is small, the cleanup is occasional, and a human needs to eyeball the result. Above roughly 100,000 rows the formulas get slow, and COUNTIF over a 500,000-row range will make you wait. That is the point where moving the deduplication into a database pays off — ROW_NUMBER() with a PARTITION BY lets you rank copies per key and keep exactly one, with the rule written in plain view.
For a monthly file with 5,000 rows, Power Query is the sweet spot and I would not bother with SQL. For a 2 million row export that lands nightly, do it in the warehouse. The deciding factor is how often the job repeats and how much data flows through it, not which tool sounds more advanced.
Quick Checklist Before You Delete Rows
Work down this list before any delete pass, and you avoid almost every de-duplication disaster. It takes two minutes.
Save a copy of the workbook, or paste the original range onto a Rejected sheet. Select the entire table, never a single column. Tick only the columns that genuinely define a duplicate — one key column for an email list, key plus a second column only when the business rule says so. Write down the reported duplicate count so you have evidence. Spot-check three surviving rows against the originals. Finally, sort on your 'best' column before de-duplicating if the surviving row matters, because every method keeps the first occurrence in whatever order the sheet is in.
