Duplicate rows make totals wrong and reports confusing. Excel gives you several ways to deal with them, from a one-click button to a formula that keeps your original data untouched. Here are four methods, and when to use each one.
Quick answer
Select your data, go to Data → Remove Duplicates, tick the columns that must match, and click OK. Excel deletes the extra rows and tells you how many it removed. Make a copy of the sheet first, because this changes your data.
Sample data
| Invoice | Customer | Amount |
|---|---|---|
| INV-101 | Al Noor Trading | 250 |
| INV-102 | Gulf Supplies | 480 |
| INV-101 | Al Noor Trading | 250 |
| INV-103 | Bright Office | 125 |
| INV-102 | Gulf Supplies | 480 |
INV-101 and INV-102 each appear twice.
Method 1: The Remove Duplicates button (fastest)
- Click any cell inside your data.
- Go to Data → Remove Duplicates.
- Leave My data has headers ticked.
- Choose which columns decide a duplicate. Tick only Invoice if the invoice number alone should be unique; tick all columns if the whole row must match.
- Click OK. Excel shows a message such as “2 duplicate values found and removed; 3 unique values remain.”
Excel keeps the first occurrence and deletes the ones below it. Sort your data first if you want to keep a particular row (for example, the newest).
Method 2: Highlight duplicates first (safest)
If you want to check before deleting anything:
- Select the column, for example the Invoice column.
- Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values and click OK.
- Review the highlighted cells, then filter by colour (Ctrl + Shift + L, then the filter arrow → Filter by Color) to see them together.
To flag whole duplicate rows with a formula, add a helper column with =COUNTIF($A$2:A2, A2)>1. It shows TRUE for the second and later copies only, so you can filter and delete exactly those. More highlighting ideas are in our conditional formatting guide.
Method 3: The UNIQUE function (keeps the original)
In Microsoft 365 and Excel 2021 or later, type this in an empty cell:
=UNIQUE(A2:C6)
Excel “spills” a clean list with each row once, and your original data stays exactly as it was. When the source changes, the unique list updates by itself. For a list of unique customers only, use =UNIQUE(B2:B6). Add SORT to sort it: =SORT(UNIQUE(B2:B6)).
In older Excel versions, use Data → Advanced (in the Sort & Filter group), choose Copy to another location, tick Unique records only, and pick where to paste the result.
Method 4: Power Query (for repeat jobs)
If you clean the same export every week, let Power Query do it:
- Click inside the data and choose Data → From Table/Range.
- Select the column(s) that should be unique.
- Choose Home → Remove Rows → Remove Duplicates.
- Click Close & Load. Next time, just click Refresh.
New to Power Query? Start with our Power Query tutorial.
Which method should you use?
| Situation | Best method |
|---|---|
| Quick one-time cleanup | Remove Duplicates button |
| You want to check before deleting | Highlight duplicates |
| Keep the original data untouched | UNIQUE function |
| The same file every week or month | Power Query |
Why duplicates sometimes are not found
- Extra spaces: “Gulf Supplies” and “Gulf Supplies ” look the same but are different. Clean them with
=TRIM(B2)first. - Numbers stored as text: 101 and “101” are not treated as equal. Convert the column to numbers.
- Different capitalisation: Remove Duplicates ignores upper and lower case, but some formulas do not.
FAQ
Can I undo Remove Duplicates?
Yes, press Ctrl + Z straight away. After you save and close, the deleted rows are gone, so keep a backup copy.
How do I remove duplicates in Google Sheets?
Select the data and choose Data → Data cleanup → Remove duplicates. The UNIQUE function works the same as in Excel.