One of the most common tasks in Excel is to remove blank lines. Regardless of the type of data that you may have in Excel, in many cases you will also have many blank lines throughout the file.
If you have thousands of lines, manually removing blank lines is a big problem and almost impossible. The most common way to remove blank lines in Excel, at least as mentioned on the web, is to use some kind of Excel macro.
However, if you are not familiar with macros, this method can be difficult to implement. Also, if it doesn’t work as expected, you might not know how to change the macro for your specific Excel file. In this article, I will show you several ways to remove blank lines in Excel and the advantages and disadvantages of each method. Note that these methods will work for any version of Excel, from Excel 2003 to Excel 2016 and later.
First method – add a sort column
Fortunately, there is a simple and very effective way to remove blank lines without any macros. This mainly involves sorting. Here’s how to do it. Let’s say we have the following dataset in Excel and we want to get rid of empty rows:
The first thing we’ll do is insert a column and number it sequentially. Why do we need to do this, you may ask? Well, if the order of the rows matters when we sort column A to get rid of empty rows, there will be no way to return the rows in the order they were in before sorting.
– /
This is what the table should look like before sorting the column with animal names:
Now select both columns and click the data ribbon in Excel. Then click the Sort button on the Data tab.
In the Sort By box, select Column B and click OK. Note that you want to sort the column that has empty values. If multiple columns have blank values, just select one.
Your data should now look like below. As you can see, removing blank lines is easy because they are all at the bottom:
After you remove the rows, now you can probably understand why we added a column of numbers? Before sorting, the order was “Cat, Dog, Pig, etc.”, and now it is “Bear, Cat, Cow, etc.” So just sort again by column A to revert back to the original order.
Pretty simple, right? What I like about this method is that it is very easy to delete rows that have only one column with empty values, and not the whole row is empty. So what if you need to check multiple columns, not just one?
Well, using the same method, you simply add all the columns to the Sort By dialog box. Here’s another example I created:
As you can see, this sheet is more complex. Now I have some completely blank lines, but some lines are only partially blank. If I just sort by column B, I don’t get all the completely blank rows at the bottom. After adding the numbers, select all five columns and by clicking Sort, you will see that I have added four levels to the Sort By option.
When you do this, only those rows will be displayed at the bottom where all four columns I added are empty.
You can now remove those lines and then use the rest of the lines to revert them to the original order they were in.
The second way – the use of filters
Similar to using the sort function, we can also use a filter parameter. Using this method, you don’t need to add anything like this to any additional column. Just select all the columns you want to check for spaces and click the Filter button.
You will see a dropdown arrow next to each heading in the title bar. Click on it and then uncheck the Select All box and check the Blanks box at the bottom.
Now do the same for each column on the sheet. Note that if you get to the point where only blank lines are displayed, you don’t need to select blank spaces for any of the remaining lines as they are already checked by default.
You will see the empty line numbers are highlighted in blue. Now just select those lines, right click and choose Delete. When they disappear, just click the Filter button again to remove the filter and all the original non-blank lines should reappear.
Method 3 – Delete any blanks
Another method that I would like to mention will remove any row that has either a completely empty row or any row that has at least one empty column. This is definitely not for everyone, but it can be useful if you want to delete rows that contain only partial data.
To do this, select all the data on the sheet and press the F5 key. This will bring up the Go To dialog box in which you want to click on Special.
Now select “Presets” from the list and click “OK”.
You will see that all blank cells or rows will be grayed out. Now, on the Home tab, click Delete and select Delete Sheet Rows.
As you can see, this removes any row in any column of which there is at least one blank cell.
This is not useful for everyone, but it can be quite useful in some cases. Choose the method that works best for you. Enjoy!
–