6

How to Delete All Empty Rows and Columns in Google Sheets

Posted by Robert Hayes on March 28, 2019

Google’s major office productivity suite, consisting mainly of Docs, Sheets, and Slides, rolled out in 2005 and 2006 and since then it has won the loyalty of millions of users worldwide. The Google suite offers a free but powerful cloud-based alternative to Microsoft’s Office suite of products. The three flagship apps don’t quite approach the power and functionality of Microsoft’s Office productivity suite, but for most users these three products represent a “good-enough” middle ground between other free Office replacements and Office itself. Perhaps the most powerful selling point for the Google apps is that you get the full power of a cloud-based solution behind the applications. Google’s suite was designed from the ground up with collaboration and cloud-based storage as integral parts of the design, whereas those features are very much afterthoughts for Microsoft’s products. This makes sharing, annotating, and collaborative editing with other users a snap using the Google products.

Google Sheets is the rough equivalent of Microsoft Excel and though it can’t directly compete feature-for-feature with Microsoft’s legacy application, Sheets holds its own as a powerful spreadsheet tool capable of balancing budgets, performing equations, and keeping track of data in real time. Many of Excel’s features are replicated or mirrored inside Sheets, making it easy to make the switch from Microsoft’s productivity suite to Google’s own offerings. Users with basic spreadsheets (those without custom macros or design elements) can in fact just directly import their Excel files into Sheets without any problems or glitches.

One problem that spreadsheet users have had since the beginning and probably always will have is that in the process of importing and collating data from multiple sources (one of the many tasks that spreadsheets are great at), it is not at all uncommon for random empty cells, rows, and columns to appear inside the document. Although this problem is trivially manageable in smaller sheets, where you can just delete the rows manually, it’s a huge problem when it crops up in larger documents.

However, removing these blank spaces is quick and easy if you know the proper steps. In this article, I will show you how to remove all the empty rows and columns in your Google Sheets document using an autofilter.

Setting Up an Autofilter

In order to remove those blank columns and rows, we have to start by understanding what an autofilter is inside of Google Sheets. Users new to Sheets or to spreadsheet applications in general might not understand how to use an autofilter to sort your data. Put simply, an autofilter takes the values inside your Excel columns and turns them into specific filters based on the contents of each cell—or in this case, the lack thereof. Though originally introduced in Excel 97, autofilters (and filters in general) have become a massive part of spreadsheet programs, despite the small minority of users who know about and use them.

What’s great about autofilters is that they can be used for a number of different sorting methods. In fact, they’re powerful enough to sort and push all of the empty cells to the bottom or top of your spreadsheet. Start by opening up the spreadsheet that contains empty rows and columns you want to remove from your document. Once the document has opened, add a new row at the very top of your spreadsheet. In the first cell (A1), type whatever name you’d like to use for your filter. This will be the header cell for the filter we’re about to create. After creating the new row, find the Filter icon in the command row inside Google Sheets. It’s pictured below; its general appearance is similar to an upside down triangle with a line running out the bottom, like a martini glass.

Clicking this button will create a filter, which will by default highlight a few of your cells in green on the left side of the panel. Because we want this filter to extend to the entirety of our document, click the small dropdown menu next to the filter icon. Here, you’ll see several options for changing your filters. At the top of the list, select “Create new filter view.”

Your Google Sheets panel will extend and turn a dark grey color, along with an entry point for you to insert the parameters of your filter. It’s not critical that you include every single column, but ensure that you’ve included every row and column in your document that contains blank spaces. To be safe, you can just have the filter cover the entirety of your document. To input this into your document, type something like A1:G45, where A1 is the starting cell and G45 is the ending cell. Every cell in between will be selected in your new filter.

Using the Autofilter to Move Blank Cells

This next bit may seem a bit odd, because it will be moving and reorganizing your data in a way that seems counterintuitive at best and destructive at worst. Once your filter has been selected, click the green triple-line icon in the A1 column of your spreadsheet where you set a title earlier. Select “Sort A-Z” from this menu. You’ll see your data move into alphabetical order, beginning with numbers and followed by letters.

The blank spaces, meanwhile, will be pushed to the bottom of your spreadsheet. Continue to resort your spreadsheet column by column until your blank cells have moved to the bottom of the display and you have one solid block of data displayed at the top of Google Sheets. This will likely make your data a confusing, unreadable mess—don’t worry, this will all work out in the end.

Deleting Your Blank Cells

Once your blank cells have been moved to the bottom of your spreadsheet, deleting them is as simple as deleting any other cell. Use your mouse to highlight and select the blank cells on your spreadsheet that have been moved to the bottom of the document. Depending on the amount of blank cells and the working area of your spreadsheet, you might want to zoom out of your display a bit to see more of the surrounding area (most browsers, including Chrome, allow you to zoom by using Ctrl/Cmd and the + and – buttons; you can also hold down Ctrl/Cmd and use the scroll wheel on your mouse or touchpad). Click and hold to select the surrounding blank cells and drag your mouse across every cell.

After selecting the offending cells, right click anywhere in the highlighted areas. You’ll see a pop-up menu containing several different options for your cells, including cut, copy, and paste, and the ability to insert comments and notes. About midway through the menu are the options for deletion, including deleting rows, columns, and deleting cells. Because all of your blank cells have been sorted into rows, the easiest method is to select row deletion, which will remove all content from your selected blank rows. You can also delete specific cells by choosing a shift method; since your blank cells are in one block, your shift method doesn’t matter.

Reorganizing Your Spreadsheet

Now that you’ve removed the offending blank cells, you can reorganize your spreadsheet back into a normal order. While clicking on that same triple-lined menu button from earlier inside the filter will only allow you to organize in alphabetical or reverse-alphabetical order, there’s another sort option: turning your autofilter off. To do this, click the triangle menu button next to the autofilter icon inside Sheets. Inside this menu, you’ll see an option for your filter (called “Filter 1,” or whatever number filter you’ve made), as well as an option for “None.” To turn off the filter you applied earlier, simply select “None” from this menu. Your spreadsheet will return to normal like magic but without the blank cells you deleted earlier.

With the cells deleted, you can resume reorganizing and adding data back into your spreadsheet. If, for whatever reason, this method causes your data to fall out of order, reversing it is as simple as diving into your documents’ history and reverting to an earlier copy. You can also use the copy and paste function to move your data around easily, without having to deal with hundreds of blank cells blocking your path. This isn’t a perfect solution—Sheets doesn’t make deleting blank cells easy in general—but it does work as a quick and dirty way to push your data above the mass of blank cells in your document. And at the end of the day, it’s a lot easier than mass-deleting rows one by one.

Let us know your favorite method in the comments below for deleting blank cells from Google Sheets!

Thanks to commenter Martin for letting us know about this tip.

We have a lot of other articles for Google Sheets users, such as our tutorial on how to filter on the basis of cell color, how to hide cells, how to lock cells, how to utilize pivot tables, and how to swap columns.

6 thoughts on “How to Delete All Empty Rows and Columns in Google Sheets”

WHITNEY WETHERILL says:
Say you have data in a checkerboard pattern and you need to remove all the empty cells. (By checkerboard, I mean there is data in A1, A3, A5, A7 … and there is data in row 1, row 3, row 5, row 7 …).
(FIRST: make a copy of the sheet you’re working on just in case…)
1: Insert a column on the left. in the new A1 cell, enter a formula that looks at the entire row and if it finds any value in any cell in the row, it writes “x” in the cell. Copy the formula down the entire column of the spreadsheet, then copy/paste special – values only to get rid of the formulas.
2: Sort the sheet on column A. (now your empty rows should be gone).
3: Select and then copy the entire spreadsheet.
4: Paste Special by putting the cursor in A1 and clicking Paste Transposed (in the Edit menu, under Paste Special). Now you have empty rows but no empty columns (your data is reversed but not incorrect).
5: Repeat Steps 1 through 4.
I hope I’ve written this correctly, I just did it and it worked.
Reply
SorinS says:
If you want to delete empty rows below your data:
1. Select the first empty row below the last row that contains data. (By clicking on the number label).
2. Press Ctrl+Shift+DownArrow (This selects all the rows down to the last row).
3. Right click on the selected row labels
4. Select “Delete rows … – …”
That’s it! Hope this has helped you.
Reply
Michelle says:
This! Thank you! I just deleted empty rows 132-1000!
Reply
Michael says:
So why not do this filter bit, as suggested, and then choose ‘cell is empty’. Then delete a heap of rows, turn off the filter et voila, the blank cells are gone.
Reply
Martin says:
Not the most helpful post unfortunately – bordering on clickbait.

Not sure if this helps others but, to do this quickly and semi-manually (i.e. without Macros), you can potentially use the autofilter setting to quickly move the blank rows to the bottom end of a data set. Say you have a long single column of entries with some cells blank, some cells full and you want to remove the empty cells so all the data is in one block:

1) insert a row above the data set and, in the cell above the data, enter a title (this will be the header cell for the filter)

2) click the filter button (in Google sheets it looks like a triangle with a flat side at the top and a little line out the bottom)

3) sort by A to Z

4) all your blanks cells will pop to the bottom and you have one block of data

With a data set of a number of columns, you can select and filter the entire data set to remove the blanks and then apply filters again to reorganise the data without the blank cells.

Hope that helps.

Reply
Paul says:
Why did you make this useless article? I have over 300,000 rows. I do not want to delete the empty ones MANUALLY.

What’s next, a tutorial on how to breathe without falling over?

Reply
Vince says:
The reason I Googled this problem is because I didn’t want to do it manually, as I have a LOT of alternating empty rows. This still only explains how to do it manually… 🙁
Reply
carlos victor says:
hahaha Exactly my case.
Reply
Glenda Ward says:
Same here. I have a sheet using 17 rows and there are 12 empty pages starting on row 18. Manually deleting them is ridiculous.

Leave a Reply

Your email address will not be published. Required fields are marked *


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.