Unique values ignore blanks
To extract a list of unique values from a set of data, ignoring blank cells, you can use the UNIQUE function together with the FILTER function. In the example shown, the formula in D5 is:
which outputs the 5 unique values seen in D5:D9.
This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove any blank values from the data:
FILTER(B5:B16,B5:B16<>"")
The <> symbol is a logical operator that means "does not equal". For more examples of operators in formula criteria see this page.
FILTER returns an array of values, excluding empty strings:
{"red";"amber";"green";"green";"blue";"pink";"red";"blue";"amber"}
This array is returned directly to the UNIQUE function as the array argument. UNIQUE then removes duplicates, and returns the final array:
{"red";"amber";"green";"blue";"pink"}
UNIQUE and FILTER are dynamic functions. If data in B5:B16 changes, the output will update immediately.
Dynamic source range
Because the range B5:B15 is hardcoded directly into the formula, it won't change if data is added or deleted. To use a dynamic range that will automatically resize as needed, you can use an Excel Table, or create a dynamic named range with a formula.
