Unique values
Generic formula
=UNIQUE(data)
Explanation
To extract a list of unique values from a set of data, you can use the UNIQUE function. In the example shown, the formula in D5 is:
=UNIQUE(B5:B16)
which outputs the 7 unique values seen in D5:D11.
How this formula works
This example uses the UNIQUE function, which is fully automatic. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11.
UNIQUE is a dynamic function. If any data in B5:B16 changes, the output from UNIQUE will update immediately.
Dynamic source range
UNIQUE won't automatically adjust the source range if data is added or deleted. To feed UNIQUE a dynamic range that will automatically resize as needed, you can use an Excel Table, or create a dynamic named range with a formula.
Note: This function is a new "Dynamic Array Function" in Excel. It is a beta feature available only through the Office Insiders program. Dynamic Array functions are expected to be released in 2019 to Office 365 subscribers.
Related formulas
Related functions
