Removing duplicate values in Excel takes just one click of the [Remove Duplicates] button on the [Data] tab, but because the deleted data is gone permanently, the order you do things in matters. The flow Microsoft's help recommends is to find duplicates first with Conditional Formatting or the COUNTIF function, check them visually, copy the original data, and then run Remove Duplicates. If you want to pull out only the unique values without touching the original, you can use the Advanced Filter or the UNIQUE function.
When you combine ad reports or customer lists from several files, the same row sometimes ends up in there twice or three times. Have you ever hesitated, thinking, "I just need to wipe out the duplicates in this list, but which ones get deleted and which ones stay?" In this post, based on Microsoft's official help, I'll walk through how to find duplicates, how to remove them, and how to extract only the unique values.
What exactly does Excel mean by a "duplicate"?
The help article explains that a duplicate value is one where all values in a row exactly match all values in another row. So the result changes a lot depending on which columns you choose to compare in the Remove Duplicates window. I'll come back to this later.
Another thing that's easy to get confused about is that the comparison is based on what's displayed in the cell, not the value stored in it. The help article's example is that even for the same date, if one cell is formatted as "3/8/2006" and another as "Mar 8, 2006", they're treated as different unique values. If your data mixes date or number formats, it's safer to make the formats consistent before removing duplicates.
As an analogy, it's like organizing a business card holder. When you have several cards from the same person, putting the extras in a drawer for a while and looking at only one of each is filtering for unique values, and throwing the rest away for good is removing duplicate values. The help article makes the same distinction: filtering for unique values only hides duplicates temporarily, while removing duplicate values deletes them permanently. So before throwing anything away, let's start with how to check which cards overlap.
1. How do I find and highlight duplicates before deleting them?
The quickest way is Conditional Formatting. Here are the steps from the Find and remove duplicates help article.
- Step 1: Select the cells you want to check for duplicates.
- Step 2: In the [Styles] group on the [Home] tab, select [Conditional Formatting] → [Highlight Cells Rules] → [Duplicate Values].
- Step 3: Pick a format (color) and click [OK], and the duplicates are highlighted in color.
If instead you want to color values that appear only once, go to [Conditional Formatting] → [Manage Rules] → [New Rule], choose the rule type "Format only unique or duplicate values," and select "unique" or "duplicate" in the [Format all] list. However, the help article notes that you can't highlight duplicates in the Values area of a PivotTable report, so I recommend applying the formatting to the source data rather than the pivot results.
If you want to see how many times each value repeats as a number, you can also put the COUNTIF function in the next column.
The COUNTIF function counts the number of cells in a range that meet a condition, so for an email list in column A, entering =COUNTIF($A$2:$A$100,A2) in B2 and filling down gives you the number of times each value appears.
Rows with 2 or more are duplicates, and filtering for just those rows lets you see at a glance which data overlapped.
It's also good to remember that COUNTIF isn't case-sensitive for text, and returns incorrect results when comparing strings longer than 255 characters.
2. What's the right order for removing duplicates in Excel?
Once you've checked the duplicates, it's time to delete them. The first thing the help article stresses is to copy the original data to another worksheet, because Remove Duplicates is a deletion you can't take back.
- Step 1: Select a range of cells, or select a single cell inside a table.
- Step 2: In the [Data Tools] group on the [Data] tab, select [Remove Duplicates].
- Step 3: In the [Columns] list, choose the columns to compare. You can also change them all at once with [Select All] or [Unselect All].
- Step 4: Click [OK], and a message tells you how many duplicate values were removed and how many unique values remain.
Step 3 is where you need to be most careful in practice. The help article says data is removed from all columns, even if you don't select all columns. For example, if you check only the "Email" column, rows with the same email are deleted in their entirety, even if the values in other columns differ. The help article explains that the one that stays is the first occurrence in the list, and later identical values are deleted, so if you want to keep the latest record, it's a good idea to sort by date first.
If you delete something by mistake, you can immediately click [Undo] or press Ctrl+Z to revert. The help article also says you can't remove duplicates from data that is outlined or has subtotals, so for that kind of data you need to remove the outline and subtotals first. It also notes that the counts of duplicate and unique values shown in the message after removal may include empty cells and spaces. But what if you want to leave the original as is and make a separate list of unique values?
3. How do I extract only unique values while keeping the original?
The first way is the Advanced Filter. Click [Advanced] in the [Sort & Filter] group on the [Data] tab, choose "Copy to another location" in the Advanced Filter dialog box, specify a cell in the [Copy to] box, and check "Unique records only," and a list of unique values is created next to the original or wherever you specified. If you choose "Filter the list, in-place," the duplicate rows are only hidden temporarily, so you can also use it to preview the result before removing duplicates. The help article also recommends filtering for unique values or applying conditional formatting first to confirm you get the result you want before removing duplicates.
The second way is the UNIQUE function. Its syntax is =UNIQUE(array,[by_col],[exactly_once]), and the arguments break down like this.
| Argument | Meaning | If omitted |
|---|---|---|
| array | The range from which to return unique rows or columns | Required |
| by_col | TRUE compares columns with each other; FALSE compares rows | FALSE (compares rows) |
| exactly_once | TRUE returns only values that occur exactly once | FALSE (returns all unique values) |
For example, if you enter =UNIQUE(A2:A100) in one cell, the results automatically flow down into the cells below, which the help article calls "spilling."
If you turn the source data into an Excel table and reference it with structured references, the result range updates automatically as you add or delete rows, which is handy for reports that are updated weekly.
Setting exactly_once to TRUE leaves only values that appear once, so you can use it to pull out items that appear exactly one time, like "customers who purchased only once."
※ Note: What should I check if duplicates won't go away?
If values look identical but aren't detected as duplicates, there's very likely an invisible difference.
- Format differences: As we saw earlier, the comparison is based on what's displayed in the cell, so different date display formats are treated as different values.
- Leading and trailing spaces: The COUNTIF help article also warns that leading or trailing spaces, or mixing straight and curly quotation marks, can throw off text comparisons. The TRIM function removes all spaces except single spaces between words, but it's designed to remove only the 7-bit ASCII space character (value 32), so the nonbreaking space character (value 160) commonly used on web pages isn't removed by TRIM alone.
- Excel version: The Excel functions list marks UNIQUE as a function introduced in Excel 2021, so it isn't available in Excel 2016 or 2019. On those versions, you can use Remove Duplicates or the Advanced Filter, both of which list Excel 2016 through Microsoft 365 in the help article's "applies to."
If Korean text is garbled in a CSV downloaded from somewhere else, the same name can also be read as different values, so in that case please check the post on fixing broken Korean text in Excel first.
So which method should I choose?
Based on the help articles, here are my criteria.
- When you want to see where the overlaps are first: [Duplicate Values] in Conditional Formatting, or COUNTIF if you also want to see how many times they repeat
- When you need to hand over cleaned-up data: [Remove Duplicates], after copying the original
- When you need just the list and want to keep the original: "Unique records only" in the Advanced Filter, or the UNIQUE function on Microsoft 365 or Excel 2021 and later
To sum up, removing duplicate values in Excel is safest in the order of find, back up, remove, and which columns you check in the Remove Duplicates window determines the result. Data with duplicates cleared out won't inflate your numbers when you join it to other tables with VLOOKUP or XLOOKUP or summarize it with a PivotTable. I'm going to stick to this order when combining report data myself, so for detailed options, please refer to the official help.
FAQ
- Which row stays when I remove duplicate values in Excel?
- According to Microsoft's help, the first occurrence in the list stays, and later identical values are deleted. If you want to keep a specific record (for example, the most recent date), sort the data in the order you want before removing duplicates.
- How do I just find duplicates in Excel without deleting them?
- Select [Home] tab [Conditional Formatting] → [Highlight Cells Rules] → [Duplicate Values], and the duplicate cells are colored while the data stays as is. If you also want to see how many times they appear, put the COUNTIF function in the next column and check rows with 2 or more.
- If I check only one column in Remove Duplicates, is only that column deleted?
- No. The help article explains that data is removed from all columns even if you don't select all columns. If the value in the checked column matches an earlier row, only the first row stays and the other rows are deleted entirely, so I recommend copying the original to another sheet before running it.
- Can I use the UNIQUE function in Excel 2019?
- Microsoft's Excel functions list marks UNIQUE as a function introduced in Excel 2021 and explains that such functions aren't available in earlier versions. In Excel 2016 and 2019, you can create a list of unique values by checking "Unique records only" in the [Advanced] filter on the [Data] tab.
Related posts
Posts that are useful alongside this one when cleaning up data in Excel.
- What is an Excel PivotTable, and when should you use one? - How to summarize data with duplicates removed by multiple criteria
- How to fix broken Korean text in Excel data - Fixing garbled Korean characters when opening a downloaded CSV
- Excel VLOOKUP vs XLOOKUP - Comparing functions that connect a cleaned list to other tables