Two people open the same customer export. The first selects the column, clicks Data → Remove Duplicates, and watches a third of the rows vanish. The second types =UNIQUE(A2:A500) in an empty cell and gets a clean list next to the original data, untouched. Both end up with deduplicated values. Only one of them is destructive, and only one keeps updating when new rows arrive. That difference decides which tool you should reach for in any given workbook.
One is a command, the other is a formula
Remove Duplicates is a UI command that edits the sheet in place. You point at a range, confirm which columns count, and Excel deletes every row it considers a repeat. The action is committed the moment you click OK; once the file is saved and closed, the deleted rows are gone.
UNIQUE is a dynamic array function. It reads a source range and spills a distinct list into a new range, leaving the source untouched. Change a value in the source, add a row, delete a row — the spilled list updates the next time Excel recalculates. Nothing about the original data moves.
| Behavior | Remove Duplicates | UNIQUE |
|---|---|---|
| Touches the source | Yes — deletes rows | No — spills elsewhere |
| Updates with new data | No, one-shot | Yes, on recalc |
| Available in | Excel 2007+ | Excel 2021, 365, Web |
| Undoable later | Until file closes | Anytime — source intact |
That table is the whole argument in miniature. Every other difference between the two tools is a consequence of the destructive-vs-dynamic split.
UNIQUE in 90 seconds
The function takes one required argument and two optional ones that most people never use until they need them.
=UNIQUE(array, [by_col], [exactly_once])array is the range you want to deduplicate. by_col is FALSE by default, which compares rows; set it to TRUE only when your data sits across columns instead of down rows. exactly_once is FALSE by default, returning every distinct value; set it to TRUE to return only values that appear once and only once in the source — useful for finding the row that nobody else duplicated.
Here is what a typical setup looks like. Column A is the source list of cities; cell C2 holds the formula and the rest of column C is the spilled result.
| A | B | C | |
|---|---|---|---|
| 1 | City | Distinct | |
| 2 | Berlin | =UNIQUE(A2:A100) | |
| 3 | Oslo | Berlin | |
| 4 | Berlin | Oslo | |
| 5 | Lisbon | Lisbon |
Nest it inside SORT — =SORT(UNIQUE(A2:A100)) — and you get an alphabetized distinct list that resorts itself as the source changes. That single-cell formula replaces a manual Remove Duplicates plus a manual sort, every time the export refreshes. If you keep returning to cleanup steps you already automated once before, this is the same principle, scaled down to one formula.
Where Remove Duplicates quietly destroys data
The classic Remove Duplicates failure is not a crash. It is a successful click that deleted the wrong rows. The dialog shows a checkbox per column, asking which fields define a duplicate. Uncheck the email column on a customer list and Excel will treat two different people with the same name as duplicates — and keep only one of them.
This is why a small audit habit matters. Before you run the command, copy the sheet to a backup tab. After the command, sort by your key column and spot-check that the rows that survived are the ones you intended. The two minutes that takes are the cheapest insurance in spreadsheet work.
UNIQUE sidesteps the whole problem by never touching the source. If the spilled list looks wrong, you adjust the formula and the source data has not moved a pixel. That is the property that makes UNIQUE the right default for any sheet that an analyst will revisit.
Blanks, case, and other edge cases
Both tools share the same default matching logic, and both surprise people the same way. The four edge cases below catch almost every “but it kept the wrong row” report.
Blanks. Multiple empty cells in the source are treated as a single duplicate value. Remove Duplicates keeps one blank row; UNIQUE returns a single empty string at the top of its spilled list. If you want to ignore blanks entirely, wrap the source in FILTER: =UNIQUE(FILTER(A2:A100, A2:A100<>"")).
Case. Both tools are case-insensitive by default. “Berlin” and “BERLIN” count as the same value, and the one that survives is the first one encountered. For a case-sensitive distinct list, replace UNIQUE with a more verbose pattern that uses EXACT; Remove Duplicates has no case-sensitive mode at all.
Hidden and filtered rows. Remove Duplicates ignores the filter state and deletes rows that are not visible on screen. This is the single most-cited gotcha for analysts who filter to a subset, run the tool, and discover later that the hidden rows changed too. UNIQUE reads whatever range you give it, including hidden rows, but it never deletes anything — so the consequence is only an extra entry in the spilled list, not lost data.
Whitespace. “Berlin” and “Berlin ” (with a trailing space) are different values to both tools. If your source comes from a CSV or a web paste, run =UNIQUE(TRIM(A2:A100)) so trailing spaces stop creating false duplicates.
=UNIQUE(FILTER(TRIM(A2:A100), TRIM(A2:A100)<>"")) trims whitespace and drops blanks in one formula. Reach for it whenever an export feeds a dashboard.
Troubleshooting #SPILL!, #NAME?, and #CALC!
UNIQUE has three failure modes worth recognizing on sight. Each one has a single fix.
#SPILL!A cell inside the spill range already contains a value. UNIQUE refuses to overwrite it.
Clear or move the cells below the formulaClick the error indicator to see which cell is blocking the spill, then delete or relocate it.
#NAME? means Excel does not recognize the UNIQUE function name. You are almost certainly on Excel 2019 or earlier, where dynamic arrays do not exist. Microsoft’s own documentation on unique and duplicate values confirms UNIQUE is restricted to Microsoft 365, Excel 2021, and Excel for the web. On legacy builds you either upgrade or fall back to Remove Duplicates and a manual sort.
#CALC! appears when the array passed to UNIQUE is empty — usually because an upstream FILTER returned nothing. Wrap the whole formula in IFERROR(..., "") if you want the cell to stay quiet during an empty state instead of flashing red.
- Click the cell that displays the error.
- Read the small flag icon to the left of the cell — it names the conflict.
- Clear the blocking cell, fix the version, or wrap with IFERROR, then press Enter.
Picking the right one for the job
The decision is not which tool is better; it is which property your sheet needs. A one-time cleanup of a downloaded file is a job for Remove Duplicates. A reporting tab that should keep itself tidy as the source grows is a job for UNIQUE.
- ✓ Static cleanup of an export you will never refresh → Remove Duplicates
- ✓ Lookup list, dashboard input, or any tab that must stay in sync → UNIQUE
- ✓ Legacy Excel 2019 or older with no dynamic arrays → Remove Duplicates only
- ✓ You need just the values that appear exactly once → UNIQUE with
exactly_onceTRUE - ✓ Million-row dataset where formulas drag the file → Power Query instead of either
If the workbook will live longer than this week, default to UNIQUE. The source stays auditable, the deduped list keeps itself current, and a colleague who opens the file in six months can read your formula and understand what it does. None of that is true of a button click that left no trace.
For the messy upstream work that feeds these lists — splitting one column into many, or stripping inconsistent formatting — pair UNIQUE with helpers like TEXTSPLIT or Flash Fill so the deduplication step receives clean input. Garbage rows in still produce garbage distinct lists out, no matter which tool you pick.
Open the workbook that gives you the most grief and find the one column you deduplicate by hand every week. Add a helper tab, type =SORT(UNIQUE(Source!A2:A10000)), and point your downstream formulas at that helper instead of the raw source. The next refresh will update the list for you. Repeat for the next column, and the next, and the manual Remove Duplicates click will disappear from your routine without ceremony.