How to Display Unique Values of Multiple Columns in a Single Column in Google Sheets

This one is going to be a really straight-forward and short article. For one of my projects, I had values spanned across multiple columns in a spreadsheet in Google Sheets and needed to get the unique values from all of these cells compiled in one single column.

If you’ve tried previously to use the formula UNIQUE and setting the range within this formula to a range that contains multiple columns, you probably ended up with the same result as I did:

=UNIQUE(Sheet14!B:Q)

Now, in an ideal world, we would want this UNIQUE formula to output every unique value from all of these columns into one column.

Thankfully, Google has thought about this issue and provided us with the FLATTEN formula. Here is a little summary of the formula from the Google Sheets’ Helper:

Now, if we combine this FLATTEN formula with our UNIQUE formula which had a range spanning across multiple columns, here is the result:

=UNIQUE(FLATTEN(Sheet14!B:Q))

Magic. When I found out about this formula I was just ecstatic. It’s a game-changer which benefits not only this specific scenario but many others.

And that’s really it for this article. I just wanted to give you the solution as quickly as possible. If you appreciated this article and are working in the Marketing Technology space, please consider joining my weekly newsletter which is already followed by hundreds of marketers around the world. In this newsletter I share custom-made tools by myself and other marketers as well as insightful articles about the industry.

I hope you found what you were looking for. If you still have any questions about this specific article, don’t hesitate to reach out to me by email or through the comments section available below this article.

Thank you for your time!