Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
UNIQUE function vs Remove Duplicates in Excel and Sheets
Topic:Duplicates & uniquenessHeads up: Excel and Google Sheets do this differently.
Excel
=IFERROR(INDEX(A2:A6,MATCH(0,COUNTIF($C$1:C1,A2:A6),0)),"")Google Sheets
=UNIQUE(A2:A6)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Category | |
| Fruit | |
| Vegetable | |
| Fruit | |
| Dairy | |
| Vegetable |
=IFERROR(INDEX(A2:A6,MATCH(0,COUNTIF($C$1:C1,A2:A6),0)),"")→
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
- 1Decide whether you need a live, updating list (use UNIQUE formula) or a permanent cleanup of your data (use Remove Duplicates tool).
- 2For the UNIQUE function in Excel 365 or Google Sheets, type =UNIQUE(A2:A6) in an empty cell — results spill automatically into adjacent cells.
- 3In Google Sheets, UNIQUE also works on multiple columns: =UNIQUE(A2:B6) returns rows where the combination of A and B is distinct.
- 4For Remove Duplicates in Excel: go to Data tab → Remove Duplicates; in Google Sheets: Data menu → Data cleanup → Remove duplicates.
Tips
- Use UNIQUE combined with SORT for a clean, sorted dropdown source: =SORT(UNIQUE(A2:A100)) keeps your validation list fresh automatically.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Does UNIQUE exist in older Excel versions?
UNIQUE requires Excel 365 or Excel 2021. On older versions, use the Remove Duplicates tool or an array workaround with COUNTIF and INDEX.
Will UNIQUE update when I add new data?
Yes — as long as your source range is large enough or uses a full-column reference (A:A), the UNIQUE result refreshes automatically when data changes.
More on Duplicates & uniqueness
See all →How-to
How to remove duplicates
Remove duplicate values with one formula using UNIQUE, or strip them in place with the built-in tool. Works in Excel 365 and Google Sheets.
How-toHow 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-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.