How to Use UNIQUE Function in Excel + tips

Hello everyone! Today i will show you how to use UNIQUE function in Excel step by step. UNIQUE is available in Excel 365 and Excel 2021+.

What is UNIQUE Function in Excel?

The UNIQUE function help you remove duplicate items and extract a list of unique values automatically from a range. When your original list change or add new data, the unique list update automatically! No need click Remove Duplicates button manually anymore.

📚 Related Tutorial: Want to extract matching data with criteria first? Check out How to Use FILTER Function in Excel!

🛠️ Formula Syntax

=UNIQUE(array, [by_col], [exactly_once])

• array: The range or column containing duplicate items (example: A2:A7).
• [by_col] (Optional): FALSE to compare rows (default), TRUE to compare columns.
• [exactly_once] (Optional): FALSE to return all distinct values (default), TRUE to return ONLY items that appear exactly 1 time!

📊 Simple Examples

Example 1: Basic Unique List (Remove Duplicates)

Suppose column A2:A7 contains duplicate fruits:

Formula:

=UNIQUE(A2:A7)

Result: Excel automatically removes duplicates and returns clean list:

Example 2: Unique List in Alphabetical Order (UNIQUE + SORT)

To get unique values sorted A to Z automatically:

=SORT(UNIQUE(A2:A7))

Result: Apple, Banana, Mango, Orange

Example 3: Unique Values from Table Column (For Dynamic Dropdown)

If your data table is named Sales and product column is Product:

=UNIQUE(Sales[Product])

This is super useful to create a dynamic dropdown list in Excel!

Example 4: Get Items That Appear EXACTLY Once ([exactly_once] = TRUE)

If you want to find items that appear only 1 time (no duplicates at all):

=UNIQUE(A2:A7, , TRUE)

Result:

Banana
Mango

(Because Apple and Orange appear 2 times!)

Example 5: Extract Unique Combination Rows

Suppose A2:B6 contains Product and Category:

Formula:

=UNIQUE(A2:B6)

Excel checks entire row and returns each unique row combination.

Example 6: Combine UNIQUE + FILTER (Unique Items by Condition)

To get a unique list of employee names from department "IT":

=UNIQUE(FILTER(A2:A100, B2:B100="IT"))

⚡ Dynamic Array Functions Comparison

💡 Quick Tips & Easy Memory Rule:

1. Memory Rule: UNIQUE = Keep only 1 of each item and remove duplicates.
2. Pro Tip for Dynamic Dropdown List: Put =UNIQUE(A2:A100) in cell H2, then set Data Validation List source to =H2# (with # spill symbol) for automatic updating dropdown list!

📚 Related Resources & Tutorials

• How to Use FILTER Function in Excel
• How to Use SORT and SORTBY Functions in Excel
• VLOOKUP vs XLOOKUP in Excel: When to Use Each
• Top 15 Basic Excel Formulas for Beginners ↗