The UNIQUE Function: A Guide to Extracting Unique Values in Excel

The UNIQUE Function: A Guide to Extracting Unique Values in Excel

Working with data often means dealing with duplicate entries, and a common task is to create a clean list of only the unique values. Manually sifting through a long list to remove duplicates can be a tedious and error-prone process. The **UNIQUE function**, introduced in Microsoft 365, is a powerful dynamic array function that automates this task. With a single formula, you can extract a list of every unique value from a range of cells, and the results will automatically update as your source data changes.

How the UNIQUE Function Works

The `UNIQUE` function has a simple syntax that makes it incredibly easy to use:

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

• array: The range of cells that you want to extract unique values from. This is the only required argument.
• [by_col] (optional): A logical value (`TRUE` or `FALSE`) to specify whether to compare columns (`TRUE`) or rows (`FALSE`). The default is `FALSE` (comparing rows).
• [exactly_once] (optional): A logical value (`TRUE` or `FALSE`) to specify whether to return values that appear exactly once (`TRUE`). The default is `FALSE`.

Since it's a dynamic array function, you simply enter the formula into a single cell, and the results will "spill" into the cells below it, creating your list of unique values.

A Practical Example: Cleaning a Customer List

Imagine you have a long list of customer names, and many appear multiple times. You want to create a clean list of unique customer names. Your source data might look like this:

Customer Name
John Smith
Jane Doe
John Smith
Peter Jones
Jane Doe

To extract the unique names, you would enter this formula into an empty cell:

=UNIQUE(A2:A6)

The `UNIQUE` function would then automatically return a new list containing only "John Smith," "Jane Doe," and "Peter Jones." By using `UNIQUE`, you can save time and ensure your data is always clean and up-to-date.