How to Get Unique Values Without Blanks in Google Sheets

In Google Sheets, sometimes we want to find unique values from a huge dataset. But unfortunately, if the dataset contains any blank cell, this also adds to the unique data value. In this article, we will explain how to extract Google Sheets’ unique values without blanks.

overview of google sheets unique without blanks

A Sample of Practice Spreadsheet

You can download Google Sheets from here and practice very quickly.

Steps to Get Unique Values Without Blank in Google Sheets

To get unique values from a dataset if you only apply the UNIQUE function, you will get values with blank cells. So, to extract unique values without blank cells you have to insert a complex formula to combine the UNIQUE and FILTER functions.

Step 1: Creating Dataset

To apply the method, first, we create a dataset.

  • Here, we develop a dataset that contains two columns: Employee Name & Age.
  • Both columns contain some blank cells.
  • To return to the output we also create two columns named Unique Names & Unique Ages.

dataset of google sheets unique without blanks

Read More: How to Use UNIQUE Function in Google Sheets (5 Applications)

Similar Readings

Step 2: Inserting Formula to Get Unique Values Except for Blanks

After creating the dataset, now it’s time to insert the formula to get unique values. To do so,

  • First, we apply the formula for the Employee Name column. So we select cell E5.

select cells in google sheets

  • Then insert the UNIQUE function. We use this function to extract only the unique values from the dataset, as the dataset contains a number of duplicates or repetitive values.
  • The UNIQUE function syntax is:
  • Here, range is the dataset from where you want to get the unique ones.

insert unique function

=FILTER(range,condition1,[condition2, ..])
  • Here, range represents the dataset from where you want to filter data, condition1 is the argument from which basis you filter the dataset and [condition2, ..] is the optional argument.

combine unique function with filter function

  • Now, add the B5:B17 range as the first argument of the filter function.

insert range in filter function

  • Then insert the B5:B17<>”” as the criterion1 parameter of the FILTER function.
  • Here,<>”” means not equal to blank. So we set the criterion for the range to filter only non blank values.

input criterion in google sheets

  • Finally, press ENTER and you will get the unique values that ignore the blank cells.

outcome of google sheets unique without blanks for text values

  • Similarly, you can combine the same two functions for getting the Unique Ages. Here, we add the UNIQUE and FILTER function. Then insert the range C5:C17 that contains numeric values.

input combined formula in google sheets

  • Now input the criterion for the same range with these <>”” symbols. Where <> means Not Equal and “” represent nonblank values.

add criterion in the formula

  • In the end, click on ENTER and get the desired unique values ignoring blank ones.

outcome of google sheets unique without blanks for numeric values

Read More: How to Filter Unique Values in Google Sheets (5 Simple Ways)

Step 3: Finalizing Outcome

  • After applying the same method for both text and numeric values in the dataset, we get our two desired columns Unique Name and Unique Ages. Both columns contain unique values without any blank cells.

final output of google sheets unique without blanks

Read More: How to Filter Unique Rows in Google Sheets (4 Easy Ways)

Things to Remember

  • Apply the formula carefully.
  • Insert the range properly.
  • To combine the formula, insert the UNIQUE function first and then the FILTER function.


We believe this article will help you to get a clear concept of unique values without blanks in Google Sheets. If you are keen to learn more about Google Sheets, you can visit the OfficeWheel website.

Related Articles

Annyca Tabassum

Annyca Tabassum

Hello, I'm Annyca Tabassum. I currently work for the SOFTEKO as a technical writer and content developer. I have completed my graduation and post-graduation from SUST.

We will be happy to hear your thoughts

Leave a reply