If your Excel dataset contains unnecessary blank lines or empty rows, you may need to remove those. In this article, I will introduce you to 8 easy ways to remove blank lines in excel
Let’s say in our dataset we have data of different members of a club. Our dataset contains several blank lines. We will delete these empty rows by using 8 different techniques.
Download Practice Workbook
8 Ways to Remove Blank Lines(Rows) in Excel
1. Manually Delete Blank Lines
To delete blank lines manually, first, you need to select all of the empty rows you want to remove. Then right-click on any of your selected rows. A dropdown menu will appear. Select Delete from that menu.
After that, all the blank lines will be removed.
2. Remove Blank Lines Using Keyboard Shortcut
The easiest way to remove blank lines is using a keyboard shortcut. Select all the empty rows you want to delete and press CTRL and Minus key (-) altogether. All the bank lines will be deleted.
3. Remove Blank Lines by Go to Special Command
You can delete blank lines by using the Go to Special command.
First go to Home> Editing > Find & Select > Go to Special
A Go to Special box will appear. Select Blanks and click on OK.
Now all the blank cells of your dataset will be selected
You can manually deselect those empty cells which you don’t want to remove.
After that go to, Home> Cells and click on Delete.
Now All the blank lines will be removed.
4. Find Command to Remove Blank Lines
You can also delete empty rows by the Find command. First go to Home> Editing> Find & Select > Find
Check the Match entire cell contents box in the Find and Replace box and click on find all.
Now, at the bottom of the Find and Replace box, you will see all the empty cells of your dataset. Select the empty cells you want to delete and close the box.
Now, you will see all the blank lines are highlighted.
To remove these blank lines, go to Home> Cells and Click on Delete.
After that, all the blank lines of your dataset will be deleted.
- Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
- How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)
- Fill Blank Cells with 0 in Excel (3 Methods)
- How to Autofill Blank Cells in Excel with Value Above (5 Easy Ways)
- How to Skip Blank Rows Using Formula in Excel (8 Methods)
5. Using Sort Command to Delete Blank Rows
Another easy way to remove blank rows is using Sort Command. First, Go to Data> Sort & Filter. Then select either the icon of sorting in ascending order or the icon of sorting in the descending order.
All the blank lines of your dataset will be automatically removed after that.
6. Filter to Remove Blank Rows
Using Filter is another way to delete empty rows. First go to Data> Sort & Filter > Filter
After clicking on Filter, the Downward arrow will appear beside every column header. Click on the Downward arrow besides Name, check only on the Blanks box, and press OK.
Now you will only see the blank lines of your dataset. Select these rows and right-click. Select Delete Row from the dropdown menu.
A Microsoft Excel confirmation box will appear. Click on OK in this box.
As a result, all the blank lines will be removed.
Now again click on the downward arrow besides Name, check (Select All) and click on OK.
Now, you will see your dataset has only the filled lines. All the blank lines are removed.
7. Remove Blank Rows by FILTER Function
You can remove the blank lines by using the FILTER function. Type the following formula in an empty cell,
Here, A4:D18 = range of your data
A4:A18 = range of the filtering column
<>”” indicates that all the blank lines will be removed.
After pressing Enter, you will get your dataset where all the blank lines are removed.
Read More: Find If Cell is Blank in Excel (7 Methods)
8. Advanced Filter to Delete Blank Rows
By making a small setup, you will be able to use Advanced Filter to remove blank lines from your dataset. First, type the name of the header of your first column (Name) in an empty cell (F5) and type =”” in the adjacent cell(F6) of the first cell (F5) in that column.
Now, Go to Data> Sort & Filter> Advanced
An Advanced Filter box will appear. In the box Criteria range, select cells F5 and F6. After that press OK.
As a result, all the blank lines of your dataset will be removed.
You can remove blank lines in Excel by using any of the described methods. If you face any confusion about any of these methods, please leave a comment.