Google sheets allow you to organize and analyze data via spreadsheets. A key point in filtering data is to delete empty rows and columns. In this article, we cover the various ways in which you can do so.
3 Methods of deleting empty rows in google sheets
- Filter and delete the empty rows
- Sort the complete data set and delete empty rows
- Delete empty rows using an add on
Method 1: Filter and delete the empty rows
The filter tool comes in really handy to find the empty rows in your data set and delete them. You can follow the steps below to use this method.
Step 1: Filter a Google Sheets spreadsheet for ‘empty’ cells.
Let’s take an example of a dataset here. As you can see there are several empty rows here.
Select your entire data set by clicking CTRL + A on a Windows operating system. You can click on Command + A on a Mac operating system. Your spreadsheet will appear somewhat like this.
Step 2: Create a filter.
Click on the ‘Data’ tab at the top.
Click on Create a Filter option.
You can also click on the funnel-shaped icon in the toolbar below the main tab.
Click on Create a New Filter.
Your spreadsheet will look like the following:
Step 3: Clear the filter.
Click on the filter icon at the top of the columns which looks like an inverted pyramid.
In the drop-down menu that appears, click on Clear which deselects all filters.
Step 4: Click on Blanks
Select Blanks after you’ve clicked on clear in the previous step and click OK.
Step 5: Select empty rows and delete them.
Now you can see the blank rows filtered out together. All the rows with data are now hidden. You can now select all of the empty rows and delete them.
To select, click on the row number from the left. In the current example, it’s row number 3.
Next, press and hold the Shift key and click on the last row. In our case, it’s row number 32.
Step 6: Delete selected rows.
Right click on any of these selected rows. A drop down will appear. Click on
Delete selected rows.
Step 7: Remove the filter.
Now that the empty rows have been deleted, remove the filter. Click on Data at the top panel. Click on Turn off filter from the dropdown that appears.
You can also click on the funnel icon for Filter.
Your data set is free of blank rows now.
Method 2: Sort the complete data set and delete empty rows
This one’s an equally easy alternative that gets you the same result.
Step 1: Sort the data.
Select all your data, even the empty cells, to apply sorting on the data. For this, click CTRL + A on a Windows operating system or Command + A on a Mac operating system.
Step 2: Click on the Data tab.
Step 3: Click on Sort range by column K, Z to A.
Step 4: Select empty rows and delete them.
You will see that all the empty rows are now sorted. You can find them at the bottom of the spreadsheet like this:
Select the empty rows and delete them. You can do this by clicking the row number visible at the left end.
Next, press the Shift key and click on the last empty row. You now have all the empty rows selected.
Right click on any selected row and select Delete rows 45-51 option.
You have now removed all empty rows successfully.
Method 3: Delete empty rows using an add on
You can also use an add-on to delete empty rows. An add-on is a pre-created code to perform a specific function at the click of a button.
Here’s how you can install an add-on in Google Sheets.
Step 1: Click on the Add-ons option in the menu bar.
Step 2: Click on Get add-ons.
You’ll see a pop-up window open.
Step 3: Search and install the add-on.
Type delete empty rows and click on the suggestion that shows up below the search bar.
Click on the first add-on Delete Empty Rows here. You can choose any of these options, but the first one is the simplest to use.
Click on Install.
Select a Google account to sign in with in the pop-up window that opens.
Click on Got it when you see the redirection prompt.
Step 4: Give permissions for installing the add-on.
Click Allow. If you so choose, you can always uninstall any Add-on later.
Step 5: Refresh your Google Sheet as prompted.
Step 6: Use the add-on to delete empty rows.
You can now use your Add-ons option on the main menu to delete empty rows. Click on Delete Empty Rows.
You will see options on the right side of your spreadsheet.
Click on Delete Empty.
You’ll see a new tab open up.
You’ll now see that all empty rows have been deleted.
Additionally, if your header row is not visible, simply click on the 1st row.
Click on the fill icon in the toolbar.
Select a colour of your choice.
You’ll now be able to see the column titles.
You can easily delete empty rows from a Google Sheet using any of these three methods. If you feel that you know of a method that we are missing out on, do let us know in the comments. Happy learning!