How to Bring Blank Cells to the Top When Sorting Rows in Google Sheets

Have you ever found yourself struggling to sort rows in Google Sheets and bring the blank cells to the top? Don’t worry, I’ve got you covered! In this tutorial, I’ll show you two simple methods to achieve this. So let’s dive right in!

Using the FILTER Command

The first method involves using the FILTER command in Google Sheets. Here’s how you can do it:

1. Select the table (data set) that you want to sort.
2. Go to the ‘Data’ menu and click on ‘Create a filter’.
3. Now, let’s say you want to sort column A and bring the blank cells to the top. To do this, follow these steps:
   • Click on the drop-down arrow in cell A1.
   • Click on ‘Clear’ to remove any existing filters.
   • Select ‘Blanks’ to filter out the blank cells.
   • Click ‘OK’ to segregate the blank cells.
4. Next, select the blank cells (e.g., A2:A3) and fill them with any color you like, let’s say “Yellow”.
5. Click on the drop-down arrow again and select ‘Select all’. Then click ‘OK’.
6. Lastly, click on the drop-down arrow for the third time. This time, choose ‘Sort by color’ > ‘Fill color’ > ‘Yellow’.

And there you have it! By following these steps, you can easily sort rows and bring the blank cells to the top in your Google Sheets.

Using a QUERY Formula

If you prefer a formulaic approach, you can use the QUERY function to achieve the desired result. Here’s how:

1. In the cell where you want the sorted output to appear (let’s say E1), use the following QUERY formula:
   =Query(A1:B7,"Select * order by A asc",1)

   

   If you plan to use the QUERY formula in a new tab, remember to include the sheet name with the range. For example, ‘new sheet’!A1:B7, with ‘new sheet’ replaced by the actual sheet name within single quotes (apostrophes).

   It’s worth noting that if you use any other clauses in the QUERY formula, make sure to follow the correct order of the clauses.

SORT + Helper Column Approach

Alternatively, you can use the SORT function along with a helper column. Here’s how:

1. Insert the following formula in cell C1 (make sure column C is empty before using this formula, as we’ll be using it as a helper column):
   ={"Helper";ArrayFormula(if(A2:A7="",TRUE))}

   This formula will place TRUE values in the helper column wherever the rows are blank in the range A2:A7, and FALSE values wherever the rows are not blank.

2. Now, sort the 3rd column (C2:C7) in descending order and the first column in ascending order. Use the following SORT formula in cell E2:
   =sort(A2:B7,C2:C7,0,1,1)

   

By employing this method, you can effectively sort rows and bring the blank cells to the top in Google Sheets.

And that’s a wrap! You now have two handy methods at your disposal to sort rows and bring blank cells to the top in Google Sheets. Give them a try and enjoy the ease and efficiency they offer.

Thanks for staying with me throughout this tutorial. If you’d like to explore more Google Sheets tips and tricks, be sure to visit Crawlan.com. Happy sorting!