When you use Google Sheets, you often encounter cells and rows that have no information in them. This happens because you might have received data that includes empty rows, or you might have removed some cells or rows, leaving gaps.
If there are just a few empty rows, it’s not too difficult to delete them one by one. But when you have lots of these empty rows spread throughout your data, doing it manually becomes very time-consuming.
In this beginner-friendly guide, I’ll teach you some easy methods to delete empty rows in Google Sheets at once.
But before we jump into the practical aspect of today’s guide, showing you how to delete empty rows in Google Sheets, let’s quickly look at a scenario to help you understand the topic better.
Scenario/Use Case
Imagine you’re a sales manager at a small retail company, and you use Google Sheets to keep track of your monthly sales data. Each month, you receive a report from your sales team, which includes details of all the products sold, their prices, and the quantities sold.
However, over time, the spreadsheet has become a bit messy. Some sales reps forget to fill in certain rows, leaving them completely empty. Additionally, during your last review, you decided to remove a few product lines from the report because they were no longer relevant.
Now, you’re left with a spreadsheet that’s cluttered with empty rows. Manually going through the entire sheet to delete these rows seems like an endless and inefficient task, especially when you have hundreds of products listed.
Now, let’s dive into solving this problem with some easy methods. These methods will help you delete all those unwanted empty rows in Google Sheets with ease. Let’s get started.
Filter Empty Rows In Google Sheets and Delete It
Did you know that you can easily delete empty rows in Google Sheets? It’s as simple as using a filter to find those empty rows and then deleting them. The best part is that this process is very straightforward.
In this section, we’ll guide you through the steps to filter your data and then delete the empty rows.
To make things even more straightforward, we’ll use the following sample data to show you how to delete rows in Google Sheets.
Copy Sample Data
If you want to practice the steps we’re about to explain, you can copy our sample data and follow along.
If you already have your own data in a Google Sheet, don’t worry; you can still follow the steps we’ll demonstrate.
Step 1: Highlight the Data
To delete rows in Google Sheets using the filter option, select the data in your spreadsheet.
Begin by clicking and dragging your mouse to highlight the specific rows you want to work with in your Google Sheets document.
You can select an entire row by clicking the row number on the left side, or you can choose specific cells by clicking and dragging to form a selection box around them.
Once you’ve highlighted the data, you’re ready to move on to the next steps for deleting empty rows.
Step 2: Data > Create A Filter
With the data in your spreadsheet now highlighted, it’s time to create a filter. To do that, navigate to the Data menu and select the option for Create a filter.
If you followed the steps exactly as we showed you above, Google Sheets should instantly add a filter icon next to every header in your spreadsheet.
Take a look at what our spreadsheet looks like after selecting the option for Create a filter.
Step 3: Filter The Data
Now that we’ve set up a filter for our data, let’s learn how to use it to refine our data based on specific conditions. Here’s how you can do it.
Begin by finding the filter icon next to the “Quantity” column header. Clicking on this icon will open up a menu with various filter options that you can customize to suit your needs.
Step 4: Select a Filter Criterion
Having clicked on the filter icon and seen all the different filter choices, it’s time to pick the one that matches what you want to do. Since our main aim in this guide is to delete empty rows in Google Sheets, follow these steps:
Scroll down through the filter options until you find them all. Then, you want to uncheck everything except for the “Blanks” option. Make sure “Blanks” is the only one that remains selected. This will help us focus on those empty rows we want to delete.
With your preferred filter criteria selected, your spreadsheet should be instantly updated to only show blank cells.
Here, take a look at what ours looks like:
Step 5: Delete Empty Rows In Google
Now that we have been able to group all the empty rows together, you can delete them all at once. Here’s how:
Start by selecting the blank cell in your spreadsheet.
Once you have all the empty rows selected, right-click on any of the selected row numbers, and from the menu that appears, choose “Delete selected rows.” This will remove all the selected empty rows from your Google Sheets document in one go.
If you followed the steps precisely as we detailed above, you should have something like this:
Notice how we have deleted all the blank cells? Well, that’s how to delete empty rows in Google Sheets easily.
Step 6: Remove Filter
Having deleted the empty rows, it’s time to get rid of the filter so you can see your cleaned-up dataset. Here’s how to do it:
Go to the “Data” tab at the top of your Google Sheets window. Once you’re on the “Data” tab, look for and click on the option that says “Remove filter.” This action will clear the filter, and you’ll see your dataset with the empty rows now removed.
After removing the filter, you should have a spreadsheet that looks like this
From the screenshot above, you can see that we have successfully deleted empty rows from Google Sheets using the filter option. That wasn’t too hard, was it?
Sort and Delete Empty Rows In Google Sheets
While the method we just discussed for filtering and deleting empty rows is our preferred approach, there are other ways to achieve the same result. Here’s another method that works almost as well:
You can sort your dataset, and when all the empty rows are grouped together, you can select and delete them.
To show you how to delete empty rows in Google Sheets using the sort method, we will use the same sample data we used for the previous example.
Step 1: Highlight the Data
The first thing we need to do is select the data in our spreadsheet. To do this, just follow these simple steps:
Use your mouse to click and drag it across the data you want to work with in your Google Sheets. As you do this, Google Sheets will automatically highlight the selected area.
Step 2: Data > Create a filter
With our data now highlighted, let’s quickly create a filter for it. To do that, navigate to the Data menu and select the option to Create a filter.
Once we select the option for Create a filter, Google Sheets will automatically add a filter icon next to every header in our spreadsheet.
See the image below to see what ours looks like:
Step 3: Select Sort Criteria
Now that we’ve added filter icons to the headings in our spreadsheet, let’s choose how we want to organize our data. Here’s how to do it step by step:
To start, click on the filter icon next to the “Quantity” column. When you do this, Google Sheets will show you various options for how to sort your data.
Step 4: Sort Z to A
As mentioned before, when you click on the filter icon next to the “Quantity” column, you’ll see several choices. From these options, pick the one that says “Sort Z to A.”
With that option chosen, Google Sheets will automatically sort the data in our spreadsheet to look like this:
Notice how the empty rows have been sorted to appear at the bottom of our spreadsheet? That’s how easy it is to sort data using the method we described above.
Step 5: Delete Empty rows
Now that you’ve gathered all the empty rows in one place, you can select and delete them all at once. Here’s how:
- Click on any row number within the group of empty rows. This will highlight the entire row.
- If there are more empty rows below, hold down the Shift key on your keyboard and click on the row number of the last empty row in the group. This will select all the rows in between as well.
- Once you have all the empty rows selected, right-click on any of the selected row numbers. From the menu that appears, choose “Delete rows.” This action will remove all the selected empty rows from your Google Sheets document in one go.
If you followed the steps exactly as we showed you, Google Sheets will automatically delete empty rows, leaving only rows that contain data.
Here, check out what our spreadsheet looks like:
So, that’s how you delete empty rows in Google Sheets using the sorting method. It wasn’t too hard, was it?
Final Thoughts
Deleting empty rows in Google Sheets can be a straightforward process, especially when you have a cluttered spreadsheet with data gaps. In this guide, we explored two methods to help you tidy up your data effectively: using filters and sorting.
Using filters, you can easily identify and delete empty rows. By selecting the filter icon, sorting by “Blanks,” and then removing them, you can quickly streamline your dataset.
Alternatively, you can achieve the same result by sorting your data, making the empty rows appear together, and then deleting them in one go.
Whichever method you choose, the goal remains the same: to clean up your Google Sheets and have a well-organized dataset that makes your work more efficient and manageable.
So, the next time you find yourself dealing with unwanted empty rows in Google Sheets, remember these methods. With a few simple clicks, you’ll be able to delete empty rows in Google Sheets and maintain a tidy and useful spreadsheet for your projects.