Yes. In Excel for Microsoft 365 on Windows, select the entire table, choose Data > Remove Duplicates, and in the dialog leave checked only the column you want Excel to compare. Excel keeps one row for each distinct value in that column and removes the other matching rows from the selected range, so make a copy first if you may need the original data.
What removing duplicates based on one column means
A row can contain several fields that together make up a record: an order number, customer name, date, and amount, for example. If you remove duplicates based on just the customer name, Excel uses that name to decide which rows count as repeats. It does not require the order number, date, or amount to match.
For instance, suppose a list has three rows for Jordan Lee, with different order numbers and purchase dates. Checking only the Customer column marks those rows as duplicates, and Excel retains one Jordan Lee row while removing the other matching rows in the selected data. This is different from checking every column, which removes a row only when the values across all selected columns match another row.
This distinction matters: a repeated name is not necessarily a repeated person, and a repeated product may represent separate sales. Decide whether the selected field really identifies records you are comfortable reducing to one row. If the answer depends on a second field, use both relevant columns as the duplicate criteria instead.
Remove duplicates using one column: steps
- Save a working copy. Removing duplicates changes the data in place. A duplicate copy or worksheet gives you a way to compare or restore the original later.
- Select the full data range or table. Include all columns that belong to each record, not just the column you are using to identify duplicates. If you use an Excel table, click a cell inside it.
- Open the command. On the ribbon, choose Data > Remove Duplicates.
- Confirm the header setting. If the first row contains headings such as Customer or Order ID, make sure My data has headers is selected. This prevents Excel from treating the headings as ordinary values.
- Choose the comparison column. In the list of columns, clear Select All, then check only the field that defines a duplicate. For example, select Email if each email address should appear once.
- Run the removal and review the result. Excel reports how many duplicate values it removed and how many unique values remain. Check several retained rows and confirm that the other fields are still aligned with their records.
If Excel asks whether to expand the selection after you selected only one column beside other data, choose Expand the selection. Choosing to continue with the current selection can remove cells from that one column without removing their neighboring cells, which can scramble row relationships. Selecting the full table before opening the command helps avoid that prompt.
Which matching row does Excel keep?
Excel keeps the first occurrence it encounters in the selected range and removes later rows that match the chosen comparison column. Therefore, row order determines which version of a repeated record survives. If several rows share an email address but have different dates or status values, the first one in the current order is kept; Excel does not automatically choose the newest, most complete, or highest-value record.
To control which record stays, sort the full data range before removing duplicates. For example, sort by Last Updated from newest to oldest, then remove duplicates based on Email. The newest row will be first for each email and is the one Excel retains. Sort the entire table, not just the date column, so every record remains intact. If you need a more precise rule—such as keeping the row with the highest score—review the groups first or use a formula or Power Query approach that explicitly supports that selection logic.
Prepare the data and avoid common problems
Small inconsistencies can make a list appear to have duplicates when Excel does not treat the entries as the same, or make distinct records appear interchangeable. Before removing rows, check how the key column is stored and whether blanks are meaningful. Helpful checks include:
- Extra spaces: A name with a trailing space can behave differently from the same name without one. Clean imported text before deduplication if spaces are accidental.
- Capitalization: Excel’s Remove Duplicates command treats text matches as case-insensitive, so values such as alex@example.com and Alex@example.com are considered duplicates for this command.
- Dates and numbers: Inconsistent formatting or a mixture of text and numeric entries can make data harder to compare. Standardize the column and inspect suspicious values before deleting records.
- Blank keys: If multiple rows have a blank value in the selected column, Excel may treat those as repeats and remove rows. Filter or review blank-key rows separately if they should be preserved.
- Formulas: A cell containing a formula displays a result. Check whether repeated results represent truly duplicate records before removing rows.
Also check for filters and hidden rows before you make changes. The command operates on the selected data range, and hidden or filtered records can make it less obvious which rows are present in the data. If you are unsure what will be removed, make a copy and test the procedure there first. You can undo an immediately completed removal with Ctrl+Z on Windows (Command+Z on Mac), but do not rely on undo after closing the workbook or making later changes.
Remove Duplicates compared with other methods
Remove Duplicates is usually the quickest option for a one-time cleanup because it changes the selected data directly. It is also easy to target one field: select the full table, then check only the column that defines a repeat. Its main limitation is that it deletes rows, so the choice of retained row and the scope of the selected range matter.
Conditional formatting can highlight repeated values without deleting anything. Select the key column, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This is useful for reviewing a list before deciding what to keep. Highlighting identifies repeated values in the selected cells; it does not decide which full records should be removed.
UNIQUE creates a separate list of distinct values instead of changing the source. With email addresses in A2:A100, enter =UNIQUE(A2:A100) in an empty cell to return one copy of each distinct entry. The formula returns a unique list of that column only; it does not automatically return the complete record associated with each value. UNIQUE is available in Microsoft 365 and Excel 2021 or later, and its results spill into cells below the formula.
Use a formula to flag repeated values
A helper column is useful when you want to inspect duplicates before deleting anything, or filter the rows that repeat. With IDs in A2:A8, enter =COUNTIF($A$2:A2,A2)>1 in B2 and fill it down through B8. The first occurrence of each ID returns FALSE; later occurrences return TRUE. For example, if A2:A8 contains 101, 104, 101, 105, 104, 106, 101, the formula flags rows 4, 6, and 8 as later repeats.
COUNTIF is not case-sensitive, which suits many ID and email checks but may not suit a case-sensitive code system. The expanding range in the formula is intentional: its starting cell remains A2, while the ending cell advances on each row. After filling down, filter the helper column to TRUE to review repeated records. If you choose to delete those rows, select the entire record rows, not just the key cells.
Use Power Query for repeatable cleanup
For a file that you import and clean regularly, Power Query can save the cleanup steps as a query. In Excel, select the data and choose Data > From Table/Range to open the Power Query Editor. Select the one column that defines a duplicate, then choose Home > Remove Rows > Remove Duplicates. Because only that field is selected for the operation, rows are compared by that field rather than by every column.
When the query is ready, use Home > Close & Load to return the result to Excel. Later, you can refresh the query to apply its steps to updated source data. Power Query is a good fit when the same import-and-clean process repeats; for a small one-off list, the regular Remove Duplicates command is often simpler. If which record should survive is important, explicitly design and check the query’s sorting and selection logic rather than assuming that a particular duplicate row will always be retained.
Frequently Asked Questions
Can I remove duplicates based on one column but keep all the other columns?
Yes. Select the full table, then check only the comparison column in the Remove Duplicates dialog. Excel removes matching rows from the selected range, preserving the other columns for the row it keeps.
Does Excel keep the first or last duplicate?
It keeps the first occurrence in the current order of the selected range and removes later matches. Sort the full table first if you want a particular record, such as the most recent one, to come first.
Why did Excel delete rows that had different information?
If only one column was checked, differences in the other columns do not affect the duplicate test. Excel removes later rows with a matching value in that checked field, even when their other details differ.
Can I remove duplicates without deleting data?
Use Conditional Formatting to highlight repeated values, or use UNIQUE to create a separate list of distinct values. Both let you review or display results without removing source rows.
Can I undo Remove Duplicates?
Immediately after running it, press Ctrl+Z on Windows or Command+Z on Mac. Because this is a destructive edit, keeping a backup is safer, especially if you will continue working in the workbook.
Related
- Excel Power Query Value Replacement: How to Use It
- Excel Power Query Value Filtering Explained
- Excel Power Query: Split a Cell Into Multiple Rows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook Mobile Rules: How to Create Them on Your PhoneYou can’t create a traditional inbox rule inside the Outlook mobile app. To make one from your phone,…
- Timesheet Template in Word: Create and Use OneA timesheet template in Word is a document with a table for recording work dates, start and end…
- How to Alphabetize in Google Sheets Without Mixing RowsTo alphabetize data in Google Sheets, select the full range you want to sort, then choose Data >…
- Excel Reverse Undo Shortcut: What Works and What Doesn’tIn Excel for Microsoft 365 on Windows, the shortcut that reverses an undo is Ctrl+Y; Excel calls this…
- Protect an Excel Sheet Without Locking Cells: How It WorksYes. In Excel for Microsoft 365 on Windows, unlock the cells people should be able to edit, then…
- How to Encrypt a Microsoft Excel File with a PasswordTo encrypt an Excel file in Microsoft 365 for Windows, open the workbook and choose File > Info…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.