To remove duplicates in Excel, select any cell in your data, click the Data tab, choose Remove Duplicates, tick the columns that define a duplicate, and click OK — Excel deletes the repeated rows and tells you how many it removed.
Duplicate rows are one of the most common data problems in a spreadsheet, and Excel has a dedicated tool for them. The steps below apply to Microsoft 365, Excel 2021, and Excel for the web. Because the tool deletes rows, it is worth understanding exactly how it decides what a duplicate is before you run it.
Remove duplicates with the built-in tool
- Click any cell inside your data range. Excel will usually detect the whole block, including headers.
- Open the Data tab on the ribbon.
- In the Data Tools group, click Remove Duplicates.
- In the dialog, confirm that “My data has headers” is ticked if your first row contains column names.
- Tick the columns Excel should compare. A row is deleted only when the values in every ticked column match another row.
- Click OK. Excel removes the duplicates and shows a message such as “3 duplicate values found and removed; 47 unique values remain.”
If you want to remove rows that are identical across the whole table, leave all columns ticked. If a single field should be unique — say, an email address — tick only that column so Excel keeps just the first occurrence of each address.
How Excel defines a duplicate
Excel compares the cell values you select, left to right, and keeps the first matching row it finds while deleting the rest. It does not treat formatting, cell color, or font as part of the comparison — only the underlying values. That means two cells showing “Sales” and “Sales ” with a trailing space are different to Excel, and a number typed as text will not match the same number entered normally. Clean those inconsistencies first for accurate results.
Highlight duplicates before deleting
Because Remove Duplicates is destructive, many people prefer to see the duplicates first:
- Select the range you want to check.
- Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Choose a color and click OK. Every repeated value is highlighted so you can review, sort, or filter by color.
You can also flag duplicates with a formula. In a helper column next to your data, enter =COUNTIF(A:A, A2)>1 and copy it down; any row returning TRUE appears more than once in column A.
Comparing the main methods
| Method | What it does | Reversible? |
|---|---|---|
| Data > Remove Duplicates | Deletes duplicate rows in place | Only with immediate undo |
| Conditional Formatting | Highlights duplicates without changing data | Yes — no data is deleted |
| COUNTIF helper column | Flags how many times each value appears | Yes — remove the column when done |
| UNIQUE function | Returns a separate de-duplicated list | Yes — the source data is untouched |
The UNIQUE function: a non-destructive alternative
In Microsoft 365 and Excel 2021, the UNIQUE function returns a de-duplicated list that updates itself. Its syntax is =UNIQUE(array). For example, =UNIQUE(A2:A100) spills a list of the distinct values found in that range into the cells below your formula. Because it is a formula, the result recalculates automatically whenever the source data changes, which makes it ideal for live dashboards. If you later need those results as fixed text rather than a formula, copy the spilled range and use Paste Special > Values. You can even combine it with SORT, as in =SORT(UNIQUE(A2:A100)), to return the distinct values in alphabetical order in one step.
Remove duplicates from a single column only
Sometimes you want unique values in one column while leaving the rest of the row intact. In that case, do not use Remove Duplicates on the whole table, because it will delete entire rows. Instead, either copy the single column to a blank area and de-duplicate that copy, or use a UNIQUE formula pointed at just that column. This keeps your original records untouched while giving you the distinct list you need for a drop-down, a filter, or a summary.
Clean your data first for best results
Trailing spaces are the usual reason duplicates survive. Wrap values in TRIM, such as =TRIM(A2), to strip leading and trailing spaces, and make sure numbers and dates are stored consistently rather than as text. Case is worth noting too: Remove Duplicates and UNIQUE are not case-sensitive, so “Apple” and “apple” are treated as the same value. Standardizing the layout also helps downstream tasks: once your list is clean, building a summary is easier, and formatting touches like merging cells for a header row keep the report tidy.
Sharing your cleaned sheet
When the data is ready, you may want to send a quick visual summary rather than the whole file. Capturing the range as an image is often enough; if you work on a Mac, our walkthrough on how to take a screenshot on a Mac shows the built-in shortcuts, and if you are reviewing the sheet on a phone, our guide to taking a screenshot on iPhone covers the equivalent. Keep the original file saved separately so you always have the pre-cleanup version to fall back on.
When to clean up, and how often
De-duplication is best treated as a routine step whenever you import data from another system, merge two lists, or paste from a web page — the moments when duplicates most often creep in. For a list you maintain by hand over time, a monthly pass is usually enough. Building the habit of highlighting duplicates before deleting, and always working on a saved copy, keeps the process safe even on large data sets where a single wrong click could otherwise remove hundreds of rows.
