Yes. In Excel for Microsoft 365 on Windows, select your data and choose Data > Remove Duplicates to delete repeated records; Excel keeps the first match and removes later ones. For recurring cleanup, use Power Query and refresh the query, or use the UNIQUE function to create a live list of distinct values without deleting the source data.
What “automatically” means in Excel
Excel offers several ways to handle duplicates, but they do different jobs. The built-in Remove Duplicates command is a quick, one-time action: you choose which columns define a duplicate, and Excel removes matching rows from the selected range or table. It does not keep watching the data and automatically run again when new records arrive.
For a repeatable process, Power Query can remove duplicates as part of a saved data-cleaning workflow. When the source changes, refreshing the query applies the same steps again. A formula using UNIQUE is another reusable option: its results update when the source range changes, but it produces a separate list rather than deleting records from the original dataset.
It helps to decide what you want before choosing a method. If you want to permanently clean a list once, use Remove Duplicates after making a backup. If you want to preserve the source or handle future imports consistently, use a formula or Power Query.
Remove duplicates with the Data tab
To remove repeated records from a worksheet in Excel for Microsoft 365 on Windows:
- Make a copy of the workbook or the data you are about to change.
- Click a cell in the list. If the data is a proper Excel table, click inside the table; otherwise, select the full range, including every column that belongs to each record.
- Choose Data > Remove Duplicates.
- If Excel asks whether to expand the selection, choose Expand the selection when the columns belong together as complete records. Choosing to continue with the current selection can separate a column from the other information in its rows.
- In the dialog, confirm whether the first row contains headers. Select the columns that should be used to identify duplicates, then select OK.
Excel reports how many duplicate values or rows it removed and how many unique items remain. For example, if a customer list has columns for email, name, and city, selecting only Email treats two records with the same email as duplicates even if their names or cities differ. Selecting all three columns removes a row only when the combination of email, name, and city matches.
Choose the columns that define a duplicate
The column checkboxes are the most important part of the command. Excel considers a row a duplicate when its values match the selected columns. You do not have to select every column, but the selection should reflect what makes a record genuinely unique in your work.
- One identifier: Select an employee ID, invoice number, or email column when that field should occur only once.
- A combination of fields: Select date, product, and location if a repeated record means the same product was recorded for the same location on the same date.
- Every column: Select all columns to remove only fully repeated rows, leaving records that differ in even one selected value.
Consider a sales table with Order ID, Item, and Quantity. If each order can contain several items, using Order ID alone may wrongly remove legitimate lines. If the actual repeated record is an identical combination of Order ID, Item, and Quantity, select those three columns. When the right rule is unclear, test it on a copy and review the rows that would be affected before cleaning the master data.
Make recurring cleanup repeatable with Power Query
Power Query is useful when you regularly import or append data and want the same cleanup steps available for each refresh. On Windows, convert the source range to a table if practical, click in it, and choose Data > From Table/Range. In the Power Query Editor, select the columns that define a duplicate, then choose Home > Remove Rows > Remove Duplicates. Choose Home > Close & Load to return the query result to Excel.
The query stores the transformation steps. When the connected source is updated, use Data > Refresh All to run the query again. This is repeatable, but it is not necessarily an instant background action: the output updates when the query refreshes, and the query must still be able to reach its source. Check the output after changing the source structure, such as renaming a column, because a saved step can depend on the original column names.
In Power Query, removing duplicates from selected columns keeps one row for each distinct combination of those columns. If other columns contain different values among those rows, do not assume which version will be retained unless you have deliberately arranged and verified the workflow. Sort or otherwise define a rule for which record should win, then inspect the result. Power Query is preferable to repeatedly deleting rows by hand when the input is an ongoing import, but it still requires a clear definition of a duplicate.
Show distinct results with the UNIQUE function
In Microsoft 365 and Excel 2021 or later, UNIQUE returns distinct values or rows in a separate output area. It does not alter the source. With names in A2:A20, enter this formula in an empty cell outside the source list:
=UNIQUE(A2:A20)
The result spills into the cells below and updates as values in the referenced range change. For full records, with data in A2:C100 and a blank output area available, enter:
=UNIQUE(A2:C100)
That returns distinct rows based on the combination of values across columns A through C. The source remains intact, which makes this approach safer for reports or review. Make sure the cells where the results need to spill are empty; an existing value in the spill area can produce a #SPILL! error. Also, a fixed range such as A2:C100 will not include new data placed below row 100 unless you adjust the reference. If your data grows, use an appropriately sized range or place the source in an Excel table and build a suitable reference-based formula.
Do not confuse a distinct output with deletion. If another system needs a cleaned, static file, copy the results and use Paste Special > Values in the destination, then check the copied records. Keep the source data if it may be needed for auditing or reconciliation.
Clean inconsistent values before deduplicating
Rows that look alike on screen may not contain identical text. Leading or trailing spaces, nonbreaking spaces copied from a webpage, different punctuation, and inconsistent ID formats can make records behave unexpectedly. Conversely, the command can consider records duplicates based on the selected columns even if other, unselected columns differ. Formatting, such as a cell’s font color, does not establish a separate record.
Before removing anything, check likely problem fields. Sort the column, apply filters, or use a temporary helper column to reveal variations. For example, with names in A2:A20, enter this in B2 and fill down to remove ordinary leading and trailing spaces in a comparison copy:
=TRIM(A2)
TRIM handles ordinary spaces but does not remove every invisible character that can arrive in imported text. If a list came from an external system, compare suspicious cells and clean the data using an appropriate method before treating the result as definitive. Avoid overwriting the only copy of original IDs or names; cleanup can change values that appear meaningful to another system.
Common mistakes and how to avoid them
- Selecting too few columns: A repeated ID may belong to multiple valid detail rows. Include the fields that define a complete duplicate, not just a field that happens to repeat.
- Selecting too many columns: A timestamp or note that varies from row to row can prevent otherwise repeated records from matching. Decide whether that field should matter.
- Removing duplicates from a single column by mistake: If the rest of the row contains related data, use the full dataset and expand the selection so Excel removes complete records rather than leaving mismatched columns.
- Forgetting the header setting: Confirm that “My data has headers” is correct. Otherwise, Excel may treat a heading as a data value or treat a real first record as a heading.
- Expecting the command to run automatically later: The Data tab command is a one-time cleanup. For repeat imports, save a Power Query workflow; for a changing distinct list, use a formula.
Excel’s duplicate detection is based on cell values in the chosen columns, not on whether two records seem semantically equivalent to a person. For instance, an address written as “12 Main St” and “12 Main Street” may remain two separate values. Standardize the relevant fields first if different spellings or formats should count as the same entity.
Which method should you use?
Use the built-in command when the data is already in the workbook and you want to remove duplicates once. Use Power Query when you repeat the same import and cleanup process and want a refreshable result. Use UNIQUE when you need a live distinct list for analysis while keeping the original rows available.
Conditional formatting can highlight repeated values without deleting them. Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to review duplicates visually. This is a useful check before a destructive cleanup, but it does not resolve which row to keep or remove. Excel for the web also offers a Remove Duplicates command on the Data tab; ribbon details can vary by platform and update. Excel for Mac has a Data tab duplicate-removal command as well, though Power Query availability and capabilities can differ by Mac version. If you cannot find a Power Query step on Mac, verify the options available in your installed Excel version rather than assuming they match Windows.
Protect your data and undo mistakes
Removing duplicates changes the selected data, so keep an untouched copy when the records matter. You can duplicate the worksheet, save a separate workbook, or use a query or formula that leaves the source unchanged. Be especially cautious with customer, inventory, payroll, or transaction records, where two similar rows may represent separate real events.
If you remove the wrong rows and have not made other changes, use Ctrl+Z on Windows or Command+Z on Mac to undo. Undo is not a substitute for a backup: later actions, closing the workbook, or other changes may limit what you can recover. Review the removal count and a sample of the remaining data before saving or sharing the cleaned file.
Frequently Asked Questions
Does Remove Duplicates keep the first row?
Yes. Excel keeps one occurrence and removes the later matching rows. If it matters which version survives, arrange the data deliberately before running the command and verify the result.
Can Excel remove duplicates automatically when I add new rows?
The Data > Remove Duplicates command does not automatically rerun when new rows are added. Use a refreshable Power Query workflow for recurring imports, or a UNIQUE formula for a changing distinct-results list.
How do I remove duplicates based on one column but keep the other columns?
Select the whole dataset so each record stays together, then choose only the identifying column in the Remove Duplicates dialog. Excel uses that column to decide which rows match and removes later matching records across the dataset.
Does Remove Duplicates delete entire rows?
When applied to a range of related columns, it removes the duplicate records, not just the repeated cell in the selected key column. Select or expand to the complete dataset to avoid separating related information.
Can I highlight duplicates without deleting them?
Yes. Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This marks repeated values for review but does not remove any records.
Why does Excel leave two records that look identical?
They may differ in an unselected or selected field, contain extra spaces or invisible characters, or use different text or number formats. Inspect the underlying values and standardize the fields that should define a match before running cleanup again.
Related
- How to Create a Hyperlink in Excel: Links to Files and Pages
- How to Hyperlink in Excel on a Mac: Web, Files, and Cells
- Detect Circular References in Excel: Step-by-Step
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 Automatic Page Numbers in Microsoft WordTo add automatic page numbers in Word, open the Insert tab, choose Page Number, select where the numbers…
- Car Loan Amortization Schedule in Google SheetsYes. In Google Sheets, enter the amount financed, annual interest rate, and loan term, calculate the monthly payment…
- Excel Row Stays When Scrolling? Keep It VisibleTo make an Excel row stay visible while you scroll, freeze it: in Excel for Microsoft 365 on…
- Change to Light View in Outlook: Steps for Every VersionTo change Outlook to a light view, open its appearance or theme settings and choose Light, or turn…
- Excel Scatter Plot Labels: Add Names to PointsTo label points in an Excel scatter plot with names, select the chart, choose Chart Design > Add…
- Excel Slicer: Change a Vertical List to HorizontalYes. In Excel for Microsoft 365 on Windows, select the slicer, open the contextual Slicer tab, and increase…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.