Excel has three built-in ways to handle duplicates, and which one you use depends on whether you want to delete them permanently or just hide them temporarily

The fastest method is the Remove Duplicates feature, which deletes duplicate rows in place. You select your data, go to the Data tab, click Remove Duplicates, choose which columns to check, and Excel deletes the matching rows when ready. This is permanent — you cannot undo it after you close the file, so save a backup first.

If you want to see which rows are duplicates before deleting anything, use conditional formatting to highlight them in color. This lets you review what will disappear and manually delete only the ones you want gone. The third option, filtering, temporarily hides duplicates so you can work with unique rows only, but does not delete anything.

Each method works best for different situations. Remove Duplicates is fastest when you trust your data and want a clean list. Conditional formatting is safest when you need to check what matches before deletion. Filtering is useful when you need the duplicates later or want to keep your original file unchanged.

Key Takeaways

  • Remove Duplicates deletes matching rows permanently, so you must save a backup copy before using it.
  • Conditional formatting highlights duplicate rows in color so you can review them before deciding what to delete.
  • The Remove Duplicates tool checks only the columns you select, so you can ignore columns that legitimately repeat (like a city name appearing in many rows).
  • Filtering hides duplicates without deleting them, which is useful if you need to restore them later or want to keep your original data intact.

Using Remove Duplicates for a permanent cleanup

Select all the data you want to check, including headers. Click the Data tab at the top of the ribbon, then find and click Remove Duplicates (it is in the Data Tools group). A dialog box opens showing all your columns. Uncheck any columns that should not be part of the duplicate check — for example, if you have a column for "Date Added" and two people with the same name added on different dates, you probably want to keep both rows, so uncheck "Date Added".

Excel considers a row a duplicate only if all the checked columns match exactly. Once you click OK, the duplicates vanish when ready. Excel tells you how many rows it removed. This action cannot be undone after you close the file, so close without saving first if you made a mistake, or use Ctrl+Z right away if you catch the error before closing.

This method works well for customer lists, inventory records, or any data where you know duplicates are errors and you want them gone fast. It is less useful if you are unsure which duplicates to keep or if your data has legitimate repetition in some columns.

Using conditional formatting to review duplicates before deletion

Select your data and go to the Home tab. Click Conditional Formatting, then Highlight Cell Rules, then Duplicate Values. Excel highlights every duplicate in the color you choose (usually light red). Now you can see exactly which rows match before you delete anything.

Once you have reviewed the highlighted rows, you can delete them manually by selecting each row number on the left side, right-clicking, and choosing Delete. Or, if you are confident about what you see, you can use Remove Duplicates on the same selection — the highlighting gives you confidence that you are removing the right rows.

This approach takes longer than Remove Duplicates alone, but it is much safer. You can see the context of each duplicate (the full row, not just the matching columns) and decide whether it is truly a duplicate or a legitimate repeat. You also keep your original file intact until you manually delete, so you can close without saving if you change your mind.

Using filters to hide duplicates without deleting them

Select your data and go to the Data tab. Click Filter (the button with a funnel icon). Small dropdown arrows appear in your header row. Click the dropdown arrow in any column, uncheck Duplicates at the bottom of the list, and click OK. Excel hides all rows that are exact duplicates in that column.

Filtering is temporary — the hidden rows are still in your file. If you need to see them again, click the filter dropdown and check Duplicates again. This method is useful when you want to work with unique rows only (for example, to count how many different customers you have) but you do not want to delete the original data.

Filtering also lets you filter by multiple columns at once. You can hide duplicates in Column A and also hide duplicates in Column B, so you see only rows that are unique in both columns. This is more flexible than Remove Duplicates if you need to experiment with different definitions of "duplicate".

What "duplicate" actually means in Excel

Excel considers two rows duplicates only if every checked column matches exactly. The match is case-insensitive (John and john are the same) but space-sensitive (John and John with an extra space are different). Numbers must match exactly — 100 and 100.0 are treated as the same, but 100 and 100.00 may not be, depending on how they are formatted.

This is why choosing which columns to check matters. If you have a spreadsheet of orders with columns for Customer Name, Order Date, and Amount, and you check all three columns, two orders from the same customer on the same day for the same amount will be flagged as duplicates. But if you check only Customer Name, every order from that customer will be flagged, even if they ordered different things on different days.

Before you remove duplicates, think about what a true duplicate means in your context. Is it a row where every field matches? Or only certain fields? The Remove Duplicates dialog lets you uncheck columns, so you can define "duplicate" the way your data needs.

Handling partial matches and near-duplicates

Excel's built-in tools only find exact matches. If you have "John Smith" in one row and "Jon Smith" in another, Excel will not flag them as duplicates. The same is true for extra spaces, different capitalization in some columns, or typos.

If you suspect your data has these kinds of near-duplicates, scan the data manually first or sort by the column you are most concerned about (usually a name or ID) so similar entries sit next to each other. You can then spot typos and fix them before running Remove Duplicates. Alternatively, use Find and Replace to standardize formatting — for example, convert all names to title case or remove leading and trailing spaces — before checking for duplicates.

For large datasets with many potential typos, consider using a pivot table or a COUNTIF formula to find rows that appear only once. This does not remove duplicates, but it helps you understand your data quality before you decide what to delete.

Recovering from a Remove Duplicates mistake

If you used Remove Duplicates and when ready realized you made a mistake, press Ctrl+Z to undo, as long as you have not closed the file. This restores the deleted rows.

If you closed the file without saving after the deletion, reopen it and the duplicates will still be there — the deletion was not saved. If you closed and saved, the duplicates are gone permanently unless you have a backup. This is why saving a backup before using Remove Duplicates is essential.

If you do not have a backup and need to recover the data, check whether your file storage system (OneDrive, Google Drive, or your company's backup) keeps version history. OneDrive and Google Drive both let you restore earlier versions of files for a limited time. Your IT department may also have backups of shared drives.

Frequently Asked Questions

Can I undo Remove Duplicates after I close the file?

No. Once you close the file after using Remove Duplicates, the deletion is permanent. You can undo it only if you have not closed the file yet (press Ctrl+Z). Always save a backup before using Remove Duplicates.

What if I want to keep one copy of each duplicate instead of deleting all of them?

Use conditional formatting to highlight the duplicates, then manually delete the rows you do not want. This gives you control over which copy to keep. Remove Duplicates deletes all but one copy automatically, so you cannot choose which one survives.

Does Remove Duplicates check the entire row or just one column?

It checks only the columns you select in the dialog box. If you want to find rows where Column A matches Column B, uncheck all other columns. If you want to find rows where all columns match, leave all columns checked.

Will filtering remove duplicates from my file permanently?

No. Filtering hides duplicates temporarily but does not delete them. Your original data stays in the file. You can unhide the duplicates anytime by removing the filter.

How do I find duplicates across multiple sheets in the same workbook?

Excel's built-in tools work on one sheet at a time. To find duplicates across sheets, copy all the data into a single sheet, use Remove Duplicates or conditional formatting, then delete the temporary sheet. Alternatively, use a COUNTIF formula that references cells from multiple sheets to identify matches.