How to Delete Duplicate Values in Excel Without Losing Data

How-to basics · Updated November 20, 2026

To delete duplicate values in Excel for Microsoft 365 on Windows, select your data, choose Data > Remove Duplicates, select the columns Excel should compare, and click OK. Excel keeps the first matching entry and removes later ones, so save a backup or make a copy first if you might need the original list.

This tool is useful for cleaning contact lists, product inventories, and other tables with repeated records. The important decision is what counts as a duplicate: an identical entry in one column, or a row whose values match across several columns. Decide that before removing anything.

What You’ll Need

  • An Excel workbook containing the list you want to clean.
  • A clear rule for identifying duplicates, such as matching email addresses or matching every field in a row.
  • A backup of the worksheet or workbook if the original data matters. Removing duplicates changes the selected data, although you can immediately undo the operation.

Step-by-Step

  1. Make a safe copy. If there is any chance you will need to restore the original list, save a separate copy of the workbook with File > Save As, or duplicate the worksheet by right-clicking its sheet tab and choosing Move or Copy. You can also copy the data to another sheet. Do this before cleanup; relying on Undo is less safe after you make more changes or close the workbook.
  2. Check the layout and decide what makes a record unique. A list of email addresses may be considered duplicate when the email address repeats, even if names differ. For a complete transaction record, you may only want to remove rows where the date, customer, and amount all match. Identify the relevant columns before opening the tool. If the list has headings, make sure each heading is in the top row of the selected range.
  3. Select the data range or a cell within it. Click a cell in a contiguous list and Excel will usually detect the full data region. To be more precise, drag across the exact columns and rows you intend to clean. Include all columns that belong to each record if you need to keep rows together. If your data is an Excel table, clicking inside it is generally enough.
  4. Open Remove Duplicates. On the Windows ribbon, choose Data > Remove Duplicates in the Data Tools group. If the selected range includes headings, turn on My data has headers in the dialog. Excel normally detects headers, but verify the setting rather than assuming it is correct. For a table, the column names appear in the list of fields.
  5. Choose the columns Excel should compare. To remove repeated email addresses while keeping the first row for each address, select only the email column. To remove only fully repeated records, select every column that defines the record. If you select just a name column, two people with the same name may be treated as duplicates even when their other details differ. Clear the checkboxes for fields that should not affect the duplicate test.
  6. Review the selection before confirming. Check that the header row is excluded from the comparison and that the right fields are checked. If Excel asks whether to expand the selection because you selected only part of a larger data range, choose Expand the selection when other columns belong to the same records. Choosing to continue with the current selection can remove cells from one column without removing their corresponding cells in other columns, which can scramble the table.
  7. Run the removal and read the result. Click OK. Excel displays a message reporting how many duplicate values were found and removed and how many unique values remain. Review those counts. The first occurrence in the selected range is kept, and later occurrences that match the chosen columns are deleted. The order of the remaining rows follows the original order; Excel does not automatically sort the list.
  8. Check the cleaned data. Inspect the beginning, middle, and end of the list, and confirm that related columns still describe the same records. If the result is not what you intended, immediately use Ctrl+Z on Windows. On Mac, use Command+Z. If you have made other changes since the removal or are unsure what Undo will reverse, restore the backup instead.

In Excel for Mac, the command is also available from the Data tab as Remove Duplicates, though ribbon grouping and dialog appearance can differ slightly by release. Excel for the web may also provide Remove Duplicates on the Data tab; if the command is unavailable in your account or layout, open the workbook in the desktop app to use the same workflow.

Common Mistakes

  • Selecting too few columns. Comparing only a surname, company name, or date can delete records that are different in other important fields. Select all fields that together define a unique record.
  • Selecting too many columns. If you want one row per email address, selecting every field means Excel only removes rows that match across every selected field. Differences in a phone number or note can leave repeated email addresses in place.
  • Leaving the header setting wrong. If a heading is treated as ordinary data, it may be included in the comparison. If the first real record is incorrectly treated as a header, that record may be excluded. Check My data has headers in the dialog.
  • Removing duplicates from one column only. A list usually represents complete rows. If you remove repeated cells without expanding the selection, values in other columns may no longer line up with the correct person or item.
  • Expecting Excel to ignore spaces or spelling differences. Entries such as “Taylor Lee” and “Taylor Lee ” may look alike but contain different text. Misspellings and alternate formats can also prevent a match. Clean inconsistent text before deduplicating.
  • Assuming the operation is just a highlight. Remove Duplicates deletes later matching entries from the selected data; it does not merely mark them. Use a backup or a highlighting method if you need to inspect candidates first.

Tips

  • Use a table for growing lists. Select the data and choose Insert > Table, confirming whether it has headers. Tables make it easier to identify columns and keep related fields together. The Remove Duplicates dialog uses the table’s column names, which can make the comparison clearer.
  • Standardize text before comparing it. Remove accidental spaces, settle on one spelling, and use consistent formats for dates or codes. For a review-only pass, Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values can mark repeated values in a selected column. It highlights candidates but does not delete them.
  • Think carefully about blank cells. If a selected comparison column contains blanks, multiple blank entries can be treated as duplicates. Decide whether blank records should remain, and consider filtering or reviewing them before removal rather than letting blanks define which rows are deleted.
  • Remember that matching is based on cell contents, not appearance. Formatting such as color or font does not make two values different. Conversely, numbers stored as text and numbers stored as numeric values, or dates represented differently, can complicate comparisons. Check the underlying data when results seem unexpected.
  • Keep the first row you want to retain at the top. Excel preserves the first occurrence and removes later matches. If a particular version of a record should survive, sort the data into the desired order before using Remove Duplicates, while keeping whole rows together.
  • Consider filtering or highlighting first for important data. When the duplicate rule is complicated or deletions could have consequences, first identify the matching records and review them. Once the results look right, run Remove Duplicates on a copy or carefully selected range.

Troubleshooting

Excel removed too many rows. Use Ctrl+Z on Windows or Command+Z on Mac right away. Reopen Remove Duplicates and select more columns so the comparison reflects the full record, or restore your saved copy if Undo is no longer available. Verify the header setting as well.

Duplicates are still present. Check whether the entries actually match in every column you selected. A hidden trailing space, punctuation, different date value, or variation in another checked field may make the rows distinct. If you only need unique values from one field, select only that field, but expand the selection if you need entire records to stay intact.

Rows no longer line up. This can happen if you removed duplicates from a single column instead of the full data range. Undo immediately or restore the backup. Then select the complete range and choose Expand the selection if Excel prompts you. Do not try to repair misaligned records by sorting individual columns independently.

The Remove Duplicates command is unavailable or disabled. Confirm that a worksheet range or table is selected and that the sheet is not protected against edits. If the workbook is in a restricted or read-only state, save an editable copy. In Excel for the web, try the Data tab; if the option is missing, use Excel for Microsoft 365 desktop.

The duplicate count seems wrong. Recheck the selected columns, headers, and actual cell contents. A count of removed rows is not necessarily the number of repeated values in just one column when several fields are selected. Excel removes later matching records according to the combination of fields you checked.

Frequently Asked Questions

Does Excel keep the first or last duplicate?
It keeps the first occurrence in the selected range and removes later matches. If you need to retain a particular record, put it first by sorting the complete table into the right order before removing duplicates.

Can I remove duplicates based on one column but keep the whole row?
Yes. Select the full data range, open Data > Remove Duplicates, and check only the column that defines a match. Excel removes the later matching rows while keeping the associated values in the other columns together.

Does Remove Duplicates distinguish uppercase and lowercase?
Excel’s Remove Duplicates comparison is not case-sensitive, so entries that differ only by capitalization are treated as duplicates when the selected fields otherwise match. It does not compare formatting such as font color.

Can I undo removing duplicates?
Usually, yes: press Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. Undo may no longer be a reliable option after later edits or after closing the workbook, so keep a copy when the data is important.

How do I find duplicates without deleting them?
Select the values and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Pick a highlight style and confirm. This marks repeated values for review; it leaves the data in place.

Will Excel remove rows that look the same but have different formatting?
Yes, formatting alone does not distinguish records. Excel compares the contents of the selected cells, not their fills, fonts, or borders. Check the values themselves if apparently identical rows are not being removed.

Related

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.