Quick answer
The Remove Duplicates formula returns a unique list from a range without manually deleting rows.
=UNIQUE(A2:A100)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=UNIQUE(range)When to use it
Data cleaning formulas save time before analysis. They help repair exported data, standardize columns, remove invisible characters, and prepare spreadsheets for pivot tables, lookups, and dashboards.
- Find duplicate records
- Convert text numbers into real numbers
- Clean phone numbers for consistent formatting
- Create dropdown validation lists
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Original imported value |
| B | Cleaned value or flag |
| C | Check/result column |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic Remove Duplicates example
=UNIQUE(A2:A100)Use the simple version first and compare the result to a manual check.
Copy down a table
=UNIQUE(A3:A100)After testing the first row, copy the formula down the rest of the column.
Use a fixed input cell
=UNIQUE(A2:A$H$2)Lock assumptions, targets, or thresholds with dollar signs when needed.
Use an Excel Table
=UNIQUE(Table1[Category])Tables make formulas more readable and resilient.
Make the formula report-safe
=IFERROR(UNIQUE(A2:A100),"")Avoid visible errors in a final report when inputs are missing.
Use LET for clear logic
=LET(result,UNIQUE(A2:A100),result)LET helps document the calculation inside the formula itself.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- The referenced range does not include all rows.
- The cell format makes the result look wrong even when the formula is correct.
- A fixed assumption cell was copied down without dollar signs.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the Remove Duplicates formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.