How to Remove Duplicates in Excel Without Losing Data

To remove duplicates in Excel safely, work in three passes: highlight the duplicates, preview the unique rows with a filter, and only then delete with Remove Duplicates. Microsoft's own help pages recommend this order, because the delete step is permanent.

One thing to know before you start: Excel decides what counts as a duplicate in ways that can surprise you. The section on displayed values below explains why two identical-looking dates might survive, or two different formulas might not.

Step 1: Copy your data before you touch it

Microsoft's Find and remove duplicates page says Remove Duplicates permanently deletes the duplicate data, and advises copying or moving the original data to another worksheet first. Do that now. Right-click the sheet tab, choose Move or Copy, tick Create a copy, and keep the copy untouched until you've checked the result.

Step 2: Highlight duplicates in Excel

This shows you what Excel thinks is a duplicate without changing anything.

1. Select the cells you want to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. In the box next to values with, pick a format, then select OK.

The Mac version of Microsoft's page lists the path as Home > Styles > Conditional Formatting, with the options in a New Formatting Rule dialog, per Filter for or remove duplicate values. One limit: Microsoft says Excel can't highlight duplicates in the Values area of a PivotTable report.

Highlighting works cell by cell, so it is good for spotting repeats in a single column. Remove Duplicates works on whole rows, which is why the next step matters.

Step 3: Preview the result with a unique-values filter

Filtering for unique values only hides duplicates, while removing them deletes them, according to Microsoft's filter page. Microsoft recommends filtering first to confirm the result. Here's how:

1. Select the range, or click any cell inside a table.
2. On the Data tab, in the Sort & Filter group, click Advanced.
3. Choose Copy to another location and type a cell reference in the Copy to box. (You can also pick Filter the list, in-place, but copying leaves the original data unaffected.)
4. Tick Unique records only, then click OK.

Scroll through the copy. If a row you expected to disappear is still there, or one you need has gone, check the displayed-value rules below before you go further.

Why Excel misses some duplicates and catches others

Microsoft's page defines a duplicate as a row where all values exactly match all values in another row. And it compares the value displayed in the cell, not the value stored. That leads to three cases:

• Same date, different format: not a duplicate. A date shown as 12/8/2017 in one cell and Dec 8, 2017 in another counts as unique.
• Same number, different number format: not a duplicate. 1.00 and 1 are treated as different.
• Different formulas, same result: a duplicate. These two cells match because both display 1:

=2-1
=3-2

The fix for the first two is to make the formatting consistent. Select the column, open Format Cells, and apply one date or number format to every cell, then run your preview again.

Step 4: Clear outlines and subtotals

Microsoft's Mac-specific page says plainly that you can't remove duplicates from data that is outlined or has subtotals, and you need to remove both first. The general Windows-facing page gives the same advice as a tip. If Remove Duplicates won't work on your sheet, this is the first thing to check.

Step 5: Remove duplicates, choosing the right columns

1. Select the range with the duplicates, or click a cell inside the table.
2. Go to Data > Remove Duplicates. (The Mac page places it in the Data Tools group.)
3. Under Columns, tick the columns Excel should compare. With many columns, clear Select All and tick only the ones you need.
4. Click the confirm button. Microsoft's main page calls it OK; the Mac page calls it Remove Duplicates.

The columns you tick decide what a duplicate means. Excel looks for rows that match across every ticked column. To remove duplicate rows based on one column, such as an email address, tick only that column. Microsoft's own example unticks a January column that holds price information, so prices don't count when Excel judges matches.

Neither Microsoft page says which of the matching rows Excel keeps. If that matters, check the result against your copy rather than assuming.

Only values inside the selected range or table are affected, and nothing outside it is altered or moved, per Microsoft.

Step 6: Sanity-check the counts

Excel shows how many duplicates it removed and how many unique values remain. Microsoft notes these counts might include empty cells, spaces and similar, so a number that looks a little high isn't necessarily wrong. Compare against your copy from Step 1, and delete the copy only when you're satisfied.

Which Excel versions this covers

The filter page used here applies to Excel for Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac, per its Applies To line. The menu names match Microsoft's general Find and remove duplicates page. This guide doesn't cover Excel for the web.

Doing the same job in Google Sheets? See How to Highlight Duplicates in Google Sheets (and Remove Them Safely).