How to Use the UNIQUE Function in Excel
Last updated: 2026-09-06
⚡ Quick Answer
The UNIQUE formula automatically removes duplicates from lists and arrays non-destructively.
=UNIQUE(FILTER(A2:A500, A2:A500<>""))📊 Visual Demo
HOME · INSERT · FORMULAS · DATA
fx =UNIQUE(FILTER(A2:A500, A2:A500<>""))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1 | Acme Corp | East | =UNIQUE(A2:A4) | Acme Corp |
| 2 | 2 | Global Log | West | Global Log | |
| 3 | 3 | Acme Corp | East | (Duplicates Skipped) |
🧠 Deep Dive
The UNIQUE formula automatically removes duplicates from lists and arrays non-destructively.
Key Insights
- Import problems are often hidden non-breaking spaces.
- Numbers stored as text break SUM and lookups.
Common Mistakes
- Running VLOOKUP against uncleaned keys.
- Using Find & Replace on spaces blindly.
Pro Tips
- Add a helper column with LEN().
- Standardize case with UPPER.
FAQ
What does this formula do in plain English?
The UNIQUE formula automatically removes duplicates from lists and arrays non-destructively.
Why is my formula not working?
Running VLOOKUP against uncleaned keys.
Does this work in Google Sheets?
Yes, this syntax is fully compatible with Google Sheets.
What is the most common mistake?
Using Find & Replace on spaces blindly.
Related Guides
How to Remove Extra Spaces in ExcelHow to Split Text in ExcelHow to Highlight Duplicates in ExcelHow to Use IS Data Validation FunctionsExcel Error Hub
Need Complete Playbooks?
Download our verified manuals covering ERP Cleanup and Modeling.
Explore Playbook Series (From ₹199)