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.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.