Yes. In Excel for Microsoft 365 on Windows, select your data, choose Data > Remove Duplicates, select the columns that define a duplicate, and click OK. Excel keeps the first matching row in the current order and removes later matches.
What Excel Means by a Duplicate
Before removing anything, decide what counts as the same record. If a customer list has a repeated email address but different names or dates, you might regard the email as the unique key. If you select every column, Excel only treats rows as duplicates when the values match across all selected columns. Those two choices can produce very different results.
For example, imagine a list with columns for Order ID, Customer, and Status. Two rows with the same Order ID but different statuses are not identical across all three columns. Selecting only Order ID tells Excel to keep one row for each ID, while selecting all three columns keeps both rows unless every selected value matches. When a duplicate key is found, Excel removes the later row as a whole; it does not merely clear the repeated cell.
Excel keeps the first occurrence it encounters from top to bottom. It does not decide which row is newest, most complete, or most accurate. The row order is therefore part of the decision: put the record you want to retain first before using the command.
Remove Duplicates with the Data Tab
For a straightforward cleanup, use Excel’s built-in command. It changes the source data rather than producing a separate cleaned list, so first confirm that you have a backup or can undo the operation.
- Click a cell inside the data range or table. If your data is not a table, make sure the full range is contiguous and has a header row if you plan to identify columns by name.
- Open Data > Remove Duplicates.
- In the dialog box, select My data has headers if the first row contains column names. Check or clear the boxes to choose the columns that define a duplicate.
- Click OK. Excel reports how many duplicate values were removed and how many unique values remain.
- Review the remaining rows, especially if the chosen key may identify records that differ in other important fields.
For a table of customer records, selecting Email alone means that repeated email addresses count as duplicates, even if other fields differ. Selecting Email and Country means that only rows with the same combination of email and country count as duplicates. The selected columns are the comparison criteria; Excel still removes the entire later row.
Choose the Columns That Define a Match
Choosing comparison columns is the most important part of the operation. If you select too few, Excel may discard legitimate records. If you select too many, duplicates that differ in an irrelevant field may remain.
- Use one column when a single field should be unique, such as an employee ID or invoice number.
- Use several columns when a record is identified by a combination, such as Store plus Product Code or First Name plus Last Name plus Date of Birth.
- Use all columns when you only want to remove fully repeated rows and retain records that differ in any selected field.
Think through what should happen when two rows share a key but contain different details. If an account ID repeats with two different phone numbers, removing a row based only on account ID will discard one phone number along with the rest of that row. If that is not acceptable, investigate the conflict first rather than treating the duplicate as a simple extra copy.
When the desired survivor is not currently first, sort the entire dataset—not just the key column—so the preferred record comes first. For example, to keep the latest row for each customer, sort the full range by customer and then by date from newest to oldest. Then remove duplicates using the customer column. Check the result carefully; sorting only one column can misalign records and corrupt the data.
Check Which Rows Would Be Removed
If you want to inspect duplicate groups before changing the source, add a helper column. For example, with product IDs in A2:A8, enter the following in B2 and fill it down through B8:
=COUNTIF($A$2:A2,A2)
For the example IDs 104, 107, 104, 109, 107, 110, and 110, the formula returns 1, 1, 2, 1, 2, 1, and 2. A result of 1 marks the first occurrence; a result above 1 marks a later occurrence. You can filter the helper column to values above 1, review those rows, and then decide whether to delete them. This check is useful when a duplicate key may have conflicting details.
The formula compares values in the range from the first data row through the current row, so its result depends on row order. If you sort the records afterward, the first occurrence may change. For duplicate combinations, use a helper column that joins the relevant fields, or use a formula tailored to compare the columns separately; take care with a joined key if field values can contain the same separator you use between them.
For a visual check of one column, select the values and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. That formatting highlights repeated values, including the first occurrence, so it does not by itself identify which copy Excel will keep. Treat it as a review aid, not as a deletion instruction.
Use UNIQUE to Make a Separate Clean List
If you want to preserve the original data, a dynamic array formula can return a separate list of distinct values. With product IDs in A2:A100, enter this formula in an empty cell:
=UNIQUE(A2:A100)
In Microsoft 365, the results spill into cells below the formula and update when the source range changes. This is a good option when you need a distinct list for reporting or want to compare the cleaned result before replacing the original. The formula returns distinct values in their first-seen order by default; it does not delete or edit source rows.
There is an important distinction between keeping one representative of each repeated value and returning values that occur exactly once. With product IDs in A2:A100, =UNIQUE(A2:A100,,TRUE) returns only IDs that appear exactly once. It excludes every ID that occurs more than once, rather than keeping one copy from each duplicate group. Use the regular UNIQUE formula when your goal is one instance per value.
UNIQUE is available in Microsoft 365 and Excel 2021 or later. In older Excel versions, use Remove Duplicates on a copy, Advanced Filter, or a helper-column workflow instead. A formula-based result is often preferable when you need a repeatable view; the built-in command is more direct when you intend to clean the source itself.
Common Problems and Data Quality Traps
Rows that look alike may not compare as expected because the underlying entries are not truly consistent. Extra spaces, nonprinting characters, inconsistent spelling, and mixed data types can make a list harder to deduplicate reliably. For example, a name with a trailing space may look identical on screen but behave differently in comparisons. Standardize the data first when the results seem surprising.
Case is generally not a useful way to distinguish duplicates in Excel’s duplicate-removal tools: entries such as APPLE and Apple are treated as the same for this purpose. If capitalization carries meaning in your data, do not assume Remove Duplicates will preserve both. Also remember that blank cells may be treated as repeated values in the selected columns, so selecting a key column with many blanks can remove rows you did not intend to group together.
Other frequent causes of accidental loss include these:
- Selecting only a name when different people can share that name.
- Choosing every column when you intended to deduplicate by a single ID.
- Including a timestamp or status column that differs between otherwise repeated records.
- Sorting one column without expanding the selection to the full dataset.
- Running the command on a partial selection instead of the complete table or range.
If duplicates represent possible errors rather than exact copies, review the records instead of automatically deleting them. A repeated invoice number, for instance, could signal a duplicate entry or a legitimate adjustment. Excel can identify matching keys, but it cannot determine the business meaning of the match for you.
Undo, Back Up, and Platform Notes
Remove Duplicates deletes rows from the active data range. You can usually reverse the most recent operation immediately with Ctrl+Z on Windows or Command+Z on Mac, but undo history is not a substitute for a backup, particularly if you continue editing after the removal. Save a copy of the workbook or duplicate the worksheet before a high-stakes cleanup.
In Excel for Microsoft 365 on Windows, the standard path is Data > Remove Duplicates. Excel for Mac also provides Remove Duplicates on the Data tab; the dialog is similar, though ribbon layout and keyboard shortcuts can differ. Excel for the web may present a more limited interface depending on the available features in your account. If the command is missing, open the workbook in the desktop app rather than assuming the data has been cleaned.
For a table, click within the table before opening the command so Excel can identify the table’s columns and headers. For a plain range, verify the selection and header setting in the dialog. Afterward, inspect the status message and the surviving rows. Save only after you are satisfied with the result.
Frequently Asked Questions
Does Excel keep the first duplicate or the last one?
Remove Duplicates keeps the first matching row in the current top-to-bottom order and removes later matches. To keep a different record, reorder the complete dataset first so the preferred record appears first.
Can I remove duplicates based on one column but keep the rest of each row?
Yes. In the Remove Duplicates dialog, select only the column that defines the match. Excel then removes later matching rows in full, including their values in every other column, and keeps the first row’s complete record.
How do I remove only exact duplicate rows?
Choose all columns in the Remove Duplicates dialog. Excel will remove a later row only when its values match the selected columns. If you leave out a column, differences in that column will not prevent a match.
Does UNIQUE remove duplicates from my original data?
No. UNIQUE creates a separate formula result and leaves the source cells unchanged. Use Data > Remove Duplicates when you want to alter the original range, or copy the formula results and paste values if you need a static list.
Can I keep the newest record for each ID?
Yes. Sort the entire dataset by ID and then by date with the newest record first. Run Remove Duplicates using the ID column alone; Excel keeps the first row for each ID. Verify the sort and the remaining records before saving.
Related
- Excel Print Area Locked? Causes, Fixes, and Workarounds Explained
- Excel Print Area Lines: What They Mean and How to Hide Them
- Excel Print Area Button: Where to Find It and How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Oracle SQL Text to Columns: Split Delimited DataTo split delimited text into separate columns in Oracle SQL, use INSTR to find delimiter positions and SUBSTR…
- Correlation Table in Excel: Create and Read OneIn Excel for Microsoft 365 on Windows, create a correlation table by choosing Data > Data Analysis >…
- Clear Cache and Cookies in Outlook: Step-by-StepTo clear Outlook cookies, remove the site data for Outlook in the browser you use. To refresh cached…
- How to Hide Long Text in Excel Cells Without Deleting ItTo keep long text from showing across neighboring cells in Excel, make the cells to its right non-empty,…
- How to Unlock Microsoft Excel: Sheets, Files, and EditingTo unlock Excel, first identify what is blocking you: a protected sheet or workbook needs its password, a…
- XLOOKUP in Google Sheets: How It Works and When to Use ItYes. XLOOKUP is a built-in Google Sheets function that searches a row or column for a value and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.