When you delete rows, it is essential to double-check that you are not losing any data. Let’s see how to delete empty rows properly in Google Sheets.

When you clean up your data by removing duplicates or out-of-date transactions, you may often create several blank rows. It is advisable to remove these blank rows to keep your data tidy and organized.

There are a few other advantages to deleting empty rows in Google Sheets, including:

• Free up space. Removing empty rows can free up space on your sheet and can improve the performance of your sheet. It will also make your sheet easier to navigate.
• Improve the performance of array formulas. Deleting empty rows can improve the performance of array formulas and LAMBDA functions by reducing the amount of data they need to process.
• Neat printout. If you plan to print your sheet, deleting empty rows can make your printout look tidier and more organized.
• Avoid the hassle of filtering out empty rows when creating charts or pivot tables.

How to Properly Delete Empty Rows in Google Sheets

Usually, spreadsheet users use the Data menu > Create a filter to filter one of the columns in the table (data set) for “blanks” and delete the filtered empty rows.

It may result in data loss if the filtered column is blank and one or more other columns have values. To solve this, you must apply the filter to each column in the table for blanks. That’s time-consuming if you have data spread across several columns.

What we will do here is slightly different from the above approach.

We will use the built-in Filter menu command and a helper column with an array formula in the topmost row of it. This helper column will allow us to filter the data for blanks instead of filtering several columns individually.

We can use this method to delete adjoining and distant blank rows in Google Sheets in one go. There are three steps involved.

Step 1: Helper Column and Formula

The sample data is in three columns and several rows, namely A1:C. To find out if any of these rows are blank, we can insert the following array formula in cell D1:

=ARRAYFORMULA(IF(TRANSPOSE(TRIM(QUERY(TRANSPOSE(A1:C),,9^99)))="",TRUE,FALSE))

In short, we should insert our formula in the first cell of the last blank column in our table. Our helper column is ready.

If you want to delete empty rows in the range A1:Z1000, you can modify the above array formula by replacing A1:C with A1:Z. You should then insert the formula in the first cell in the next blank column, i.e., AA1.

Here is the corrected formula:

=ARRAYFORMULA(IF(TRANSPOSE(TRIM(QUERY(TRANSPOSE(A1:C),,9^99)))="",TRUE,FALSE))

Formula Logic:

The QUERY function with TRIM and TRANSPOSE joins columns. It is one of the flexible array formulas in Google Sheets for joining columns. The IF function tests whether the joined columns contain any values or not. If not, it will return TRUE, or else FALSE.

In the coming steps, we will filter TRUE values and remove them. That will be equal to deleting empty rows.

Step 2: Filter If the Helper Column Value Is True

In the first step, we successfully marked blank rows using a formula: TRUE represents blanks, and FALSE represents non-blanks. Now we can filter blank rows.

1. Select the column that you want to filter. As per the example, we want to filter our helper column D. To select, click on the column letter “D.”
2. Go to the DATA menu. Select Create a Filter or click on the Filter icon in the toolbar.
3. In the Filter values drop-down list in cell D1, uncheck FALSE.
4. Click on the OK button.

Now all the visible rows, except the header, are blank. In the next step, we will delete all of them in one go.

Step 3: Delete Filtered Empty Rows

We want to select all the blank rows quickly. How can we do that?

You can select a range of blank rows by clicking on the first empty row number and then dragging the mouse down to the last row number.

For example, if you want to select blank rows 3 to 10, you would click on row number 3 and then drag the mouse down to row 10.

Note:- You may hold down the click; otherwise, it will move the row instead of selecting rows down.

In our case, we should click on row # 3.

To delete selected empty rows and complete the steps, please follow these:

1. Go to the EDIT menu and click Delete > Selected Rows.
2. Go to the DATA menu and click the Remove filter.
3. Remove the formula in cell D1.

The above is the proper way to delete empty rows in Google Sheets.

Other Methods to Delete Empty Rows in Google Sheets

There are three other methods to remove blank rows in Google Sheets:

• Sort the data to place blank rows at the top or bottom and delete them. This method is simple and easy to use, but it has some limitations. For example, it will not remove blank rows that are hidden, and it may not be helpful for those who don’t want to sort their data.
• Use the QUERY or FILTER function to remove blanks. These functions are more powerful than the sort method, and they can be used to remove blank rows without sorting the data. However, they can be more difficult to use if you are not familiar with them. Additionally, they create a new dataset without blank rows, so you may have two datasets: the original one and the formula-created one.
• Use third-party add-ons. There are a few third-party add-ons that can be used to remove blank rows from Google Sheets.

Examples

Here is an example to delete empty rows using the sorting method in Google Sheets.

1. Open your Google Sheets file and select the range of cells that contains the data you want to delete blank rows from. Exclude the header row while selecting. In this example (please see the figure below), we must select A2:C.
2. Click on Data > Sort range > Sort range by column A (A to Z). The column letter may be different depending on the range you want to sort.
3. This will sort the data in your spreadsheet, moving all the empty rows to the bottom. You can then select the empty rows and delete them.

Here is an example to delete empty rows properly using the FILTER function method in Google Sheets. The example is as per my sample data above.

Generic Formula:

=FIITER(range,helper_formula=FALSE)

The range is A1:C and the helper_formula is the one we used in our “Step 1: Helper Column and Formula” above.

Formula:

=FILTER(A1:C,ARRAYFORMULA(IF(TRANSPOSE(TRIM(QUERY(TRANSPOSE(A1:C),,9^99)))="",TRUE,FALSE))=FALSE)

Note: You can remove the ARRAYFORMULA. It’s not required within the FILTER function.

If you use the formula in a second sheet (the sheet other than the source sheet) in the same workbook, replace A1:C with Sheet1!A1:C where Sheet1 is the data tab name.

Here is the QUERY formula for Google Sheets enthusiasts:

=QUERY({A1:C,ARRAYFORMULA(IF(TRANSPOSE(TRIM(QUERY(TRANSPOSE(Sheet1!A1:C),,9^99)))="",TRUE,FALSE))},"Select Col1,Col2,Col3 where Col4=FALSE")

Regarding the add-ons option, I don’t recommend any as I’ve tested none of them. Please go as per the user reviews and your own test experience. To get add-ons, go to the Extensions menu in Google Sheets.

That’s all. Thanks for the stay. Enjoy!