To remove duplicate values from a column in Excel for Microsoft 365 on Windows, select the column or the full related table, then choose Data > Remove Duplicates. In the dialog, select the column Excel should compare, confirm whether the first row is a header, and choose OK; Excel keeps the first occurrence and removes later matches.
Remove duplicates from a single column
For a standalone list, the built-in command is the quickest way to leave one copy of each value in the original cells. For example, if A1 is a heading and A2:A8 contains North, South, North, East, South, West, and East, removing duplicates leaves North, South, East, and West. The command changes the list rather than making a separate result, so copy the data first if you might need the original order or full list later.
- Select a cell in the list, or select the cells you want to clean. Include the heading if you have one.
- Open Data > Remove Duplicates.
- In the dialog, check My data has headers if the first row contains a label such as Region. Otherwise, leave it unchecked.
- Choose the column or columns that define a duplicate. For a one-column list, leave that column checked.
- Choose OK. Excel reports how many duplicate values it removed and how many unique values remain.
Excel retains the first matching entry in the current order. If you want a particular copy to remain—for example, the newest record—sort the data into the desired order before removing duplicates. Remember that sorting changes the order of the records, so consider copying the list first.
Keep related row data together
A column often belongs to a larger record. A customer name may be next to an email address, order number, or date. In that case, do not remove duplicate cells from only the name column and leave the neighboring columns untouched. Doing so can shift the remaining names upward while the other values stay where they were, pairing records incorrectly.
Instead, select the entire data range or click inside the table, then open Data > Remove Duplicates. In the dialog, check only the column that defines a duplicate—for example, Email—and uncheck columns that should not affect the comparison. Excel removes the entire row for each later match in the selected range, keeping the first matching row and its related details together.
If Excel asks whether to expand the selection after you select one column, choose Expand the selection when the neighboring columns belong to the same records. Choosing to continue with the current selection can clean just that column and detach its values from their row data. Before confirming the dialog, check both the selection and the column checkboxes; they answer different questions. The selection determines which cells or rows can be affected, while the checkboxes determine what Excel compares.
What Excel counts as a duplicate
The checked columns define the comparison. With only Email checked, two rows with the same email count as duplicates even if their names or order dates differ. With both Email and Order ID checked, Excel removes a row only when the values in both checked fields match another row. This is useful when one field alone is not a unique identifier.
Excel keeps the first matching record in the selected range and removes later matches. “First” means first in the current row order, not necessarily earliest by date or most complete. If the rows contain conflicting details, decide which one should survive before running the command. Sorting by a reliable rule, such as newest date first, can make the retained row predictable.
Visually similar entries are not always identical. A value with a trailing space, an extra space between words, or an invisible character may remain separate from the clean-looking version. Conversely, formatting can make different underlying values look alike; inspect the cell contents if the result is surprising. Excel’s duplicate removal is not a general-purpose spelling or data-cleaning tool. It does not decide that “NY” and “New York” mean the same thing. Text that differs only by letter case is generally treated as a duplicate by this command, so do not rely on capitalization to preserve separate entries.
Prepare a safe copy before cleaning
Remove Duplicates edits the selected data immediately. Ctrl+Z can undo the change while the action remains in the undo history, but closing the workbook or performing other operations may make recovery less convenient. Save a copy of the workbook or duplicate the worksheet first when the data matters, and verify the result before saving over the original.
- Check that the selected range includes every related column and excludes unrelated data.
- Confirm whether the first row is a header; otherwise Excel might treat a heading as a value or skip the first actual entry.
- Decide which columns identify a duplicate and which record should be retained.
- Look for filters or hidden rows. If you need to clean a complete dataset, make sure you understand whether the range and view include all the records you intend to process.
- Afterward, compare the removal count with what you expected and inspect a few retained rows.
On Windows, use Ctrl+Z to undo the most recent action. On Mac, the equivalent is Command+Z. Menu and ribbon labels can vary slightly by platform or update, but Excel for Mac also provides a Remove Duplicates command on the Data tab.
Use a formula to identify repeats first
If you want to review duplicates without deleting anything, add a helper column. With values in A2:A100, enter this in B2 and fill it down:
=COUNTIF($A$2:A2,A2)>1
The formula returns TRUE for the second and later occurrence of a value and FALSE for its first occurrence. For example, if A2 is “North,” A3 is “South,” and A4 is “North,” the result in B4 is TRUE because North has appeared twice in the range from A2 through A4. You can filter the helper column to review the TRUE rows, or sort and inspect them before deciding what to remove. This is a review aid, not a deletion command.
To mark every occurrence of a value that appears more than once, rather than marking only later occurrences, use a fixed range. With values in A2:A100, enter this in B2 and fill it down:
=COUNTIF($A$2:$A$100,A2)>1
For example, if North appears in A2 and A4, both corresponding helper results are TRUE. This lets you identify the whole group of repeated values before making a decision. If the range contains blanks, a COUNTIF-based check can also flag repeated blank cells; adjust the process or use a filter if blanks should be ignored.
Alternatives that preserve the source list
For a separate, automatically updating list of unique values, Excel for Microsoft 365 and Excel 2021 or later support the UNIQUE function. With the original list in A2:A100, enter this formula in an empty cell:
=UNIQUE(A2:A100)
Excel spills the unique results into the cells below the formula. The source remains unchanged, and the output updates when values in the referenced range change. Make sure the cells beneath the formula are empty so the results have room to spill. UNIQUE creates a result list; it does not delete repeated entries from the source. It is a good option for reports or dropdown source lists where you want the original records preserved.
Another option is Data > Advanced (Advanced Filter), which can copy unique records to another location rather than editing the source. It is useful when you need a static copy and do not want a dynamic formula. The dialog has options for filtering the list in place or copying it elsewhere and for selecting unique records only. Choose the range carefully, especially if the records span several columns.
Power Query is appropriate for repeatable imports or larger cleanup workflows. You can load a table or range into Power Query, choose the field used to identify duplicates, and remove duplicates in the query before loading the result. The query output is separate from the original source data; refresh the query to apply the steps again to updated source data. The exact ribbon placement can vary with Excel updates, but the query editor includes a Remove Duplicates command for selected columns.
Common problems and how to fix them
- Excel removed a row I wanted to keep: The command retained the first matching row in the current order. Undo if possible, then sort so the preferred record comes first and repeat the operation on a copy.
- Values that look the same remain: Check for spaces, nonprinting characters, or different underlying values. You can clean text in a separate helper column before deduplicating, but review the cleaned results because trimming or replacing characters can change legitimate data.
- Other columns no longer match their original records: The command was probably applied to a single column instead of the full table. Undo and repeat with the full record range selected, then choose only the comparison column in the dialog.
- The heading was removed or counted as a value: Reopen the operation with the correct My data has headers setting. If the heading is missing now, undo and run it again with the right setting.
- The duplicate count is unexpected: Recheck the selected range and checked columns. A duplicate is defined by the combination of all checked fields, not necessarily by the one column you had in mind.
Do not confuse removing duplicates with highlighting them. Home > Conditional Formatting can highlight repeated values while leaving every row in place. Highlighting is safer for an initial audit, but it does not remove anything. Also, removing duplicate records is different from sorting: sorting rearranges rows, while Remove Duplicates deletes later matches according to the selected comparison fields.
Frequently Asked Questions
Does Excel keep the first or last duplicate?
Remove Duplicates keeps the first matching entry in the current row order and deletes later matches. Sort first if you want a particular row, such as the newest one, to be kept.
Can I remove duplicates based on one column but keep the other columns?
Yes. Select the full table or related range, open Data > Remove Duplicates, and leave only the identifying column checked. Excel removes the later matching rows as complete rows, preserving the other fields in the retained record.
Does Remove Duplicates delete the original data permanently?
It changes the selected range, but you can usually undo immediately with Ctrl+Z on Windows or Command+Z on Mac. For a dependable backup, copy the worksheet or save a separate workbook before running the command.
Can Excel remove duplicates but leave the original list untouched?
Yes. Use =UNIQUE(A2:A100) in Microsoft 365 or Excel 2021 and later to spill a distinct list elsewhere, or use Advanced Filter to copy unique records to another location. Both approaches leave the source range in place.
Why are repeated-looking values not being removed?
They may contain extra spaces or invisible characters, or may have different underlying values despite similar display formatting. Check and clean the data, then rerun the command. Also confirm that the intended comparison column is checked.
Related
- Excel Protect Sheet but Allow Copying: What to Know
- Excel Protect Sheet: Can Users Expand Groups?
- Excel Protect Sheet: Can You Insert Table Rows?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add a Shared Mailbox in Microsoft OutlookTo add a shared mailbox in Outlook, first ask your Microsoft 365 or Exchange administrator to grant your…
- Excel Group Shortcut Key: Rows, Columns, and OutlinesIn Excel for Microsoft 365 on Windows, select the rows or columns you want to group and press…
- Excel Power Query: Merge Rows from Columns or TablesTo merge row data in Excel Power Query, you either combine values from multiple columns into one column…
- Excel Heat Map Colours: How to Apply and Choose ThemIn Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting >…
- Excel Workbook Extension: XLSX, XLSM, and MoreThe standard extension for a modern Excel workbook is .xlsx. Use .xlsm when the workbook needs to keep…
- Excel Value Formatting: What It Changes and How to Use ItIn Excel, value formatting changes how a stored number, date, or text value appears in a cell; it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.