In today’s ExcelJet tip, we’ll show you a cool way to delete blank rows, even hundreds or thousands of blank rows, in record time. Even better, Excel does all the hard work for you. Let’s take a look. Here we have a really big list that contains a lot of empty rows. If we hop down to the bottom of the sheet, then back up to the bottom row, we can see that we have over 36,000 rows, and several thousand of these rows are empty. Sure, we could just work our way through the list, deleting these empty rows one by one. But that will take a really long time, and it won’t be any fun at all. So let’s look at a really fast way to do it using Excel’s Go To Special command. To start off, select the entire first column. Then make sure you are on the Home tab of the ribbon. Now click Find & Select and choose Go To Special. Select “Blanks” and click OK. Excel has now selected all of the blank cells in our first column. Now carefully right-mouse click on one of the empty cells, and choose Delete from the menu. Then select Entire row, and click the OK button. Now we have a clean list with no blank lines. If we hop down to the bottom of the list, there are a little more than 33,000 rows, which means we just deleted over 3,000 empty rows! In a future tip, we’ll show you how to use this same approach to remove non-blank rows with missing values. See you next time.
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.
