which outputs the 2 distinct values in the data, “purple”, and “gray”. In the example shown, UNIQUE’s arguments are configured like this:
array - B5:B16 by_col - FALSE occurs_once - TRUE
Because occurs_once is set to TRUE, UNIQUE outputs the 2 values in the data that appear just once: “purple”, and “gray”. Notice the by_col argument is optional and defaults to FALSE, so it can be omitted: TRUE and FALSE can also be replaced with 1 and zero like this:
Dynamic source range
UNIQUE won’t automatically change the source range if data is added or deleted. To give 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
