Do you have empty rows in your data that you want to get rid of? This post is going to show you how to remove blank rows from your data in Google Sheets.

Sometimes columns in your dataset will contain empty or blank cells. Usually, this isn’t such a big deal and might even be part of the data.

When blank cells span an entire row, however, then it becomes a different thing altogether. Blank rows are just that. Blank! Unless you intend to fill them up later, there’s no good reason for blank rows to remain in your data.

Having blank rows in your data can even make your work more complicated.

There are different approaches to managing the problem of blank rows. In this post, you will be exposed to all the ways to remove blank rows from your data. Get your copy of the example workbook used in this post to follow along!

Remove Blank Rows with a Filter

Filtering allows you to cut down the amount of information on display in your dataset.

Filtering allows you to focus on only specific data that meets a criterion. This is a particularly handy tool, especially when working with large datasets that have thousands of rows.

You can specify the conditions by which you want to filter your dataset. This includes the option to filter blank cells.

The above dataset has some blank rows that need to be removed.

The following steps will show you how to remove blank rows using the filter tool.

1. Select the cell ranges containing the data and create a filter.
2. You can use the Create a filter command from the toolbar or the one in the Data menu.

A downward-facing pyramid icon will appear in the top rows of the data set after you apply a filter.

3. Select any column and click on the filter icon.

Since you’re looking to remove blank rows, it doesn’t matter which column you choose.

The dropdown options that appear when you click on the filter icon show that all the items in that column are selected.

4. Click on the Clear command to deselect all the items.

5. This allows you to select only the Blanks option.
6. Click on the OK button.

7. Select all the blank cells in the filtered range.
8. Right-click anywhere on the selection.
9. Select Delete selected rows from the right-click menu.

You’ll have deleted the blank rows from the data. You will see that the data now has some hidden rows because there are no more blank rows.

Click on the filter icon and choose the Select all option and press the OK button. This will reveal the entire dataset.

You can also remove the Create a filter command using the toolbar icon or the Data menu option to show all the data again.

Remove Blank Rows by Sorting

Sorting involves rearranging a column or columns in ascending or descending order. You can also use sorting to remove blank rows from your dataset.

Follow these steps to sort your data and remove any empty rows.

1. Select the range containing the dataset without including the column labels.

2. Go to the Data menu.
3. Click on Sort range.
4. Select any of the sort options.

Whichever sort option you select, the blank rows will be at the bottom of the dataset, and you don’t need to delete them.

You can also use the Sort sheet option. If you use the Sort sheet option, you don’t have to select the dataset range. However, the column labels will be included in the sorted output.

You can easily correct this by moving the column labels back to the top.

Remove Blank Rows with an Add-on

This method requires the use of an add-on. Add-ons are custom tools created by developers that perform a variety of functions in the spreadsheet.

To remove blank rows, you will need to download the Power Tools add-on.

1. Go to the Extensions menu.
2. Select Add-ons.
3. Click on the Get add-ons option.

This will open the google workspace marketplace where you find and download add-ons. Use the search bar to search for the Power tools add-on and install it.

When the add-on is installed, you will find it in the Extensions menu ➜ Power Tools ➜ Start.

Clicking on Start will open the Power Tools dialogue menu on the right side of the spreadsheet.

Follow these steps to use the Power Tools add-on to remove blank rows.

1. Select the cell ranges with the blank rows.

2. Select the Clear option from the Power Tools menu.

3. Deselect any preselection leaving only the Remove all empty rows option selected.
4. Click on the Clear button.

This will remove all the blank rows in the selected range.

The Power Tools add-on is free, but the free version can only be used once a day after the 30 trial period.

You might look to subscribe for the full version if you intend to use it more frequently because it performs other reoccurring tasks asides from removing blank rows.

Remove Blank Rows with the FILTER Function

You previously saw how to use the filter tool to delete blank rows.

This method uses the FILTER function to do the same thing.

= FILTER ( range, condition1, [condition2, …] )

FILTER uses two arguments to keep unwanted data from view – range and condition.

• range refer to the cells containing the data to be filtered.
• condition refers to a column of true or false values for the filtering condition used to filter the dataset.

The range and only one condition argument are required arguments in the FILTER function although you can take as many condition arguments as needed.

= FILTER ( A1:D15, A1:A15 <> "" )

Copy and paste the above syntax into the formula bar to filter the data.

The syntax filters the data by the first column A. The filter condition is set so that it returns only rows that are not equal to blanks. The "<>" operator means not equal to.

This is a more convenient method to use because it doesn’t contain as many manual steps. The FILTER function is an array formula and will update accordingly if the source data changes.

Remove Blank Rows with the QUERY Function

The QUERY is another type of array formula that can be used to perform many operations in the spreadsheet as it allows for the use of structured query language (SQL) syntaxes to manipulate data in the spreadsheet.

This SQL syntax in the QUERY function can be used to remove blank rows.

💡 Tip: Check out this complete guide to the QUERY function for more information on this amazing function.

= QUERY ( data, query, [headers] )

The QUERY function has two required arguments and one optional argument.

• data points to the range of data that you want to query.
• query is the SQL syntax for the query you want to perform.
• headers is where you can specify the number of rows used as column headers. This argument is optional.

= QUERY ( A1:D15, "SELECT A, B, C, D WHERE A IS NOT NULL", -1 )

The above syntax will remove the blank rows in the sample data set.

The range containing the dataset is A1:D15. The query filters the dataset with a SELECT and WHERE clause from the SQL libraries.

The IS NOT NULL statement provides the condition for the WHERE clause to filter the selected columns.

The result is all your data without any blank rows.

Remove Blank Rows with an App Script

If you don’t want to use any of the methods described above to remove blank rows, there’s one other method you can opt for which is using an app script.

App scripts allow you to create custom tools to accomplish your tasks in Google Sheets.

As many tools as there are in Google Sheets, sometimes you will need something more nuanced for your data. The app script provides you with exactly what you need for such situations.

💡 Tip: Check out this full guide to app scripts in Google Sheets for more details.

You can open the app script code editor window by going to the Extensions menu and clicking on the Apps script option. Make sure you delete the contents in the editor window before pasting the code below.

function removeBlanks() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()  
var selectedRange = ss.getActiveRange();
selectedRange.sort({column: 1, ascending: true})
}
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Data')
.addItem('Remove Blanks', 'removeBlanks')
.addToUi();
};

Copy and paste the above code to your code editor window.

Click on the Save and then Run buttons. Grant the necessary permissions, then go back to your spreadsheet and reload it.

The script creates a new menu item called Data which has a submenu called Remove Blanks.

This is possible because of the onOpen trigger in the script that makes changes to the spreadsheet when opened.

The script works in a very straightforward way, building on the sorting method shown previously. You can select the range with the blank rows you want to remove and then click on Remove Blanks. The script will sort the range in ascending order using the first column.

⚠️ Warning: Make sure to not select the column headers before you run this script so they’re not included in the sort range.

Conclusions

It is best practice to remove any blank rows from your dataset since they don’t add any value.

There are many ways to remove blank rows from your data such as sorting, filtering, functions, add-ons, or app scripts.

For one-time uses, manual sorting, filtering, add-ons, or app script methods may be preferred.

When your source data is continually being updated you can use a formula method to remove the blanks. This way the results will update dynamically when your data source changes.

How do you remove blank rows from your data sets? Let me know in the comments below!