HomeGoogle DocsSpreadsheetHow to Use UNIQUE Function in Horizontal Data Range in Google Sheets

How to Use UNIQUE Function in Horizontal Data Range in Google Sheets

Published on

Do you know how to use the Unique function in a horizontal data range in Google Sheets? Earlier, we required a helper function. But not now.

Most of the Google Sheets functions work well with vertical tables. But there are exceptions to this.

The Hlookup function in Google Sheets works with a horizontal data set.

Ever tried to use the Unique function in the horizontal data range in Google Sheets? If you try, you can find that it simply returns the data set as it’s.

There is a supporting function in Google Sheets that can help you to solve this dilemma. I’ll come to that in the example section below.

Why does the UNIQUE function behave so?

The reason behind it is simple. The function Unique is specifically to use on vertical data tables.

The Unique formula function finds and eliminates duplicates in rows in single or multiple-column tables.

If you still have any doubts, see the examples below.

Update:- It now supports horizontal data sets too. Please read Filter Distinct Columns or Rows in Google Sheets – UNIQUE Improvements. Still, the below method works. So you can read on.

Google Sheets Unique Function in Vertical Data Set

Below is an example that shows how the Unique function works in a vertical data set. As you can see, it discards the duplicates and returns unique values.

=unique(A1:B6)
Google Sheets Unique Function in Vertical Data Set

The Unique formula works perfectly in similarly arranged data tables.

Google Sheets Unique Function in Horizontal Data Range

The following example shows (the formula after the screenshot) how to use the Unique function in a horizontal data range in Google Sheets.

But the screenshot below shows how not to use Google Sheets’s Unique formula horizontally.

The formula returns the data without any change as it’s not the correct use.

=unique(A1:F2)
how not use Google Sheets UNIQUE formula horizontally

Then what is the proper use of Unique in a horizontal data set?

At the beginning of this tutorial, I mentioned a supporting function that can solve such issues.

Yes! You can use the TRANSPOSE function together with most of the functions to make them support horizontal data sets.

The proper use of the Unique function in the above horizontal data range is as follows.

=transpose(unique(transpose(A1:F2)))

Note:- You can now use it as follows. The Transpose is not required.

=unique(A1:F2,true)

Anatomy of the Transpose-Based Formula:

=transpose(A1:F2)

The above formula converts the horizontal data set to a vertical one.

=unique(transpose(A1:F2))

This formula removes the duplicates. Now the result is in vertical form.

With another Transpose formula, we can make the data again appear horizontally. That’s all.

This way, you can use the Unique function in the horizontal data range in Google Sheets.

These tips apply to most of the functions in Google Sheets.

Related Formulas:

  1. How to Apply Unique in Selected Columns in Google Sheets.
  2. Group and Average Unique Column in Google Sheets.
  3. How to Use COUNTIF with UNIQUE in Google Sheets.
  4. How to Use UNIQUE and SUM together in Google Sheets.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here