There are two methods that you can adopt to sort rows and bring the blank cells on top in Google Sheets. It’s by using the FILTER command or by using the QUERY.
As a side note, there may be more methods, but I am sticking to the above two in this tutorial.
In this Google Sheets tutorial, you can learn the above two methods to sort rows and bring the blank cells to the top in a column.
Some of you may ask why I am recommending Query instead of the Sort function. To know this, you must understand one main difference between QUERY and SORT in sorting rows.
I’ll come to this specific part in the later part. First, let’s see how to use the FILTER menu command to sort rows and bring the blank cells on the top in a column.
Let’s start by using sample data, which is very basic that contains a few rows and two columns.
Sort Rows to Bring the Blank Cells on Top Using the FILTER Command
Google Sheets FILTER command (DATA > Create a filter) has options to sort a table. We can use that for our purpose.
Here are the steps to follow.
1. Select the table (data set) A1:B7 and go to the menu ‘Data’ and click ‘Create a filter’.
2. We want to sort column A and to bring the blank cells on the top (to appear first) of the column. So first filter Column A as below.
- Click on the drop-down in cell A1.
- Click ‘Clear’.
- Select (Blanks).
- Click ‘OK to segregate the blank cells.
3. Select the blank cells A2:A3 and fill any color, for example, “Yellow”.
4. Click the drop-down again and click “Select all” and click “OK”.
5. Click the drop-down the third time, and this time click on Sort by color > Fill color > Yellow.
This way we can sort rows and bring the blank cells on top of the data set (table) in Google Sheets.
Sort Rows to Bring the Blank Cells on Top Using a QUERY Formula
Here is a formula approach to use in Google Sheets. It will help you to populate the data in a new range with the desired sort output.
We can use the QUERY function to sort and put the blank rows on the top of the table in Google Sheets.
In Google Sheets QUERY, there are several clauses, and one among them is ‘ORDER BY’. We can use that clause here along with the ‘SELECT’ clause.
In cell E1, use the below Query.
=Query(A1:B7,"Select * order by A asc",1)
If you are planning to use the Query in a new tab, include the sheet name with the range.
I mean, A1:B7 must be replaced by 'new sheet'!A1:B7
. Change 'new sheet'
with the sheet name that contains the sample data, and the sheet name must be placed within single quotes (apostrophes).
As a side note, if you use any other clauses in Query, you should strictly follow the Query clause order.
Why can’t we use the dedicated SORT or SORTN functions here for sorting rows and bring the blank rows to the top of the table?
To understand this you should know the sort difference between QUERY and SORT.
The SORT function puts the blank cells to the bottom in both ascending and descending sort order, whereas the Query function puts the blank cells to the top in ascending sort order and to the bottom in the descending sort order.
That doesn’t mean we can’t use the SORT function for our above purpose.
If you are not familiar with Query, you can use the SORT function itself. But you may need to use a helper column.
You may please find that method below.
SORT + Helper Column Approach
First, insert the following formula in cell C1 (before inserting the formula, you may make sure that column C is empty as we are going to use this column as our helper column).
={"Helper";ArrayFormula(if(A2:A7="",TRUE))}
This formula will place the Boolean TRUE values wherever the rows are blank in the range A2:A7 and the Boolean FALSE values wherever the rows are not blank in A2:A7.
Now sort the 3rd column (C2:C7) in descending order and the first column in ascending order. For that, use the below SORT formula in cell E2.
=sort(A2:B7,C2:C7,0,1,1)
The above are the methods to sort rows to bring the blank cells to the top in Google Sheets.
Thanks for the stay. Enjoy!
Additional Resources:
- Sort by Custom Order in Google Sheets [How to Guide].
- Sort Data in Google Sheets – Different Functions and Sort Types.
- How to Sort Horizontally in Google Sheets.
- Custom Sort Order in Google Sheets Query [Workaroud].
- Sort By Sort_Column Name Instead of Sort_Column Header in Google Sheets.
- Dynamic Sort Column and Sort Order in Google Sheets.
- How to Stop Array Formula Messing up in Sorting in Google Sheets.
- How to Properly Sort Alphanumeric Values in Google Sheets.
- Sort Items by Number of Occurrences in Google Sheets.