UNIQUE vs Remove Duplicates in Excel

UNIQUE vs Remove Duplicates is really a question of whether you want to delete data or just list it. Remove Duplicates permanently removes repeated rows from your table, while the UNIQUE function builds a separate, live list of each value once and leaves the original data alone. This guide is for anyone cleaning exports, building summaries or facing a "how many distinct customers?" question in an Excel test.

UNIQUE vs Remove Duplicates at a glance#

Both get rid of repeats. The difference is what happens to your original data.

The same task, solved both ways#

Here is an order list. The task: get a list of customers, each one once.

With Remove Duplicates#

1. Select A1:C9 (or click inside the table).
2. Choose Data > Remove Duplicates.
3. Untick every column except Customer, keep My data has headers ticked and click OK.

Excel reports that 3 duplicate values were found and removed. Rows for orders 1003, 1005 and 1007 are gone. You have five customers left, but you have also lost three orders. If you needed those orders later, for a total or a count, they are no longer in the table.

The safe way is to copy just the Customer column somewhere else first and run Remove Duplicates on the copy.

With the UNIQUE function#

In an empty cell such as E2, type:

=UNIQUE(B2:B9)

The result spills down into E2:E6: Acme Corp, Brightline, Delta Freight, Evergreen Co, Fulton & Sons. All eight orders are still in the table. Add a new customer in row 10, extend the range to B2:B10, and the list grows by itself.

To count them:

=COUNTA(UNIQUE(B2:B9))

That returns 5. COUNTA counts non-empty cells; COUNT would return 0 here, because it only counts numbers.

When to use Remove Duplicates#

Use Remove Duplicates when the repeats are mistakes you want gone from the data. It is the better choice when:

• You're cleaning an export once, such as a mailing list where the same person appears twice.
• Whole rows are exact copies, so deleting them loses nothing. Tick every column to remove only rows that match in every column.
• The file must work in Excel 2019 or older.
• You need a static list to paste into an email or another system.

Before you click OK, check the ticked columns. Ticking only one column removes rows that share that value even if the other columns differ, which is exactly how the orders above were lost. To see the repeats before deleting anything, use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

When to use the UNIQUE function#

Use UNIQUE when you need a list of distinct values for a report or a formula, and the data must stay complete. It is the better choice when:

• You're building a summary. UNIQUE makes the labels, then SUMIFS or COUNTIFS fill in the numbers. New categories appear automatically.
• You need a count of distinct values, such as "how many customers do we have?"
• You want values that appear only once, such as one-time customers: =UNIQUE(B2:B9,FALSE,TRUE) returns Delta Freight, Evergreen Co and Fulton & Sons.
• You want the list sorted: =SORT(UNIQUE(B2:B9)).
• You want unique combinations of several columns: =UNIQUE(B2:C9) returns each customer and region pair once.

The Excel UNIQUE function guide covers each argument in more detail. If your column has blank cells, UNIQUE returns a 0 for the blank. Remove it by filtering first: =UNIQUE(FILTER(B2:B20,B2:B20<>"")). The FILTER function guide explains how that test works.

Common mistakes with UNIQUE and Remove Duplicates#

These are the ones that cost people data or points:

1. Running Remove Duplicates on the whole table with one column ticked. You keep one row per value and delete the rest, along with their amounts, dates and IDs.
2. Not making a copy first. Ctrl+Z (⌘Z on a Mac) only helps until you close the file.
3. Blocking UNIQUE's spill. If a cell below the formula isn't empty, UNIQUE shows #SPILL!. Clear the cells in the way. Our guide to fixing the #SPILL! error covers every cause.
4. Hidden differences. "Acme Corp" and "Acme Corp " (with a trailing space) are two different values to both tools. Clean the text with TRIM first.
5. Using COUNT instead of COUNTA. =COUNT(UNIQUE(B2:B9)) gives 0 for names. Use COUNTA for text.
6. Sharing a UNIQUE file with Excel 2019 users. They see #NAME?. Paste the result as values if the file must open in older Excel.

How to count distinct values without UNIQUE#

In Excel 2019 or older, you can still count distinct values with one formula that works in every version:

=SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))

COUNTIF counts how often each customer appears: Acme Corp gets 3 on each of its three rows, so each of those rows adds 1/3. Every customer's rows add up to exactly 1, and the total is 5. One warning: an empty cell in the range causes a #DIV/0! error, so the range must have no blanks. On Microsoft 365, =COUNTA(UNIQUE(B2:B9)) is easier to read and to check.

Which keeps the right row?#

Remove Duplicates always keeps the first row of each group, top to bottom. If you want to keep the latest order for each customer instead, sort the data newest first before removing duplicates.

UNIQUE doesn't keep rows at all: it lists values. To pull a detail for each distinct value, such as each customer's first order number, combine UNIQUE with a lookup. Our comparison of MATCH vs XMATCH explains how to find positions, including searching from the bottom up to get the last match.

Distinct lists often feed other calculations. When you then summarize amounts per customer, look at AVERAGE vs MEDIAN before you report a "typical" order size, and TODAY vs NOW if you need to count only recent orders.

Our recommendation#

Default to UNIQUE whenever you have Excel 2021, Excel 2024 or Microsoft 365. It answers the same questions without deleting anything, and it stays correct when the data changes.

• Counting or listing distinct values for a report: UNIQUE, with COUNTA or SORT around it.
• Deleting true duplicate rows from a messy export: Remove Duplicates, on a copy, with every column ticked.
• Older Excel: Remove Duplicates on a copied column, or a pivot table.

In a job test, read the wording. "List the customers" or "how many distinct customers" points to UNIQUE. "Remove the duplicate rows" means the Remove Duplicates command.

Practise it#

Count the distinct customers in the exercise on this page. Then work through the UNIQUE function lesson, where you list regions and customers, count them with COUNTA and find the one-time customers with exactly_once.