How to use the UNIQUE function in Excel?

What does the UNIQUE Function in Excel do?

Before learning how to use the UNIQUE function in Excel let’s have a look at what it does. The UNIQUE function in Excel is used to extract distinct values from a range, helping to remove duplicates and streamline data analysis.

Why Is the UNIQUE Function Useful?

The UNIQUE function is particularly useful for creating filtered lists, summarizing data, or identifying unique entries in a dataset. In short, its advantages are:

• Eliminates duplicate entries without manual filtering.
• Simplifies data validation by generating lists of distinct values.
• Enhances analysis by identifying unique records.
• Saves time compared to manually sorting and removing duplicates.

How to use the UNIQUE function in Excel?

Syntax of the UNIQUE Function

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

• array (Required) – The range or array from which to extract unique values.
• by_col(Optional) – A logical value (TRUE or FALSE), where:
  • FALSE (default) – Compares rows and returns unique rows.
  • TRUE – Compares columns and returns unique columns.
• exactly_once(Optional) – A logical value determining if only values that appear once should be returned:
  • FALSE (default) – Returns all unique values.
  • TRUE – Returns only values that appear exactly once (ignores duplicates).

Example Uses of the UNIQUE Function

Example Dataset

1. Extracting Unique Names

To get a list of unique names from column A:

=UNIQUE(A2:A8)

🚨 Output:
Alice
Bob
Carol
Dave

2. Extracting Unique Department Names

To extract unique values from column B:

=UNIQUE(B2:B8)

🚨 Output:
HR
IT
Finance

3. Extracting Unique Name-Department Combinations

To remove duplicates based on both columns:

=UNIQUE(A2:B8)

🚨 Output:

4. Returning Only Names That Appear Once

If you want to filter out names that appear more than once:

=UNIQUE(A2:A8, FALSE, TRUE)

🚨 Output:
Carol
Dave