Do you know how to get/filter distinct columns or rows using a formula in Google Sheets? If not, please read on. One of the Google Sheets functions has this additional capability now!

This post is about a new update in Google Sheets. Yep! The UNIQUE function in it now supports not only filtering rows but also columns.

There is an additional capability too. That’s the topic of this post.

I mean, the above-said function now supports filtering distinct columns or rows in Google Sheets. Earlier, we were using workarounds for the same.

You can see the updated syntax by typing =UNIQUE( in a blank cell in one of your Google Spreadsheet files.

Earlier Workarounds

Distinct Rows (Rows Existing Exactly Once in the Range) – For this, I used Query in the past. Here is that related tutorial – How to Find Distinct Rows in Google Sheets Using Query.

Distinct Columns (Columns Existing Exactly Once in the Range) – In fact, there is no existing tutorial in this blog that offers a workaround. But possibly the above Query method will work in this case too by applying TRANSPOSE.

That’s (the workarounds) the story of the recent past.

Below, I will explain the use of the new arguments/parameters in UNIQUE that the new update/improvement brings. On the course, you can learn the following.

1. How to get/filter distinct columns using the UNIQUE function Google Sheets.
2. How to get/filter distinct rows using the UNIQUE function in Google Sheets.
3. Filtering unique columns.
4. Filtering unique rows (already were in practice).

Regarding the third point, you should understand one thing. Earlier, the function was only supporting to make the rows unique (point # 4). Now it supports columns too.

All that new UNIQUE function features you can learn with examples below.

New Syntax

Earlier Syntax: UNIQUE(range)

New Syntax: UNIQUE(range, [by_column], [exactly_once])

The two new optional arguments are within the square brackets.

In this new syntax, by_column determines whether to unique rows or columns in the range.

What about exactly_once?

This argument determines whether to filter distinct columns or rows using Unique.

Please note that the new update won’t affect your existing Unique formulas in your sheet.

Because by default the two new arguments are set to FALSE. That means the function without those arguments will work as earlier.

Let me clear things for you.

Filter Unique Rows In Google Sheets

Here is one example.

To filter the unique rows in the range B2:D5 (please refer to the image below), we can use the UNIQUE formula as earlier.

=UNIQUE(B2:D5)

I have omitted the two optional arguments since it’s already set to FALSE. If you want, use those arguments as below.

=UNIQUE(B2:D5,false,false)

Both the formulas will bring the same result.

Filter Unique Columns In Google Sheets

To filter unique columns, we were using the TRANSPOSE with UNIQUE in Google Sheets.

I have written a tutorial explaining that combo use here – How to Use UNIQUE Function in Horizontal Data Range in Google Sheets.

Now, we don’t want to depend on that combination.

Take the second formula above and change the value in the second argument (first optional argument), i.e., by_column, to TRUE. It will be as follows.

=UNIQUE(B2:D5,true,false)

As I have mentioned above, we can follow an alternative method that involves TRANSPOSE and UNIQUE as below.

=transpose(unique(TRANSPOSE(B2:D5)))

We don’t require to use this alternative solution now.

Filter Distinct Columns or Rows In Google Sheets

In the above two examples, we have experimented with the first optional argument (second argument).

I mean, we have tested the two Boolean values (TRUE/FALSE) in the second argument. In simple terms, use FALSE to filter unique by row and TRUE to filter unique by columns.

Here is the syntax once again for your quick reference: UNIQUE(range, [by_column], [exactly_once])

I hope you are now well familiar with the first two arguments. Here is the last one.

To get distinct columns or rows in Google Sheets, you need to use the Boolean TRUE in the last optional argument.

Formula in Cell F2:

=unique(B2:D5,false,true)

Formula in Cell F6:

=unique(B2:D5,true,true)

If you don’t want to filter distinct columns or rows, don’t use the last argument or use FALSE.

I hope you could understand unique rows or columns and its difference from distinct rows or columns.

That’s all. Thanks for the stay. Enjoy!