Want to learn how to delete empty rows in Google Sheets? It’s a valuable skill that I use regularly, and luckily, it’s easy to learn, especially as I have highlighted all the methods in this Google Sheets Delete Empty Rows guide.
Table of Contents
The Best Way to Delete Empty Rows in Google Sheets
To delete empty rows in Google Sheets, you can select each empty row by holding down CTRL (or CMD on macOS)ย and clicking on the row. Then, you can right-click on the selected rows and choose “Delete Rows.”
However, this works best when there are only a few empty rows in your data set. But what about thousands of empty rows? In this case, Iโd be more inclined to use a filter. And can Google Sheets delete empty rows in any other way?
Of course! Below, I will show you a few powerful ways to delete empty rows simultaneously. And on a related note, check out my guide to removing duplicates.
How To Manually Remove Empty Rows
If I donโt have a large dataset or too many empty rows, I would remove them manually by selecting and deleting the empty rows.
Follow the steps below to learn how to remove empty rows from Google Sheets manually:
- Hold down the CTRL button for Windows (or CMD for Mac).
- Select each empty row.
- Right-click on the selected rows to open the right-click menu.
- Choose the option โDelete rows.โ
While this is the simplest method for deleting extra rows in Google Sheets, it also requires a lot of effort if you have a large dataset because you need to select all the empty rows.
In such cases, if I have a large dataset, I would use another method highlighted below.
How To Filter and Delete: Google Sheets Delete Empty Rowsย
Suppose you have a dataset in one of your spreadsheets, as shown below, with multiple blank rows. Looks messy, right?
Let’s delete empty rows in the spreadsheet so we can clean it up by removing the blank rows 6, 10, 11, and 14. 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.
Additionally, you can filter the data set based on many criteria when cells are blank, which is helpful. Furthermore, once you filter all the empty rows, you can select and delete them (while the other rows remain unaffected).
And, best of all, you don’t need to use a Google Apps Script! Note: This process differs from my method for highlighting duplicates.
Follow the steps below to learn how to filter and delete empty rows.
- Select the entire dataset.
- You need to select all the cells in the dataset (including the blank ones).
- Click on the โDataโ tab.
- Click on the โCreate a Filterโ option.
- This will add filters to the headers of the dataset.
- This will add filters to the headers of the dataset.
- Click on the filter icon in any of the columns (small inverted pyramid icon at the right of the header cell).
- In the drop-down, click on โClearโ to deselect every option.
- ย Manually select the โBlankโ option.
- Click โOKโ to confirm your choice.
When you finish the above steps, you will have a filtered dataset of all the empty rows, while all the rows with data will be hidden.
Alternatively, you can use the method below to remove blank rows in Google Sheets.
- Click on the column number on the left of the row.
- Hold the Shift key (โง) and click on the last empty row in your data set.
- This will select all the empty rows in the dataset.
- This will select all the empty rows in the dataset.
- Right-click on any of the selected column numbers.
- Click on โDelete Rows 6- 14โ
- Iin your case, it will show the number of your rows.
- Iin your case, it will show the number of your rows.
The above steps will only delete the empty rows in the data set and not all the rows with data.
After this is done, remove the filter, and you will have a dataset where empty rows have been deleted.
NOTE: To remove the filter, click the โDataโ tab and then click on โTurn off the filter.โ
How To Sort and Delete: Google Sheets Delete Empty Rows
While the above method of filtering data and deleting empty rows is what I prefer, there are multiple ways to skin a cat.
Another equally good method is sorting the data set; when you have all the empty rows together, you can select and delete those.
Below are the steps for how to delete blank rows in Google Sheets by sortingย the dataset:
- Select the entire dataset.
- You must select all the cells in the dataset, including the blank ones.
- Click on the โDataโ tab.
- Click the โSort range by Column A, Z โ Aโ option.
Once completed, this will sort your entire data set and cluster all the empty rows at the bottom of the dataset. You can then have Google Sheets delete empty rows at the bottom.
Now that you have all the empty rows together, you can select all of these and delete them in one go.
Below are the steps showing how to remove blank cells in Google Sheets:
- Click on the column number on the left of the row.
- 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.
- Right-click on any of the selected row headers.
- 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:
- When you’re done with deleting empty rows, you’re left with a sorted dataset. This is great if you want to sort the data anyway, but you can’t do that if you want to return to the original dataset order.
- While there is a long way to do this (by adding a column with a serial number and sorting it to restore to the original order), it’s better to filter the data and delete empty rows.
- You need to sort the data from Z โ A and not A โ Z.
- If you sort the data A โ Z, your data headers might also get sorted, and you end up somewhere in the middle or end of the dataset.
Deleting Empty Rows in Google Sheets: Video
To learn the quickest way to delete rows using Google Sheets, check out my Google Sheets “Delete Empty Rows” video above. It’s available below, and I also have it posted on YouTube.
Alternatively, keep reading my detailed guide with screenshots and step-by-step instructions.
Removing Empty Cells with an Add-on
While the above two methods don’t take much 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 someone has 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, 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 removes empty rows in Google Sheets:
- Click the โAdd-onsโ option in the menu.
- Click on โGet add-onsโฆโ
- In the open dialog box, 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 related to your search query.
- This will show you a list of add-ons in Google Sheets related to your search query.
- Click on the โFREEโ button for the โRemove Blank Rows (and more!)โ add-on.
- You can also try out others, but for this tutorial, I will be using this particular add-on.
- You can also try out others, but for this tutorial, I will be using this particular add-on.
- Sign in with your account details if you havenโt already signed into your Google account.
- It may also ask you to allow this add-on to access your account.
- This is fairly standard and a safe-to-use add-on. Click on โAllow.โ
- This is fairly standard and a safe-to-use add-on. Click on โAllow.โ
Once you do all these steps, you will have added the add-on to your account to remove empty rows or columns in Google Sheets.
Add-On Row Removal Steps
There will also be occasions when you need to remove an add-on. Luckily, the process is quick and easy. Just follow the steps below and use this add-on to remove empty sets from your dataset method:
- Select the entire dataset.
- Click on the โAdd-onโ option in the menu.
- Hover the cursor overย the โRemove Blank Rowsย (and more!)โ option.
- Click on the โDelete/hide blank rows/columnsโ option.
- In the โRemove Blank Rowsโ pane that opens,
- Ensure โAll row cells must be blankโ is selected (reason covered a few paragraphs below).
- In โMore options,โ make sure you select โRemove Blank Rows.โ
- Click โDelete.โ
Removing all the blank rows may take a few seconds, depending on your dataset.
Note: I asked you to select โAll row cells must be blankโ because you may have some data in the same row, but that data is not part of your dataset.
This option ensures that only empty rows are deleted (not just in the dataset but in the entire worksheet).
When you add an add-on to Google Sheets, it becomes available for use in all the other Google Sheets documents that you use.
This is account-dependent, so any Google Sheets you create or work on with the same account you used to add the add-on will be able to use the add-on. This is a huge time saver; onceย you have added it, you will see how much more efficient your workflow is.
Frequently Asked Questions
Before I wrap up, I wanted to answer any remaining questions about clearing excess space in your spreadsheet. I’ll do my best to answer the most common questions here. If I miss anything, please ask in the comments!
Why Delete Rows in a Spreadsheet?
In many cases, it’s just a personal preference to delete extraneous rows. Extra rows and columns also mean you’ll have to scroll further to get to the end of the sheet.
You can also delete extra rows in Google Sheets to ensure your formulasย display error messages if you accidentally list blank cells as arguments.
What’s the Fastest Way to Delete Empty Rows?
If you’ve clumped all your empty rows together, the fastest way is to click the top-most empty row, then hold Shift (โง) and the down arrow (โ). That selects all the empty rows.
You can right-click on this selection and click โDelete.โ Unfortunately, there isnโt any Google Sheets delete empty rows shortcut.
How Do You Delete Empty Columns in Google Sheets?
While deleting empty rows in Google Sheets is pretty simple, the same doesn’t apply to deleting columns. Both filtering the data and sorting the data method work for rows, but you can not do it for columns, in which case the next best solution is to useย 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, not just the selected one. 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.
If you want additional information about columns in Google Sheets, head to the Help page.
Wrapping Up the Google Sheets Delete Empty Rows Guide
It’s super helpful to clean up data by having Google Sheets delete empty rows. Above, I showed you the best methods available, along with my video on removing empty rows. I hope my step-by-step Google Sheets delete empty rows guide has clarified some questions and improved your workflow.
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 Unhide Rows in Google Sheets
- How To Delete Multiple Sheets In Google Sheets