You export a contact list, open it in Excel, and there it is. The same customer, three times. Slightly different in each row, because one has a trailing space and another capitalized the last name.
Duplicate rows are the most common problem in any spreadsheet built from more than one source. They inflate your totals, break your pivot tables, and quietly make every number you report a little bit wrong.
Excel has a built-in tool that fixes this in about five seconds. The catch is that it deletes rows permanently, and it defines “duplicate” more strictly than you probably do. This guide covers the fast method, how to check your work before you commit, and the specific mistakes that wipe out data you actually needed.
Table of contents
- What counts as a duplicate in Excel
- How to remove duplicates in Excel
- Find duplicates before you delete anything
- Removing duplicates based on specific columns
- Five mistakes that delete good data
- When to use Power Query instead
- Where clean-data skills actually lead
- Build the data skills employers are hiring for
- FAQs about removing duplicates in Excel
What counts as a duplicate in Excel
Excel compares values, not meaning. Two rows are duplicates only when every cell you told Excel to check matches exactly. That sounds obvious until you hit the edge cases.
“john@acme.com” and “John@Acme.com” are treated as the same value, because the Remove Duplicates tool ignores capitalization. But “john@acme.com ” with a trailing space is a different value entirely, and Excel will keep both rows. So will “Acme Inc” versus “Acme Inc.” with a period.
This is why two people can dedupe the same file and get different row counts. How you clean the data first matters more than the button you press.
How to remove duplicates in Excel
The fastest method is the built-in Remove Duplicates tool, which lives on the Data tab. It works the same way in Excel for Windows, Mac, and the web.
Here’s the process:
- Click any cell inside your data. Excel will select the surrounding range automatically, though you can drag to select a specific range instead.
- Go to the Data tab and click Remove Duplicates in the Data Tools group.
- Check the box labeled “My data has headers” if your first row contains column names. Miss this and Excel treats your header row as data.
- Choose which columns Excel should compare. Every column is selected by default.
- Click OK. Excel tells you how many duplicate values it found and how many unique values remain.
Excel keeps the first occurrence of each duplicate and deletes the rest. It doesn’t ask which one you want to keep, and it doesn’t look at the other columns to decide. If row 4 and row 900 match on the columns you selected, row 900 is gone.
Microsoft’s official documentation on finding and removing duplicates confirms this behavior. There’s no undo once you save and close, so the next section matters.
Find duplicates before you delete anything
Good analysts look before they cut. Highlighting duplicates first tells you whether the rows are genuinely redundant or whether they’re separate records that happen to share a value.
Select your range, go to the Home tab, and choose Conditional Formatting, then Highlight Cells Rules, then Duplicate Values. Excel shades every repeated value so you can scan the pattern. If half your “duplicates” are legitimate repeat orders from the same customer, you just saved yourself from deleting real transactions.
The COUNTIF function gives you a countable version of the same answer. Put this in a helper column next to your data:
- =COUNTIF(A:A, A2) returns how many times the value in A2 appears in column A. Anything above 1 is a repeat.
- =COUNTIFS(A:A, A2, B:B, B2) checks two columns together, which matches how Remove Duplicates actually behaves.
Sort by that helper column and your duplicates group together. Now you can review them as a batch instead of trusting a dialog box.
Removing duplicates based on specific columns
This is the setting most people ignore, and it’s the one that does the real work.
Say you have a sales export with columns for Email, Order ID, Product, and Date. If you leave every column checked, Excel only removes rows where all four match. A customer who ordered twice has two different Order IDs, so both rows survive. That’s usually correct.
Now uncheck everything except Email. Excel keeps one row per email address and deletes every other order that customer placed. Sometimes that’s exactly what you want, like when you’re building a mailing list. Sometimes you just destroyed your revenue data.
The rule to remember: the columns you check define what “the same record” means. Pick the smallest set of columns that genuinely identifies a unique record, and think about it before you click OK. If your table has a real ID column, that’s almost always the right answer on its own.
Five mistakes that delete good data
These are the ones that cost people a rebuild.
- Forgetting to sort first. Excel keeps the first occurrence it encounters. If you want to keep the most recent record, sort by date descending before you dedupe. Otherwise you keep the oldest one by accident.
- Deduping a filtered view. Remove Duplicates works on the whole range, not just visible rows. Hidden rows still get deleted. Clear your filters first.
- Leaving out a column that matters. If you select a range by dragging and miss the last column, that column’s data stays put while the rows around it shift. Your table is now scrambled and it won’t be obvious.
- Trusting it on numbers formatted as text. The value 100 and the text “100” don’t match. Neither do dates stored in different formats. Standardize the column type first.
- Working without a backup. Copy the sheet before you run anything destructive. It takes four seconds and it’s saved more spreadsheets than any formula.
One more habit worth building: run TRIM and either UPPER or LOWER on your text columns before deduping. Trailing spaces and inconsistent capitalization are behind most of the duplicates Excel fails to catch.
When to use Power Query instead
Remove Duplicates is a one-time action. You run it, rows disappear, and next month you do the whole thing again on the new export.
Power Query is the better tool when the file refreshes. You build the cleanup steps once, and every time new data lands the same steps run again automatically. Load your data through Get and Transform on the Data tab, then use Remove Rows and Remove Duplicates inside the query editor. Microsoft’s Power Query documentation walks through the interface.
The other advantage is that Power Query is non-destructive. Your source file stays intact and the cleaned version loads to a new sheet. When you’re wrong about which rows to cut, and eventually you will be, you haven’t lost anything.
If you’re already comfortable with spreadsheet basics like freezing rows to keep headers visible and building drop-down lists to control data entry, Power Query is a reasonable next step. Preventing duplicates at entry beats cleaning them up later.
Where clean-data skills actually lead
Deduping a spreadsheet sounds small. It’s also the first thing a hiring manager watches you do in a data analyst take-home assignment, because it reveals whether you check your work or just push buttons.
Data cleaning is most of the job. Analysts spend the bulk of their time getting data into a usable state before any analysis happens. The people who move up are the ones who can explain why they made a cleaning decision, not just that they made one.
The demand side is holding up well. The Bureau of Labor Statistics projects 35 percent employment growth for data scientists between 2025 and 2035, against roughly 3 percent for all occupations. Computer and information research scientists sit at 22 percent, and information security analysts, another role that runs on clean data, at 21 percent.
You can read the underlying numbers on the BLS Occupational Outlook Handbook page for data scientists.
Build the data skills employers are hiring for
Excel is where most data careers start. It’s rarely where they end. The analysts getting hired right now pair spreadsheet fluency with SQL, a visualization tool like Power BI or Tableau, and enough statistics to know when a number is telling them something real.
Coding Temple’s data analytics bootcamp is built around that stack, with project work that looks like the messy, duplicate-ridden datasets you’ll actually get handed on the job. Career services support runs alongside the curriculum, so you’re preparing for interviews while you learn.
If you want to test the water first, the free data analytics course covers the fundamentals at no cost. When you’re ready to commit, apply to Coding Temple and start building toward your first analyst role.
FAQs about removing duplicates in Excel
How do I remove duplicates in Excel without deleting rows?
Use the UNIQUE function in Microsoft 365 or Excel 2021 and later. Enter =UNIQUE(A2:C500) in an empty cell and Excel spills a deduplicated copy into a new range while your original data stays untouched. On older versions, use Advanced Filter with “Copy to another location” and check “Unique records only.”
Does Remove Duplicates in Excel keep the first or last entry?
Excel keeps the first occurrence in the range and deletes every one after it. It has no setting to keep the last one. If you need the most recent record, sort your data by date in descending order before you run the tool.
Can I undo Remove Duplicates in Excel?
Yes, as long as you haven’t closed the file. Press Ctrl+Z on Windows or Command+Z on Mac right away. Once you save and close the workbook, the deleted rows are gone. Duplicate the sheet before running it as a habit.
Why is Excel not finding my duplicates?
Usually it’s invisible differences. Trailing or leading spaces, numbers stored as text, inconsistent date formats, or non-breaking spaces pasted from a web page all make two values that look identical read as different. Run TRIM on your text columns and confirm the column formats match before deduping.
How do I remove duplicates based on one column only?
Open Remove Duplicates, click “Unselect All” in the columns list, then check only the column you care about. Excel compares that column alone and deletes every row after the first match, including the data in the other columns on those rows.
Is removing duplicates in Excel case sensitive?
No. The Remove Duplicates tool treats “Smith” and “SMITH” as the same value. If capitalization is meaningful in your data, add a helper column with a formula like =EXACT(A2,A3) to compare values case sensitively before you decide what to cut.