Exceljet

Quick, clean, and to the point

Shortcut recipe: delete blank rows

In this video, we'll look at a shortcut recipe that lets you easily remove hundreds or thousands of blank rows in a worksheet.

In this first example, we have a big set of data that contains a lot of blank rows. If I move to the last cell, you can see that there's over 36,000 rows of data, and, looking at the count, just over 33,000 rows actually contain data, which means over 3000 rows are blank.

To quickly remove these blank rows, I can use Go To Special. First, pick a column that should always contain data. In this case, Column A should always have a name.

Then select the column, and use Ctrl + G for Go To. Then Special, then select Blanks.

At this point, every blank cell in column A is selected. Now use Control minus to delete, and choose Entire row.

When I click OK, all blank rows are completely removed.

Now if I move to the bottom of the data, you can see that we  just have about 33000 rows remaining.

Note this trick actually deletes blank rows below the data as well, so be sure you don't have important information below the data.

Course 
Excel Shortcuts

Related shortcuts

CtrlSpace
Space
Ctrl-
-
Author 
Dave Bruns