One of the most common tasks in Excel is to remove blank rows. Whatever type of information you may have in Excel, there are many times that you will have many blank lines throughout the file. If you have thousands of rows, removing the blank lines manually is a major pain and virtually impossible. The most common way to remove blank rows in Excel, at least as mentioned online, is to use some types of Excel macros.
However, if you are not familiar with macros, this method may be difficult. In addition, if the operation is not correct, you may not know how to change the macros for your specific Excel file. In this article, I will show three methods that you can try to remove blank rows in Excel and the advantages and disadvantages of each method. Please note that these methods will work for all editions of Excel, from Excel 2003 to Excel 2016 and up.
How to Remove/Delete Blank Rows in Excel:
Method 1: Add a sort column
Fortunately, there is a very easy and effective way to remove blank rows in Excel without any macros. It basically involves sorting and this is how you do it. The first thing we will do is insert columns and numbers respectively. You might ask, why do we want to do this? Now, if the order of the rows is important when we sort Column A to eliminate blank lines, there is no way to retrieve the rows back in the same order before sorting.
Here is what the sheet looks like before we sort the Animal Name column:
- Now, select both columns and click on the data rip in Excel, then click the Sort button on the Data tab.
- To sort by, choose column B and click OK. Note that you want to sort the column with blank values. If more than one column is blank, select one column.
- Your information should now look like this below. As you can see, it’s easy to delete empty rows because those rows are below:
- When you delete a row, you may now see why we added columns by numbers. Before the order is “cat, dog, pig, etc.” and now it is “bear, cat, cow, etc. …” so just reorder by column A to get the order back.
Quite easily, right? What I like about this method is to delete rows that only have one column that has more blank values than would be blank.
Method 2: How to delete blank rows correctly
In the third worksheet of our sample workbook, you will see another data set that has some blank rows. We will use filters to highlight empty rows.
- Highlight columns A, B, and C. Open the Data tab in the ribbon and click Filter.
- To filter empty rows, we will click the filter arrow in column A, uncheck (select all) and select (empty):
- As you can see, this will leave a few rows with spaces in column A but with data in other rows.
- We will repeat the filter for spaces in columns B and C to eliminate those rows:
- Now, select the filtered rows and click Delete> Delete Sheet Rows on the Home tab.
- Clear the filter from the column:
- And your empty row are gone!
This method has more steps than other steps but will store more data. When you do it a couple of times it will be much faster. I suggest learning to reduce the risk of losing important cells.
Method 3: Use filter
Similarly, using the collating function, we can also use the filter option. When using this method, you don’t need to add to the additional columns of anything, just select all the columns that need to check the blanks and click the filter button.
- You will see a dropdown arrow appear next to each title in the header row. Click there and uncheck all and check the space at the bottom.
- Now do the same thing for each column in the worksheet. Note that if you get to the point where only the blank rows are shown, you won’t have to select any spaces for the remaining rows as they are checked by default.
- You will see the number of blank rows is highlighted in blue. Now, just select those rows, right-click and select Delete. When they disappear, just click on the filter button again to delete your original filter and all non-empty rows should appear again.
There are many methods you can use to delete blank rows in Excel, from very simple to more complicated. And while the easiest way to eliminate those rows is to use but we suggest learning more complicated things You are less likely to accidentally lose data. It takes a little longer to use the filters to eliminate the rows. But in the end, it’s a better way of doing things.