See all How-To Articles

How to Sort and Ignore Blanks in Excel & Google Sheets

In this tutorial, you will learn how to sort while ignoring blanks in Excel and Google Sheets.

 

sort and ignore blanks initial data 1

 

Sort and Ignore Blanks

Sometimes in Excel, you’ll want to sort a data range that has blank rows. If you simply sort the data, Excel will automatically push blank rows to the very end. Look at the following data set to see how to ignore blanks when sorting data so the blank rows remain where they were before sorting.

 

sort and ignore blanks initial data 1

 

In this example, you have blank rows (6, 7, 13, 14, and 19). You want to sort the data by Total Sales from largest to smallest and ignore blanks.

  1. Select rows that you want to hide (6 and 7), right-click anywhere in the selected area and click Hide.

 

hide rows data 1

 

  1. Repeat Step 1 for Rows 13 and 14, then for Row 19. Now the five blank rows are hidden, and the data range looks like this:

 

rows hidden excel 1

 

  1. Right-click anywhere in the selected area and click Hide. The result is the same as in the previous steps: All blank rows are hidden.
  2. Now you can sort the data range by Total Sales and Excel will ignore the blank rows. To do this, select Column G then in the Ribbon, go to Home > Sort & Filter > Sort Largest to Smallest.

 

sort data largest to smallest 1

 

  1. In the pop-up window, choose Expand the selection and click Sort.

 

sort expand selection

 

As a result of this step, the data range is sorted by Total Sales in descending order, but blank rows are still hidden.

 

data sorted descending 1

 

  1. Finally, unhide all blank rows (6, 7, 13, 14, and 19).
    Select all rows in the data range (1:20), right-click anywhere in the selected area and click Unhide.

 

unhide blank rows 1

 

Now, you have data sorted by Total Sales. Blank rows were ignored, and they remain where they initially were.

 

sort and ignore blanks final data 1

 

Hide All Blank Rows at Once

There is another way to hide rows that allows you to hide all blank rows at once.

  1. Select the data range (B1:G20), and in the Ribbon, go to Home > Find & Select > Go To Special…

 

hide blanks go to special 1

 

  1. In the pop-up window, select Blanks and click OK.

 

go to special blanks

 

  1. Continue with Steps 3–6 above.

 

All blank cells in the data range are selected.

 

go to special blanks 2a

 

Sort and Ignore Blanks in Google Sheets

In Google Sheets, performing a sort with blanks ignored is done a bit differently.

  1. Select the data range (B1:G20) and click on the Filter icon in the toolbar.

 

google sheets create filter 1

 

  1. Click on the filter button next to SKU (cell B1), uncheck (Blanks), and click OK.

 

google sheets filter out blanks 1

 

  1. Now, all blank rows are hidden, and you can sort the data range.
    Click on the filter button next to Total Sales (cell G1) and choose Sort Z → A (descending).

 

google sheets sort descending 1

 

  1. The data range is now sorted by Total Sales in descending order, and you can unhide blank rows.
    Click on the filter button next to SKU (B1), check (Blanks), and click OK.

 

google sheets clear filter 1

 

Finally, all rows are visible, and the data range is sorted ignoring the blank rows.

 

google sheets data sort ignore blanks 1