Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to remove duplicates in Excel and Google Sheets
Topic:Duplicates & uniquenessExcel & Google Sheets
=UNIQUE(A2:A100)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Pick an empty cell where the clean list should start.
- 2Enter =UNIQUE(A2:A100), pointing at the column with duplicates.
- 3The distinct values spill down automatically and update as data changes.
Tips
- To delete duplicates in place instead, use Data → Remove duplicates (Sheets) or Data → Remove Duplicates (Excel).
- UNIQUE needs Excel 365 / Excel 2021. In older Excel, use Data → Remove Duplicates or Advanced Filter.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I remove duplicates without deleting the original data?
Use =UNIQUE() in a new column — it returns a deduplicated copy and leaves your source untouched.
What if UNIQUE is not available in my Excel?
Older Excel versions lack UNIQUE. Use the Data → Remove Duplicates button, which removes them in place.
More on Duplicates & uniqueness
See all →How-to
How to compare two columns
Check whether two columns match row by row, or find values in one column missing from another, using IF and COUNTIF.
How-toHow to get a sorted list of unique values
Extract a sorted, deduplicated list from a column using SORT+UNIQUE in modern Excel and Google Sheets, or array formulas in older versions.
How-toUNIQUE function vs Remove Duplicates in Excel and Sheets
Learn when to use the UNIQUE formula versus the Remove Duplicates tool in Excel and Google Sheets to eliminate or surface distinct values.
How-toHow to flag duplicate values
Use COUNTIF to flag duplicate values in a list with a simple formula that marks any entry appearing more than once.
How-toHow to find duplicate values
Identify which values appear more than once in a list using COUNTIF to add a Duplicate/Unique label to each row.
How-toHow to compare two lists for differences
Find values that exist in one list but not the other using COUNTIF to label each item as Matched or Unmatched.