Excel Remove Duplicates but Keep One Copy in a List

To remove duplicates in Excel but leave one copy, select your data and choose Data > Remove Duplicates; select the columns that define a duplicate, then confirm. Excel keeps the first matching row in the current order and removes later matches, so make a copy of important data before proceeding.

What Remove Duplicates means

Excel’s Remove Duplicates command looks for repeated values in the columns you choose, keeps one occurrence, and deletes the other matching entries from the selected range. It does not erase every copy: if a value appears several times, one remains. This is useful for tasks such as creating a list of customers, removing repeated product records, or cleaning up imported data.

The important choice is what counts as a match. If you select one column, Excel compares that column alone. If you select several, it compares the combination of values in those columns. For example, two rows with the same first name but different email addresses are duplicates when you check only the first-name column, but not when you check both first name and email.

The command changes the data in place. It does not merely hide duplicates or mark them for review. Because it removes rows or entries from the selected range, consider saving a separate copy or using Undo immediately if the result is not what you intended.

Remove duplicates step by step

In Excel for Microsoft 365 on Windows, use these steps:

Click a cell inside the data, or select the full range you want to clean. Include every column whose row information needs to stay together.
Open the Data tab and, in the Data Tools group, select Remove Duplicates.
In the dialog box, check My data has headers if the first row contains column names. This prevents Excel from treating the headings as ordinary records.
Check the columns Excel should use to identify duplicates. Select all columns for fully repeated rows, or only the key columns when matching should depend on a particular field.
Select OK. Excel reports how many duplicate values were removed and how many unique values remain. Review the result, especially the first and last records in the range.

If you selected only one column inside a larger table, Excel may ask whether to expand the selection or continue with the current selection. Choose to expand when you want entire matching records removed together. Continuing with only one column can separate values from the other information in their rows, which is usually not what a person cleaning a table intends.

In Excel for Mac, the command is also on the Data tab as Remove Duplicates, and the dialog uses the same basic header and column choices. Excel for the web also offers a Remove Duplicates command on the Data tab, although the ribbon layout can differ from the Windows desktop app. Always check that the selected range covers the data you mean to change.

Choose the columns that define a duplicate

Column selection determines the outcome, so decide what makes two records interchangeable before clicking OK. If a customer list has Name, Email, and City columns, selecting Name alone can keep one person named Jordan Lee and remove another Jordan Lee who is a different customer. Selecting Email instead is often a better key if each person is expected to have a distinct email address. Selecting all three columns only removes rows where all three values match.

Use a narrow set of columns when the goal is one row per category, email, or product code. Use all relevant columns when the goal is to remove only exact repeated records while preserving legitimate differences. A practical checklist is:

Ask what field or combination of fields identifies one real-world record.
Check whether blank cells should count as matching records in your situation.
Keep descriptive columns in the selected range so Excel removes the whole row, not just the key value.
Before deleting, inspect a few suspected duplicates to confirm they really represent the same record.

For instance, suppose two rows have the same order number but different shipment dates. If an order number should occur only once, comparing on that column will retain one row and remove the other. If each shipment is a separate record, order number alone is not a suitable duplicate key; you may need to compare order number together with shipment date or another identifying field.

Which copy does Excel keep?

Excel keeps the first occurrence it encounters in the selected range and removes later occurrences that match the chosen columns. “First” means first in the current row order, not oldest, newest, most complete, or best. If the rows are sorted by date, for example, sorting oldest to newest before removing duplicates can make the oldest matching row the one that remains. Sorting newest to oldest can make the newest one remain.

Before removing duplicates, sort the range if you have a deliberate rule for which copy should survive. If you want to retain the record with the most complete notes, a valid status, or a particular source, decide how to rank those records first. A sort is not a substitute for checking the data: if two rows differ in meaningful ways but match on the columns you selected, Excel will still discard one of them.

When a range is formatted as an Excel table, keep the whole table in view and ensure all columns are included in the operation. Table filters can hide rows, so clear or review filters if you need to understand the entire dataset. Afterward, check the record count and scan the retained entries to confirm that the expected version of each record remains.

Review duplicates without deleting them

If you are unsure which rows should go, identify repeated values first and review them before using Remove Duplicates. Conditional formatting can highlight repeats: select the cells in the relevant column, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is a visual check, not a deletion command, and it does not automatically decide which row is the right one to keep.

A helper column can also count each occurrence. With names in A2:A8, enter =COUNTIF($A$2:A2,A2) in B2 and fill it down through B8. For the example values Maya, Leo, Maya, Noor, Leo, Ava, Noor, the results are 1, 1, 2, 1, 2, 1, 2. A result of 1 marks the first occurrence; a number greater than 1 marks a later repeat. You can filter the helper column to review those rows before deciding whether to delete them.

This check is useful when duplicate-looking entries may not be identical in meaning. For example, a repeated product name could refer to separate sizes or models. The count formula above compares the values in the name column only, so it is not enough if another column defines a distinct item. In that case, build the review around the correct key or use Remove Duplicates with the intended combination of columns.

Use UNIQUE when you want a separate list

If you want a deduplicated list without changing the source, Excel 2021 and Microsoft 365 include the UNIQUE function. With the source names in A2:A8, enter =UNIQUE(A2:A8) in an empty cell. The example Maya, Leo, Maya, Noor, Leo, Ava, Noor produces a spilled result of Maya, Leo, Noor, Ava. The original values stay where they are, and the result updates when the source range changes.

UNIQUE is a better fit for a report or a quick list of distinct values than for permanently cleaning a table. It returns results in the order of their first appearance; it does not sort them alphabetically. If you need a sorted output, you can use =SORT(UNIQUE(A2:A8)) in Microsoft 365 or Excel 2021. Keep the formula result separate from the source, and remember that a spill range needs enough empty cells below or beside it to display all results.

For distinct records rather than distinct names, UNIQUE can work on a multi-column range too. With complete records in A2:C100, =UNIQUE(A2:C100) returns distinct rows based on the combination of values across the columns. This is a non-destructive alternative, but it requires a version with dynamic array functions, such as Microsoft 365 or Excel 2021. If the goal is to remove repeated rows from the original table itself, the Data tab command is more direct.

Common problems and safe fixes

A common surprise is that selecting too few columns removes records that only appear to be duplicates. If two rows share a last name but have different email addresses, selecting Last Name alone makes them match. Undo at once with Ctrl+Z on Windows or Command+Z on Mac if the result is wrong, then repeat the process with the right columns. Undo may not be available after other actions or after closing the workbook, so a backup is safer for important data.

Another issue is inconsistent text. Extra spaces, imported characters, or inconsistent spelling can make entries that look alike behave differently, while capitalization alone is generally not a reliable way to distinguish records in Excel’s duplicate-removal comparison. Clean or standardize the key values before removal if the data came from multiple systems. For example, check for leading or trailing spaces and use a separate cleaned helper column for review rather than making changes to the original values without a backup.

Also check that the data is one continuous, rectangular range with headers correctly identified. Blank rows or columns can affect what Excel recognizes as the data area. If the command reports an unexpected number removed, undo and inspect the range, the header setting, and the selected columns. The dialog’s count is a useful confirmation, but it does not prove that the selected definition of “duplicate” was correct.

Remove Duplicates compared with other options

Remove Duplicates is best when you have decided what constitutes a duplicate and want to change the existing data. It is quick, built into Excel, and preserves the first matching entry. Its trade-off is that it deletes information from the selected range and does not ask you to choose the best record from each group.

Conditional formatting and COUNTIF are better for investigation because they help you find repeats while keeping the underlying records available. UNIQUE is useful when you need a separate, automatically updating list. A PivotTable can summarize categories and counts without deleting the original records, which is useful when the goal is to understand frequency rather than clean the source. These options solve related but different problems: finding repeats, reporting distinct values, and deleting duplicate entries are not the same task.

Frequently Asked Questions

Does Excel leave one copy when I remove duplicates?
Yes. For each match based on the columns you select, Excel keeps the first occurrence in the current order and removes later matching entries.

How do I remove duplicates but keep the entire row?
Select the full data range and choose Data > Remove Duplicates. In the dialog, select the columns that define a match. Including the full range lets Excel remove the matching row’s related values together.

Can I remove duplicates based on just one column?
Yes. Select only that column in the Remove Duplicates dialog, while keeping the full range selected so the entire corresponding row is removed. Confirm that the one column really identifies records that should be treated as duplicates.

Can I get a unique list without deleting the original data?
Yes. In Microsoft 365 or Excel 2021, use =UNIQUE(A2:A8) with your source values in A2:A8. The result appears separately and leaves the source unchanged.

Can I undo Remove Duplicates?
Usually, you can undo it immediately with Ctrl+Z on Windows or Command+Z on Mac. If you have continued working, closed the workbook, or cannot undo the operation, restore a saved copy or use a backup instead.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.