The fastest way to remove duplicates in Excel
Excel has a built-in tool that finds and deletes duplicate rows in seconds. Select the data range you want to check, go to the Data tab, click Remove Duplicates, and choose which columns to compare. Excel then deletes any rows where those columns match exactly.
This method works best when your data is already organized in a table or a clean range. If your spreadsheet has headers, make sure to include them in your selection — Excel will treat them as data otherwise and may delete them by mistake.
The Remove Duplicates tool is available in Excel 2007 and later on Windows, and Excel 2011 and later on Mac. If you are using an older version or Excel Online, you will need to use a different method.
Key Takeaways
- The Data tab's Remove Duplicates button deletes entire rows where selected columns match, and it works in seconds on large datasets.
- You choose which columns to compare — Excel can check all columns or only specific ones, depending on what "duplicate" means for your data.
- The tool permanently deletes rows, so save a backup copy of your spreadsheet before running it.
- If Remove Duplicates is not available, you can use filtering, sorting, or conditional formatting to find duplicates manually.
How to use the Remove Duplicates button step by step
Start by selecting all the data you want to check, including headers if your data has them. Click anywhere in your data range, then go to the Data tab at the top of the ribbon. Look for the Remove Duplicates button — it is usually in the Data Tools group on the right side of the ribbon.
When you click it, a dialog box opens showing all the columns in your selection. By default, all columns are checked. If you want Excel to consider a row a duplicate only when certain columns match, uncheck the columns you want to ignore. For example, if you have a list of customer names and phone numbers, you might check only the phone number column so that duplicate phone numbers are removed even if the names are spelled differently.
Click OK and Excel removes the duplicate rows. A message tells you how many rows were deleted. The remaining data stays in place, and the deleted rows are gone permanently — you cannot undo this with the standard Undo button if you close the file, so save a backup first if you are unsure.
What counts as a duplicate in Excel
Excel considers a row a duplicate when the values in the columns you selected match exactly. The match is case-insensitive, so "John" and "john" are treated as the same. Spaces and punctuation matter — "John Smith" and "John Smith" (with two spaces) are different.
If you select multiple columns, all of them must match for a row to be flagged as a duplicate. For instance, if you check both Name and Email, Excel only deletes rows where both the name and email are identical to another row. If only the name matches but the email is different, both rows stay.
When duplicates are found, Excel keeps the first occurrence and removes the rest. If you have three rows with the same phone number, Excel deletes rows two and three and keeps row one. The order of your data matters, so sort before removing duplicates if you want to control which copy survives.
Finding duplicates without deleting them
If you want to see which rows are duplicates before removing them, use conditional formatting instead. Select your data range, go to Home, click Conditional Formatting, choose Highlight Cell Rules, and then Duplicate Values. Excel highlights all duplicate entries in a color you choose, so you can review them first.
This method highlights individual cells rather than entire rows, and it does not delete anything — it just marks duplicates so you can decide what to do with them. You can then manually delete rows, or use the filter feature to hide non-duplicates and work with only the duplicates.
Another option is to use the Advanced Filter feature. Select your data, go to Data, click Advanced, check the box for "No duplicates," and Excel shows you only the unique rows in place. This does not delete anything; it just filters the view temporarily.
Removing duplicates when the Remove Duplicates button is not available
If you are using Excel Online or an older version of Excel, the Remove Duplicates button may not exist. In that case, you can use the Standard Filter to hide duplicates. Select your data, go to Data, click Filter, then Standard Filter. Set up rules to show only rows that meet your criteria, or use the AutoFilter dropdown arrows to manually uncheck duplicate values.
Another workaround is to sort your data by the column most likely to contain duplicates, then scroll through and delete matching rows by hand. This is slower but works in any version of Excel. After sorting, identical values appear next to each other, making duplicates easy to spot.
You can also copy your data into a pivot table, which automatically groups identical rows together. This does not delete the original data, but it shows you the unique values and how many times each appears, which helps you decide what to remove.
Handling duplicates across multiple sheets
The Remove Duplicates tool only works within a single sheet at a time. If your data is split across multiple sheets and you want to remove duplicates across all of them, copy all the data into one sheet first, then run Remove Duplicates on the combined range.
To combine sheets, create a new blank sheet, then use formulas or copy-paste to bring data from each sheet into one place. If the sheets have the same structure, you can select all the data from the first sheet, copy it, then paste it below the last row of the previous sheet, repeating for each sheet. Once all data is in one location, use Remove Duplicates as normal.
After removing duplicates from the combined sheet, you can split the data back into separate sheets if needed, or keep it in one place depending on your workflow.
Preventing duplicates from appearing in the first place
To stop duplicates from being entered in the future, use data validation. Select the column where you want to prevent duplicates, go to Data, click Data Validation, and set up a rule that rejects duplicate entries. This does not remove existing duplicates, but it stops new ones from being added.
You can also use a formula to flag duplicates as they are entered. In a helper column, use the COUNTIF function to count how many times each value appears in the range. If the count is greater than one, the cell is a duplicate. This alerts you to duplicates without automatically deleting them, giving you a chance to review before taking action.
Frequently Asked Questions
Can I undo Remove Duplicates after I close the file?
No. Once you close the file without saving, the undo history is lost and you cannot recover the deleted rows. Always save a backup copy of your spreadsheet before using Remove Duplicates, or use Undo immediately if you make a mistake.
What if I only want to remove duplicates in certain columns, not all of them?
In the Remove Duplicates dialog, uncheck the columns you want to ignore. Excel will then only compare the checked columns when deciding whether a row is a duplicate. For example, if you uncheck the Date column, two rows with different dates but identical names will be treated as duplicates.
Does Remove Duplicates work on filtered data?
No. Remove Duplicates processes all rows in your selection, including hidden ones. If you have filtered your data to show only certain rows, you must remove the filter first, or select only the visible cells you want to check.
How do I remove duplicates but keep the last occurrence instead of the first?
Sort your data in reverse order by a unique identifier or timestamp, then run Remove Duplicates. Since Excel keeps the first occurrence after sorting, reversing the order makes the last original occurrence become the first in the sorted list, which Excel then keeps.
Can I remove duplicates based on partial text matches?
No. Remove Duplicates only works on exact matches. If you need to find rows where text is similar but not identical, use conditional formatting with a formula instead, or manually review the data and delete rows you consider duplicates.