How to Delete All Empty Rows and Columns in Google Sheets

How to Delete All Empty Rows and Columns in Google Sheets
How to Delete All Empty Rows and Columns in Google Sheets – Sheetaki

Deleting all empty rows and columns in a Google Sheets spreadsheet allows you to tidy up your dataset.

At times, you’ll find it necessary to clean up your spreadsheet. A good way to start it is to delete all the empty rows and columns between records. Usually, you can also select all the records you want to delete and call on the command in the right-click context menu. This would not be ideal for spreadsheets that contain hundreds of records.

In this guide, we’ll show you possible workarounds for this scenario. We will also demonstrate different ways on how to delete all empty rows and columns in a spreadsheet.

Let’s get started!

 

 

Filter and Delete All Empty Rows in Google Sheets

One efficient way to delete all empty rows in a spreadsheet is to filter out these rows first. After that, you can already select and delete them easily.

Let’s try out a simple activity to see how it works. First, make sure to click the link below to have a copy of our example dataset.

Afterward, follow the instructions below.

Filter All Empty Rows

The first thing you need to do is to filter out all the empty rows. Doing so will let you display only the rows that you need to delete.

Here are the steps on how to do it:

  1. With the spreadsheet open, make sure that Sheet1 is the active sheet by clicking its name. You should have this dataset:
    Example dataset for filtering and deleting empty rows in Google Sheets
  2. The next step you need to do is to select the entire dataset. There are several ways to do this; the easiest is to press CTRL + A on your keyboard.
    Highlight the entire dataset
  3. At this point, you can already create a filter for the whole dataset. To do so, click on the Data menu and choose to Create a filter.
    Create a filter

    Upon clicking, you’ll see that funnel-shaped symbols will appear at the headers of your dataset. These symbols indicate that you have successfully created a filter.
    Example of filter in Google Sheets
  4. Now that the dataset has a filter, we can now filter out all the empty rows within the spreadsheet. Click on the filter symbol in any of the headers. Doing so will display a drop-down menu similar to this one:
    Using the filter drop-down menu
  5. By default, you can see that all the fields under the filter are selected. This means that Google Sheets is configured to show all the fields in the dataset. Let’s clear all the selections by clicking the Clear button on the drop-down menu.
    Clear all the fields
  6. After that, click (Blanks) on the filter list. This will check only the empty rows in the dataset.
    Filter out the empty rows in Google Sheets
  7. Next, click the OK button to apply your changes. All the empty rows should now be filtered and displayed on your spreadsheet.
    A spreadsheet with filtered empty rows

Delete All Empty Rows

With the empty rows only displayed, you can now quickly delete them. Here’s how to do it:

  1. First, you’ll need to highlight all the filtered rows. You can do this by clicking the header of the first empty row and dragging it toward the end of the dataset. In the example below, rows 3:21 is highlighted.
    Highlight the filtered empty rows
  2. This time, delete the selected rows by right-clicking anywhere within the highlighted portion and selecting the Delete selected rows command.
    Deleting the filtered empty rows in Google Sheets

    Upon deleting, your spreadsheet should now contain only the headers of your dataset.
    A spreadsheet with deleted empty rows
  3. Now, use the filter tool again to show all the records of your dataset.
    Showing all the records of a filtered dataset

    If you’ve performed all steps correctly, your spreadsheet should now look like this:
    A spreadsheet without empty rows
  4. Once you’ve filtered out and deleted all the empty rows, you can now remove the filter tool. To do this, click the Data menu, and then choose Remove filter.
    Remove the filter

Great! You just learned how to filter and delete all empty rows in a Google Sheets spreadsheet.

 

 

Sort the Dataset and Delete Empty Rows

It is possible to quickly delete all empty rows by sorting your data first. This will arrange all your records in ascending or descending order, sorting all empty records together. This way, you can easily highlight the empty rows and perform the delete command.

Let’s try out another activity to demonstrate this technique.

  1. Open the spreadsheet you used earlier and make Sheet2 the active sheet. You should have this dataset:
    Example spreadsheet for sorting and deleting empty rows in Google Sheets
  2. Next, highlight the entire dataset, excluding the headers. Be guided by the example below.
    Select the entire dataset
  3. After that, click on the Data menu, and then move your mouse pointer over the Sort range option. Google Sheets will provide you with additional options to choose from at this point. From the list, click the option that says Sort range by column A (A to Z).
    Sort the dataset
  4. Upon clicking, Google Sheets will automatically sort your dataset in ascending order. You’ll notice that all the empty rows will be placed at the bottom of the dataset.
    A spreadsheet with sorted records
  5. Since all the empty rows have already been grouped together, you can easily highlight them all and delete them at once, as we did earlier.
    Delete the sorted empty rows

And that’s another way of deleting all empty rows in a spreadsheet! This technique is best used when you also need to sort your dataset.

 

 

Delete All Empty Columns in Google Sheets

Unfortunately, the filter and sort techniques are not applicable if you want to delete all empty columns in your spreadsheet. But don’t worry, there’s a solution to this problem.

You can use Google Apps Script to delete your dataset’s empty columns.

Here’s how to do it:

  1. First, open the Clean sheet Apps Script project by clicking the link below.
  2. Once you’ve clicked the link, you’ll be redirected to the Apps Script interface, which contains the codes of the Clean sheet project by Google.
    The Clean sheet Apps Script project

  3. Next, click the Overview icon on top of the interface, as shown below.
    Click the overview button on the Apps Script interface
  4. Copy the project by clicking the Make a copy button on the interface’s upper right corner.
    Make a copy of the Clean sheet project
  5. At this point, you should now have a copy of the Clean sheet project. On top of the copied project, click Deploy, and then select Test deployments.
    Deploy the copy of the Clean sheet project

    The Test deployments panel should appear on your screen at this point.
    The Test deployments panel
  6. This time, click the gear icon beside the Select type label and choose Editor Add-on.
    Set the type of deployment
  7. Afterward, click the Create new test option, and then click the Test document field to browse for the spreadsheet where you want to deploy the project.
    Set the document where you want to deploy the project
  8. Locate the spreadsheet containing the empty columns you wish to delete, then click Insert. In the example below, a spreadsheet named ‘Delete All Empty Rows and Columns in Google Sheets – Sheetaki’ is selected.
    Locate the spreadsheet
  9. After locating the spreadsheet, click the Save test button.
  10. Now, open the spreadsheet by selecting it from the list and then click Execute.
    Execute the spreadsheet with the Clean sheet project
  11. To run the Clean sheet Apps Script project on the spreadsheet, highlight the entire dataset and then click the Extensions tab. After which, move your mouse pointer over the option that says ‘Copy of Clean sheet’.
    Run the Clean sheet Apps Script to delete all empty columns in Google Sheets
  12. Finally, delete all the empty columns by clicking the Delete blank columns option.
    Delete all empty columns in Google Sheet using the Clean sheets Apps Script

Perfect! Now you know how to use Google’s Apps Script to delete all the empty columns in your spreadsheet.

There you have it. We just demonstrated three different techniques you can use for deleting all empty rows and columns in Google Sheets.

If you are interested in learning more about spreadsheets, check out our other Google Sheets articles.

Stay up-to-date with other essentials in Google Sheets by subscribing to our newsletter.

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

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

You May Also Like