Exceljet

Quick, clean, and to the point

This video is part of our video training library.

Shortcuts Recipe Remove Blank Rows

Tags 
Summary 
In this video, we'll look at a simple shortcut recipe for removing thousands of blank rows in a large set of data. Shortcut recipes are just a series of shortcuts you perform in a certain sequence to solve a specific problem.
Video Transcript 

In this video, we'll look at a simple shortcut recipe for removing thousands of blank rows in a large set of data. Shortcut recipes are just a series of shortcuts you perform in a certain sequence to solve a specific problem.

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.

Author 
Dave Bruns

Related shortcuts

CtrlSpace
Space
Ctrl-
-
Very satisfied with the product! It has made me more efficient at work! -Evan
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course