Google Sheets: How to Sort and Ignore Blanks


You can use the following formula to sort rows in Google Sheets while ignoring rows with a blank value in a particular column:

=QUERY(A1:B11,"select * where B is not null order by B")

This particular formula sorts the rows in the range A1:B11 by column B, ignoring any rows with a blank value in column B.

The following example shows how to use this formula in practice.

Example: Sort and Ignore Blanks in Google Sheets

Suppose we have the following dataset in Google Sheets that contains information about various basketball teams:

We can type the following formula into cell D1 to sort the rows of the dataset based on the value in the Points column, while simply ignoring any rows that have a blank value in the Points column:

=QUERY(A1:B11,"select * where B is not null order by B")

The following screenshot shows how to use this formula in practice:

Notice that the rows have been sorted by the value in the Points column and the rows that contained a blank in the Points column have been ignored.

By default, Google Sheets sorts the data in ascending order.

However, we can use the desc argument to instead sort in a descending order:

=QUERY(A1:B11,"select * where B is not null order by B desc")

The following screenshot shows how to use this formula in practice:

The rows are now sorted in descending order based on the value in the Points column.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Sort by Date in Google Sheets
How to Rank Items by Multiple Columns in Google Sheets
How to Use a RANK IF Formula in Google Sheets

Leave a Reply

Your email address will not be published.