Home › Functions › Lookup & Reference › =UNIQUE()
Lookup & Reference Excel 2021 ◉ 19 views Dynamic Array

=UNIQUE()

Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacMicrosoft365.comExcel for iPadExcel for iPhoneExcel for Android tabletsExcel for Android phones
=UNIQUE(array,[by_col],[exactly_once])
Hover argument names in the table for details.
Output typeArray
Popularity80 / 100
VolatileNo
Dynamic ArrayYes
📥 Download sample .xlsx — Coming soon ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ Download Sample Workbook

A dedicated .xlsx for =UNIQUE() with Persian sample data & live formulas — open in Excel and practice.

  • Sheet “Sample” — data table + formula in place
  • Sheet “Guide” — syntax, arguments & tips from this page
  • Instant & secure (token + rate limit)
📥 Download UNIQUE.xlsx — Coming soon ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

🧠 🧠 Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
array Range Required The range or array from which to return unique rows or columns
by_col] Logical Optional The by_col argument is a logical value indicating how to compare.TRUE will compare columns against each other and return the unique columnsFALSE (or omitted) will compare rows against each other and return the unique rows
exactly_once] Logical Optional The exactly_once argument is a logical value that will return rows or columns that occur exactly once in the range or array. This is the database concept of unique.TRUE will return all distinct rows or columns that occur exactly once from the range or arrayFALSE (or omitted) will return all distinct rows or columns from the range or array

▣ Examples

Example 1 =UNIQUE(B2:B100)
Example 2 =UNIQUE(B2:B100, , TRUE)

✦ Tips

  • If you format the range of names as an Excel table, then the formula will automatically update when you add or remove names. If you want to sort the list of names, you can add the SORT function: =SORT(UNIQUE(B2:B12&" "&A2:A12))

⇄ Related Functions

=FILTER() How to use the FILTER function in Excel to filter a range of data based on criteria you define. =SORT() Learn about the SORT function, which sorts the contents of a range or array. SORT is in a class of functions called dynamic arrays. =COUNTIF() Conditional count (a single condition)

💬 Comments & Q&A 0 approved

🔒 Your comment will be published after review.

Share your thoughts — visible after admin approval

No comments yet — be the first!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.