How to remove duplicates in a spreadsheet comes down to one decision: which columns you tick. In Microsoft Excel, select your data range and use Data > Remove Duplicates. In Google Sheets, use Data > Data cleanup > Remove duplicates. Both tools compare the columns you select, keep the first matching row, and delete the rest.
The whole job takes about a minute. The part that goes wrong is choosing which columns define a duplicate, and that decision is what decides whether you clean a list or quietly destroy half of it.
I see the same pattern repeatedly in spreadsheet work. Someone merges a CRM export with an email list, runs Remove Duplicates across every column, and later discovers that two genuine orders from the same customer were counted as one row. Or the opposite: they tick only the email column and lose a customer who bought twice. Both errors are quiet. The sheet still looks tidy.
Duplicates also survive cleanup more often than people expect, because Excel and Google Sheets compare text exactly. John Smith and john smith are two different values as far as the tool is concerned, trailing space included. Getting good results means normalising first, picking a real key column, and checking the result before you save.
Table of Contents
- What You Need
- Menu Paths for Removing Duplicates by Program
- Step-by-Step: How to Remove Duplicates in a Spreadsheet
- Frequently Asked Questions
- Does removing duplicates in Excel delete every row with a repeated name?
- Can I remove duplicates based on two columns in Excel or Google Sheets?
- How do I remove duplicate rows but keep blank cells?
- How do I find duplicates without deleting them first?
- Can I undo removing duplicates after saving the spreadsheet?
- Conclusion
What You Need
You need a spreadsheet open in one of three programs, and you need to know which column identifies a record. That is the whole prerequisite list.
- The app: Microsoft Excel 365 or Excel 2021 on Windows or Mac, current Google Sheets in a browser, or LibreOffice Calc if your work lives in open-source files. Menu names differ, so use the row for your program below.
- A key column: one field that uniquely identifies each record, such as order ID, customer number, email address, or employee ID. Names almost never work for this, because two different people share a name.
- A working copy: a duplicate of the file, or a new sheet carrying a copy of the data. Removing duplicates deletes rows, and formulas elsewhere in the workbook can shift or break.
- An empty helper column: somewhere off to the right of your data, ready for a check formula.
If you plan to repeat this cleanup every month, add one more item: a note of which columns you ticked and which column was your key. Next month’s export will not have the same layout.
Menu Paths for Removing Duplicates by Program
| Program | Menu path | Key dialog option |
|---|---|---|
| Excel 365 or 2021 | Data > Remove Duplicates | My data has headers, Case sensitive, per-column checkboxes, Count Duplicates |
| Google Sheets | Data > Data cleanup > Remove duplicates | Data has header row, columns to check |
| LibreOffice Calc | Data > More Filters > Standard Filter > No duplication | Copy results to another location |
Excel’s dialog is the most capable of the three because it can count duplicates without deleting anything. Google Sheets asks for the same information in a simpler form. Calc’s route lives in the filter menu and offers a useful option: send the clean rows somewhere else and leave the source intact.
Step-by-Step: How to Remove Duplicates in a Spreadsheet
The workflow runs in six steps: work on a copy, find the key column, inspect the repeats, choose columns carefully, run the tool in your program, then verify and save. Skip the first three and the tool will do exactly what you asked, including the wrong thing.
Step 1: Back Up the Spreadsheet and Identify the Record Key
Make a copy first. In Excel, right-click the sheet tab and choose Move or Copy, then tick Create a copy. In Google Sheets, right-click the tab, choose Duplicate, and work on the duplicate.
Then find the column that uniquely identifies a record. On an order log it is order ID. On a mailing list it is email. On a roster it is employee ID. On a survey export, it is the respondent ID column that the survey tool quietly adds, not the name field.
Skip names. Two “Maria Garcia” entries are usually two real people, and treating them as one duplicate deletes a customer.
Step 2: Inspect Suspected Duplicate Rows
Sorting is the fastest way to see repeats, because identical values land next to each other and your eye catches them immediately. Click any cell in the key column and sort ascending.
Look at each run of matching values and ask whether the other columns match too. Two rows with the same email and the same order ID are a true duplicate row. Two rows with the same email but different order IDs are two real purchases by one person, and you want to keep both.
A repeated value is not automatically a duplicate row. That distinction is where most of the damage happens.
Step 3: Select Only the Columns to Check
Tick only the columns that together define a record, normally the key column plus anything that must stay consistent, such as order ID plus product SKU.
Checking every column lets accidental duplicates through. If two rows for one order differ only because someone typed a shipping note in one of them, every-column matching treats them as distinct and both survive.
Checking too few columns deletes real records. Tick only the customer ID on an order log and each customer survives exactly once, which quietly erases their entire order history. In my experience this is the single most destructive choice on this page.
Before you run the tool, type =COUNTIF($A$2:$A,A2) in the helper column, where column A holds your key, and fill it down. Anything returning a number above 1 has a repeat somewhere below it.
Step 4: Remove Duplicates in Microsoft Excel
Select any single cell inside your data. Excel works out the surrounding range from that one click, which is safer than dragging a selection you can get wrong. Then choose Data > Remove Duplicates from the Data Tools group on the Data tab.
Three options in that dialog matter more than the rest:
- My data has headers is normally ticked. Clear it only if your first row really is data, because Excel will otherwise treat your column titles as data and may delete a whole row of headers.
- Case sensitive is normally unticked, meaning Excel treats John Smith and JOHN SMITH as the same value. Tick it and those two rows become separate records again.
- The column checkboxes are your selection from Step 3. Untick everything you do not want compared.
Click OK. Excel reports how many duplicate rows it found and how many unique rows remain. Write that number down; it is your only record of what happened.
The same dialog has a Count Duplicates button that runs the identical selection without deleting anything. Use it when you want the number of affected rows before committing.
Step 5: Remove Duplicates in Google Sheets
Open the Data menu and choose Data cleanup > Remove duplicates. Confirm that Data has header row is ticked so your column titles are excluded, then tick or untick each column to match your Step 3 decision.
Click Remove duplicates. Sheets shows a confirmation dialog stating how many duplicate rows were found and how many unique rows remain, then deletes them. There is no preview of which rows will go, so the sorted inspection in Step 2 is your safety net.
Sheets also has Data cleanup > Trim whitespace in the same menu. Running it before the dedupe catches the most common reason duplicates survive, a stray space at either end of a value.
Step 6: Verify and Save the Cleaned Data
Check the reported count against your expectation from Step 2. Then open three or four rows at random and confirm they are complete records with intact headers and IDs.
Scroll to the bottom and confirm the last row is where you expect. A tool run against the wrong range looks identical to a correct one until the totals do not add up.
Now save. Undo works reliably straight after the operation, but once the file is closed the change is gone, which is why the backup in Step 1 exists.
Common Mistakes
These are the failures that cost people real data, and all of them are fixable. Anyone learning how to remove duplicates in a spreadsheet meets at least one of them.
You deleted the wrong rows. Undo straight away, restore from the copy, and re-check your column selection. If you ticked a single name column, repeat records went with the duplicates.
You treated repeated names as duplicates. Switch to an ID column and run the tool again. Names repeat; identifiers rarely do.
The header row was never detected. Tick My data has headers in Excel or Data has header row in Sheets. Without it your column titles become a duplicate candidate and can be deleted.
You checked too many or too few columns. Too many leaves accidental duplicates in place. Too few merges separate records into one.
You worked on the only copy. Every other mistake on this list is recoverable. That one is not.
You removed rows that needed merging instead. If each duplicate row carries a number you want to keep, such as spend or quantity, total them first with =SUMIFS($C:$C,$A:$A,A2) in a helper column, then dedupe.
The Remove Duplicates button is greyed out in Excel. A protected sheet, merged cells inside the range, or a selection that mixes cells and columns all disable it. Remove sheet protection from the Review tab, unmerge the cells with Merge & Center, and click one plain cell inside the data before retrying. A workbook saved in the older .xls format also hides the command.
Duplicates that look identical are still there. That is a matching problem, not a tool problem. Normalise with =LOWER(TRIM(A2)) in a helper column, copy the results, and paste them back over the original as values before running the cleanup again.
Frequently Asked Questions
Does removing duplicates in Excel delete every row with a repeated name?
Only if the name column is one of the columns you tick. If you tick name plus order date, two Maria Garcia rows on different dates both stay. If you tick name alone, Excel keeps one and deletes the rest, including genuine separate records. Choose the column, or column set, that uniquely identifies a record, such as an order ID or customer number, and tick only that. Repeats in the name column then survive, which is usually what you want.
Can I remove duplicates based on two columns in Excel or Google Sheets?
Yes, and this is the normal way to do it. In Excel, tick both columns in the Remove Duplicates dialog so a row counts as duplicate only when both values match another row. In Google Sheets, tick both boxes in the Data cleanup dialog. Leave other columns unticked, or the comparison tightens and genuine duplicates survive. Two columns work well when a record is defined by a pair, such as order ID plus product SKU.
How do I remove duplicate rows but keep blank cells?
Blanks are treated as a value, so empty rows can be treated as duplicates of one another and collapsed. Clear truly empty rows first by filtering on a populated column and deleting what shows, then run the cleanup. In Google Sheets use Data cleanup and Trim whitespace before removing duplicates, since a cell holding one space behaves differently from an empty cell. To keep blanks while summarising elsewhere, build a clean list with the UNIQUE function instead of deleting anything.
How do I find duplicates without deleting them first?
Excel has a Count Duplicates button in the Remove Duplicates dialog that reports how many matches your selection contains without removing a single row. For highlighting, apply conditional formatting with Highlight Cells Rules, then Duplicate Values, over the column you suspect. In Google Sheets use Format, then Conditional formatting, then Custom formula is text contains with COUNTIF, where the range covers the column above the current row. Both leave the data untouched for review.
Can I undo removing duplicates after saving the spreadsheet?
Not in the usual way. Undo works immediately after the operation because Excel and Google Sheets keep a session history, but saving and closing the file ends that history, and reopening an older version depends on version history being switched on for the file. Your dependable option is the copy you made in the first step. In Google Sheets, File, then Version history, then See version history sometimes restores an earlier state, but treat it as a last resort rather than a plan.
Conclusion
Start by copying the file, then decide which single column or column pair identifies a record. Knowing how to remove duplicates in a spreadsheet without losing valid rows comes down to that one choice, plus checking the reported count before you save.
If you find yourself doing this every month, the repeat version is simpler than the fix for a bad deletion.