How to Delete Empty Rows in Google Sheets (3 Easy Ways) - Spreadsheet Point (2024)

Let’s talk about how delete empty rows in Google Sheets. It’s a valuable skill. Sometimes, you’ll have lots of empty cells and want to clean up the data quickly. In that case, you’ll want to delete empty rows all at once. When there are only a few, I could select and delete them manually. But what about thousands of empty rows? In this tutorial, I will show you a few powerful ways to delete empty rows all at once.

For the quickest solution, check out my Google Sheets “Delete Empty Rows” video. It’s available below, and I also have it posted on YouTube. Otherwise, read on for a detailed guide, with screenshots, on how to delete empty rows.

Filter Empty Rows and Delete Them

Suppose you have a dataset in one of your spreadsheets as shown below. Note that there are multiple blank rows in it. Looks messy, right? Let’s delete empty rows in the spreadsheet, so we can clean it up. That would remove blanks in rows 6, 10, and others.

You can filter the data set based on many criteria – and to be able to filter it when cells are blank is one of them. That’s helpful. Once you filter all the empty rows, you can then select and delete all of these (while the other rows remain unaffected). And, best of all, you don’t need to use a Google Apps Script. The best way to delete rows in Google Sheets is with a filter. The process doesn’t take more than a few minutes, and it’s the same on Mac and PC.

Here’s how to filter empty rows and then delete them.

1. Select the entire dataset. Note that you need to select all the cells in the dataset (even if these are blank)
2. Click on the Data tab
3. Click on ‘Create a Filter’ option. This will add filters to the headers of the dataset
4. Click on the filter icon in any of the columns (it’s the small inverted pyramid icon at the right of the header cell).
5. In the drop-down, click on Clear. This deselects every option.
6. Manually select the Blank option
7. Click OK

When you’re done with these above steps, it will give you a filtered data set of all the empty rows only. All the rows that have data will be hidden.

Now that you have all the empty rows together. you can select all these and delete at one go.

Below are the steps to do this:

1. Click on the column number in the left of the row.
2. Hold the shift key and then click on the last empty row in your data set. This will select all the empty rows in the dataset.
3. Right-click on any of the select column number
4. Click on Delete Rows 6- 14 (in your case it will show the number of your rows).

Don’t worry, the above steps would only delete the empty rows in the data set and not all the rows.

Now that this is done, remove the filter and you will have a dataset where empty rows have been deleted. To remove the filter, click the Data tab and then click on ‘Turn off filter’

Sort the Data Set and Delete Empty Rows

While the above method of filtering data and deleting empty rows is what I prefer, there are multiple ways to skin this cat.

Another almost equally good method is by sorting the data set, and when you have all the empty rows together, you can select and delete those.

Below are the steps to sort the dataset and delete empty rows:

1. Select the entire dataset. Note that you need to select all the cells in the dataset (even if these are blank)
2. Click on the Data tab
3. Click on Sort range by Column A, Z → A

The above steps would sort the entire data set and cluster all the empty rows at the bottom of the dataset.

Now that you have all the empty rows together. you can select all these and delete at one go.

Below are the steps to do this:

1. Click on the column number in the left of the row.
2. Hold the shift key and then click on the last empty row in your data set. This will select all the empty rows in the dataset.
3. Right-click on any of the select column number
4. Click on Delete Rows 12-15 (in your case it will show the number of your rows).

While this method works great, there is a reason I said it’s almost as good as the Filter method (and not equally good or better).

Below are a few things to keep in mind when using this method:

1. When you’re done with deleting empty rows, you’re left with a sorted dataset. This is great if you wanted to sort the data anyway, but if you want to go back to the original dataset order, you can’t do that. While there is a long way to do this (by adding a column with serial number and sorting it to restore to the original order), it’s better to just filter the data and delete empty rows.
2. You need to sort data Z → A and not A → Z. If you sort the data A → Z, your data headers might also get sorted you end up somewhere in the middle or end of the dataset

Using an Add-on

While the above two methods don’t take a lot of time and are easy to do, if you need a more hands-off approach, you can use some Google Sheets add-ons to get this done.

An add-on is a code that someone has already created with a specific purpose (to delete blank rows in our example). So all you need to do is install this add-on, and whenever you want to delete empty rows, just use the add-on (which usually provides an easy interface to get the work done – more like a click of a button).

Below are the steps to install an add-on that remove empty rows in Google Sheets:

1. Click the Add-ons option in the menu.
2. Click on Get add-ons…
3. In the dialog box that opens, enter ‘delete empty rows’ in the search field on the top-right and hit Enter. This will show you a list of add-ons in Google Sheets that are related to your search query.
4. Click on the ‘FREE’ button for the ‘Remove Blank Rows (and more!)’ add-on. You can try out others as well, but in this tutorial, I will be using this add-on
5. Google Sheets may ask you to sign in. Use your Google account to do this.
6. It may also ask you to allow this add-on to access your account. This is fairly standard and this is a safe-to-use add-on. Click on Allow

Once you do all these steps, this will add the add-on to remove empty rows/columns in your Google Sheets.

Below are the steps to use this add-on to remove empty sets from your dataset:

1. Select the entire dataset
2. Click on Add-on option in the menu
3. Hover the cursor over ‘Remove Blank Rows (and more!)’ option
4. Click on ‘Delete/Hide blank rows/columns’ option
5. In the ‘Remove Blank Rows’ pane that opens, ensure “All row cells must be blank” is selected (reason covered a few paragraphs below)
6. In ‘More options’, make sure ‘Remove Blank Rows’ is selected
7. Click Delete

Based on your dataset, it may take a few seconds to remove all the blank rows.

Note: The reason asked you to make sure “All row cells must be blank” is selected as there is a possibility that you have some data in the same row, but that data is not part of your dataset. This option ensures that only those rows are deleted that are completely empty (not just in the dataset but in the entire worksheet).

When you add an add-on in Google Sheets, it becomes available for use in all the other Google Sheets document that you use. This is account dependent, so any Google Sheets you create or work on with the same account that you used to add the add-on, you will be able to use the add-on. This is a huge time saver.

How to delete empty columns in Google Sheets?

While it’s pretty simple to delete empty rows in Google Sheets, the same solution doesn’t apply to columns. Both filtering the data and sorting the data method works for rows, but you can not do it for columns. That means the next best solution is using the script editor.

The ‘Remove Blank Rows (and more!)’ add-on allows you to delete empty columns, but it does this for the entire dataset and not just selected dataset. If the add-on doesn’t work for you, you can also consider creating your own Google App Script that loops through cells in the dataset and deletes columns in the data set only. Note that Google also has some answers on columns.

Concluding Thoughts on Removing Empty Rows

It’s super helpful to clean up data by removing empty columns in Google Sheets. Above, I showed you the best way to do it. I shared a video on how to remove empty rows, and I included a step-by-step walkthrough with screenshots of the process. I hope this helped!

You may also like the following Google Sheets tutorials:

• How to Indent Text in Google Sheets
• How to Transpose Data in Google Sheets
• How to Filter By Color in Google Sheets
• How to Color Alternate Rows in Google Sheets
• How to Insert Multiple Rows in Google Sheets
• How to Hide Columns in Google Sheets
• How To Remove Duplicates In Google Sheets
• How to Unhide Rows in Google Sheets
• How To Delete Multiple Sheets In Google Sheets