by Blank rows and cells in Google Sheets aren't just an eyesore — they can also cause calculation errors and data inconsistencies. In this guide, we'll explore several easy ways to delete empty rows and cells so you have accurate and reliable data. No coding or Apps Script, promise 😉
Tip. Grab yourself a copy of the spreadsheet at the end of the article to practice along.
Delete empty rows in Google Sheets without any tools (manually)
Sometimes picking out those blanks manually is the best. Especially if you have a small data set.
Here's how you remove empty rows in Google Sheets by hand:
- Select each blank row. Keep the Ctrl key (or Cmd for Mac) on your keyboard pressed while scrolling through your data and clicking on the row numbers of every empty row:
- Delete blank rows. Right-click the last selected row and pick Delete selected rows from the context menu:
These simple steps are perfect for deleting a few empty Google Sheets rows without any tools. Doing this regularly will prevent the clutter of blanks making your data easy to manage.
However, it'll be cumbersome for larger tables. So what are other options?
Use filters in Google Sheets to delete empty rows
Filters will help you remove blank rows in Google Sheets much easier, especially in large tables. And there are actually two different ways to do that depending whether you want to permanently delete those empty rows or just hide them from view.
Remove empty rows temporarily (filter from the view)
This way doesn't really deletes blank rows but temporarily hides them from view making your data easier to work with without permanently altering the structure of your spreadsheet.
- Enable a filter. Select your entire data range and click the Create a filter icon.
Note. Selecting the range beforehand manually is important. If you click on a single cell within your data and enable the filter, Google Sheets will apply the filter to a portion of your table stopping at the first empty row or column. Manually selecting the range, you will ensure that the filter will cover your entire data set.
This will add filter drop-downs to your column headers:
- Filter out empty rows. Click one of the drop-downs and select Filter by condition > Is not empty > OK:
This will remove all blank rows from view displaying only rows with data:
Delete blank rows permanently
This way lets you quickly see and permanently remove all empty rows in Google Sheets, ensuring a clean and organized dataset.
- Enable a filter. Select your entire data range and click the Create a filter icon.
Note. As I mentioned earlier, selecting the range manually ensures that the filter covers your entire dataset.
Filter drop-downs will appear next to your column headers:
- Filter by empty rows. Click one of those filters in any of the column headers and choose Filter by condition > Is empty, and click OK:
This will filter out all non-empty rows, showing only the blank rows in your dataset:
- Select and delete blank rows. Now that only the empty rows are visible, select them all by clicking on their row numbers. Then right-click any of them and pick Delete selected rows from the context menu:
- Disable the filter. Click the filter icon on the Google Sheets toolbar once again to turn it off. You'll see your table without any blanks:
Whether you choose to permanently delete or temporarily hide empty rows from view, filters help you quickly identify them all.
But if you're looking for a more automated option that would make Google Sheets remove empty rows for you in a click, I have a perfect tool in store.
Make Google Sheets remove blank rows for you automatically
There's a special tool in Google Sheets that deletes empty rows in just one click. I'm talking about Power Tools extension from Ablebits.
It's a collection of 40+ tools for daily tasks, one of them being the Remove Blanks tool. It removes all empty rows on one or multiple Google sheets in just one click. No need to filter or select anything, really. Let me show how it works:
- Install Power Tools. If you haven't already, install Power Tools from the Google Workspace Marketplace. The installation is completely free, and you'll have 30 days of the fully functional trial to try this and other tools out. Believe me, they're worth trying 😉
- Select Remove empty rows option. Once installed, go to the following Google Sheets menu: Extensions > Power Tools > Quick actions > Remove blanks > Remove all empty rows:
All blank rows will be deleted from your Google Sheets in a matter of seconds:
Here's why you should definitely check Remove Blanks from Power Tools: Tip. Unused rows and columns are those blanks that go after your data ends. Removing them helps maintain your spreadsheet within the limit of 10 million cells. It can even shift data to fill in the blank cells! I'll demonstrate it later in the article.
This will delete all blank rows from all tabs in your Google spreadsheet:
Tip. Unused rows and columns are those blanks that go after your data ends. Removing them helps maintain your spreadsheet within the limit of 10 million cells. Whatever combo of blanks you removed, Power Tools lets you save them from Recent tools to your Favorite tools for even quicker access:
All these pros make Power Tools an indispensable extension for Google Sheets when it comes to deleting empty rows and columns.
Benefits of the Remove Blanks tool from Power Tools
How to delete empty rows in Google Sheets using formulas
If you're up to a small challenge, Google Sheets offers a couple of functions that help remove blank rows: FILTER and QUERY. They return your data to another place ignoring empty rows from the original range.
Use Google Sheets FILTER to delete empty rows
The FILTER function will return a filtered version of your original range with the rows that meet specified criteria — no blanks.
Tip. We have a full guide on the FILTER function with its basics and nuances. While here, I'll focus specifically on removing blank rows.
Create a new sheet and put the following formula there:
=FILTER(Sheet1!A1:D20,Sheet1!A1:A20<>"",Sheet1!B1:B20<>"",Sheet1!C1:C20<>"",Sheet1!D1:D20<>"")
- The formula takes my original table — Sheet1A1:D20
- And takes only those rows where columns A, B, C and D are non-blanks (not equal to empty cells) — Sheet1!A1:A20<>"" and Sheet1!B1:B20<>"" etc.
Use Google Sheets QUERY to remove blank rows
Basically, the QUERY function does the same as FILTER. It's just another powerful function unique to Google Sheets. The formula will differ though since QUERY uses a special language similar to SQL.
Tip. You will find the complete tutorial devoted to QUERY here.
Add a new sheet and enter the following QUERY formula:
=QUERY(Sheet1!A1:D20,"select * where A is not null AND B is not null AND C is not null AND D is not null",1)
- again, just like FILTER, this formula takes my original data — Sheet1!A1:D20
- and returns all columns — select *
- where there are no blank rows — where A is not null AND B is not null AND C is not null AND D is not null
- 1 is the number of header rows
All-in-all, formulas can display filtered data, but they don't actually remove blank rows. And if you've set up the formula correctly from the first try, it may require updates if your data changes. So I highly recommend the Remove Blanks from Power Tools described above for easier clean-ups.
How to remove blank cells in Google Sheets
Removing blank cells in Google Sheets can be a bit more complex than removing entire rows.
Sure, you could always scan the range with your own eyes, select those blanks, right-click them and pick Delete cells and shift up/left:
But you'll have to do this with each group of neighbouring cells, one by one, hoping you don't miss anything.
Luckily, there's a better solution. The Power Tools collection offers a feature called Remove blanks & shift data.
It scans your selected range in Google Sheets, finds all blank cells and removes them all at once shifting values up or/and left.
You will find it right under the options to remove empty rows & columns in the Clear toolset:
You just tick the boxes and click Run. The add-on does the rest ensuring that your table remains organized:
Replace blank cells with zero in Google Sheets — all in one go
Sometimes you need to replace all blank cells with zeros in Google Sheets instead of deleting them. Otherwise those gaps will disrupt your calculations and you'll only waste time analysing all the data.
Well, here comes Power Tools again 🙂 It offers a powerful add-on called Fill Blank Cells. It replaces empty cells with zeros or any custom value in just a few clicks. Even from multiple selected ranges 😎
While Find and replace feature in Google Sheets can also be used to replace blank cells, this utility from Power Tools offers several advantages:
- Targeted replacement. Fill Blank Cells was specifically designed to target blank cells within a selected range. No more searches, no affected non-blanks.
- Flexibility. It lets you not just replace with a specified value, but also fill with neighboring records or even formulas.
- Easy to use. A single tool with as few manual steps as possible. You can even add it to your favorite actions and run in a click.
Here's how you replace blank cells with 0 using the tool:
- Select your data.
- Open Power Tools. Extensions > Power Tools > Start > Process group > Fill blank cells:
- Set up the replacement. Select the option Fill with a custom value and enter 0:
- Click Fill to apply the changes.
Tip. Feel free to look through this guide to see how other options work. Or install Power Tools and try it on your data right away.
Video: how to delete empty rows in Google Sheets
Now you know several simple ways to delete empty rows & cells in Google Sheets. Whether you prefer manual work, filters, formulas or specialized tools, I invite you to try them all out and see which works best for you!
Practice spreadsheet
Remove blank rows and cells in Google Sheets (make yourself a copy)
