To delete duplicates in Excel and keep one copy, select the entire data range, choose Data > Remove Duplicates, select the columns that define a duplicate, and confirm. Excel keeps the first matching record it finds and removes later matches. Before you begin, decide whether a duplicate means an identical whole row or a match in only certain columns, and make a copy of your data so you can recover it if the result is not what you intended.
What You’ll Need
- An Excel workbook containing the records you want to clean.
- A clear definition of what counts as a duplicate. It might mean every cell in two rows matches, or it might mean only a key such as an email address or product ID matches.
- A backup, such as a copied worksheet or a saved copy of the workbook. Remove Duplicates changes the selected data.
Step-by-Step
- Make a working copy. Save a copy of the workbook, or right-click the worksheet tab and choose Move or Copy, select Create a copy, and click OK. You can also copy the data to a new sheet. Work on the copy if the records matter; this lets you compare the cleaned results with the original and restore information that Excel removed.
- Check the layout before selecting anything. Your data should have one header row and a consistent set of columns, with one record per row. Remove completely blank rows inside the data, and check that there are no merged cells in the range. If there are extra notes or totals immediately beside the records, decide whether they belong in the range before continuing.
- Select the complete data range or table. Click a cell inside an Excel table and use the table as your data set. For a regular range, select all columns and rows belonging to the records, including the header row if one exists. Do not select only the column you want to compare: Excel needs the full range so it can remove complete records rather than leave related values out of alignment.
- Open the duplicate-removal dialog. On Excel for Microsoft 365 on Windows, go to Data > Remove Duplicates. If your selection includes headers, check My data has headers in the dialog. Verify the checkbox is on only when the first row contains column names; otherwise Excel may treat the first record as a header and not compare it like the other rows.
- Choose how Excel should identify a duplicate. In the Columns list, check every column that should be part of the comparison. For identical-row duplicates, leave all columns checked. To keep one record per email address, for example, check Email only. Excel will consider two rows duplicates when the checked values match, even if their unchecked values differ.
- Review the selected columns carefully. Column names appear in the dialog when Excel recognizes headers. Uncheck fields that are allowed to differ, such as an order date or notes, if they should not make a record unique. Keep fields checked when a difference in that field means the records are genuinely different. If you are unsure, cancel and inspect a few examples before removing anything.
- Run the removal. Click OK. Excel displays a message reporting how many duplicate values were removed and how many unique values remain. Read the numbers and click OK to close the message. Excel keeps the first record for each matching set, based on the current row order, and removes subsequent matches from the selected range.
- Inspect the cleaned data. Check the number of remaining records and review several cases that previously had duplicates. Confirm the retained row contains the details you need, especially if you compared on just one key column. If the wrong version was kept, undo immediately with Ctrl+Z on Windows or Command+Z on Mac, sort the records so the preferred version comes first, and run the tool again.
- Save and verify the result. Save the workbook after checking the output. If other sheets, formulas, charts, or reports refer to the old rows, make sure they still behave as expected. Deleting rows can affect references and totals, so check important summaries rather than assuming that removing duplicates has left every dependent calculation unchanged.
The same basic workflow is available in Excel for Mac and Excel for the web, though ribbon placement and dialog wording can vary slightly by release. Look for Remove Duplicates on the Data tab. If you do not see the command, confirm that the worksheet is editable and that you are working in Excel rather than a read-only preview.
Common Mistakes
- Selecting too few columns or only one column of cells: Comparing only a key column can be correct, but the selection still needs to cover the full records. If you select a single column and proceed with only that selection, associated details in other columns may not be removed as complete rows. Select the whole table first, then use the dialog to choose comparison columns.
- Checking every column when you intend to compare a key: If a duplicate email has a different spelling in the name field, comparing all columns makes those rows look different. Choose only the fields that define sameness for your task.
- Checking too few columns: If you compare only a shared category, Excel may remove records that are not duplicates in context. For example, two people can share a city. Include a reliable identifier, or use a combination of fields that uniquely describes the record.
- Keeping the wrong version: Excel keeps the first match in the current order; it does not automatically choose the newest, most complete, or best record. Sort deliberately before removing duplicates, and ensure the preferred row is first.
- Misidentifying the header row: If My data has headers is unchecked when headers are present, Excel can treat a label as a record. If it is checked when the first row is data, that first record can be excluded from comparison. Confirm the setting before clicking OK.
- Assuming similar text is always identical: Extra spaces, different punctuation, or text-versus-number differences can affect matches. Two cells that look alike on screen may have different underlying values, so check the data if expected duplicates remain.
Tips
- Sort before deduplicating when a particular record should survive. For example, sort a date column from newest to oldest if you want the latest record kept, then run Remove Duplicates using the identifier column. Check that the date values sort correctly and that the sort includes the entire table.
- For a repeatable process, turn the range into a table with Insert > Table and confirm that the table has headers. Tables make it easier to identify and select the full set of records, particularly when the data grows. They do not change the rule that the first matching row is the one retained.
- Use the duplicate dialog as a deliberate choice, not as a general cleanup button. Write down the comparison rule first: for example, same invoice number, or identical customer, product, and date. This makes it easier to select the appropriate columns and explain the result to someone else.
- If you only want to locate duplicates without deleting them, cancel the dialog and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values for a single column. Highlighting helps you review values, but it does not determine whether whole records are duplicates or remove anything.
- Keep an untouched source sheet if you may need to audit the change. Record counts before and after are useful, but they do not show which details were removed. Comparing the source and cleaned copy can help you confirm that the retained records are appropriate.
Troubleshooting
Excel says no duplicates were found. Check whether the selected columns include fields that vary, such as timestamps, spaces, or notes. Try comparing on the intended identifier alone while keeping the full table selected. Inspect a pair of supposed duplicates side by side; differences such as a trailing space or a number stored as text may explain why Excel treats them as distinct.
Excel removed more records than expected. Undo at once with Ctrl+Z on Windows or Command+Z on Mac, before making other changes. Reopen Remove Duplicates and include more columns in the comparison. If you have already continued working and undo is no longer practical, restore the saved backup and repeat the operation with a stricter definition.
Other columns did not move with the removed records, or unrelated data disappeared. Undo and check the selected range. It should cover the complete records and should not include separate notes or totals. When Excel asks whether to expand the selection, choose the option that includes adjacent data only if that adjacent data belongs to the same records. Otherwise, cancel and select the correct range manually.
The preferred copy was not kept. Undo, then sort the complete data set so the row you want appears first within each duplicate group. Run Remove Duplicates again using the same comparison columns. If the data is already gone and you cannot undo, use your backup; Excel does not provide a setting to automatically keep the newest or most complete match.
The command is unavailable or the dialog behaves unexpectedly. Confirm that the sheet is not protected and that the workbook is not in a read-only state. If the data is filtered, clear or review the filter so you understand which records are visible, and select the intended full range. Save a copy before trying again, especially if the workbook contains linked or important records.
Frequently Asked Questions
Does Remove Duplicates keep the first or the last copy?
It keeps the first matching row in the current order and removes later matches. Sort the full data set first if you want a particular version to appear first.
Can I remove duplicates based on one column but keep the other columns?
Yes. Select the entire table or range, then check only the comparison column in the Remove Duplicates dialog. Excel removes subsequent matching records from the selected data and retains the first complete record.
Will Excel delete a row if only some columns match?
Only if the matching columns are the ones you selected in the dialog and their values match another row. With every column selected, the rows must match across all selected fields to count as duplicates.
Can I undo Remove Duplicates?
Usually, you can undo it immediately with Ctrl+Z on Windows or Command+Z on Mac. Undo may no longer be available after closing the workbook or making other changes, so a backup is the safer recovery plan.
Does Remove Duplicates change the original data in a table?
Yes. It removes duplicate records from the selected table or range; it does not create a separate list automatically. Copy the sheet or workbook first if you need to preserve the original records.
Why are two records that look the same not being removed?
They may differ in a selected field because of extra spaces, punctuation, hidden characters, or different underlying data types. Check the raw values and make sure the columns selected in the dialog match your intended duplicate rule.
Related
- How to Copy an Entire Sheet in Excel: Steps and Tips
- How to Copy a Tab in Excel Without Losing Formatting
- How to Copy a Table in Excel Without Losing Formatting
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- COUNTA in Excel: Count Nonblank Cells for Data AnalysisCOUNTA in Excel counts cells that are not empty, making it useful for checking how many entries a…
- Remove Formatting Marks in Word: Show or Hide ThemTo remove formatting marks from view in Word, press Ctrl+Shift+8 on Windows or Command+8 on Mac, or select…
- Excel Format Numbers in Thousands (K)To show Excel values in thousands with a K suffix without changing the underlying numbers, select the cells,…
- How to Lock Cells in Excel for MacTo lock cells in Excel for Mac, select the cells, open Format Cells and check Locked on the…
- How to Accept a Calendar Share in OutlookTo accept a calendar share in Outlook, open the sharing invitation in your email and select Accept or…
- Present in PowerPoint With Notes on Screen: How ToYes. In Microsoft PowerPoint for Microsoft 365 on Windows, turn on Presenter View and use an extended display:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.