Unique values

=UNIQUE(data)
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.
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.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.