How To: Delete Empty Rows From A Range Of Cells In Google Sheets

Today I faced an issue where I was copy / pasting rows from a LibreOffice Calc spreadsheet document into a new Google Sheets spreadsheet.

The problem I faced was when I did this, I retained all the empty rows / gaps in between that I didn’t want.

Here’s a basic example of what I started with:

Holding down ctrl and clicking on each cell enables me to select only the ones I am interested in keeping.

But when I paste this into a new Google Sheets spreadsheet, I end up with this:

There is actually two problems here:

1. The unwanted extra blank rows
2. It copied in the text formatting

How I Fixed

There are two problems, so there are two fixes.

Removing the empty rows is one of those things that once you know how to do it, will seem really obvious. But before you do, it isn’t the most intuitive solution.

To delete empty rows in a range of cells in Google Sheets, you can use the following steps:

1. Select the range of cells that you want to delete empty rows from.
2. Go to the Data menu and select the “Remove Duplicates” option.
3. In the Remove Duplicates dialog box, make sure that the column you want, and the “Select All” check boxes are selected, and then click the “Remove Duplicates” button.
4. This will remove all rows that are completely empty within the selected range.

This should pop up a box:

And clicking ‘Remove duplicates’ gives you almost exactly what you want:

I’m left with one empty row.

Why?

Because all the other empty rows were seen as duplicates.

It kinda makes sense, in an unintuitive way.

Of course if your data set contains duplicates then this technique won’t work. I didn’t experience that problem fortunately.

I still need to remove that one extra row, but that’s one row compared to the hundreds I had in my case. I’ll take one over 100+ any day.

And to clean up the formatting is very simple.

Select all the cells from which you wish to clear the formatting, then go to Format > Clear Formatting:

And with the extra blank row removed, we are done: