How to Remove Blank Lines in Excel (8 Easy Ways)

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.

dataset

 

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.

Remove Blank Lines

After that, all the blank lines will be removed.

organized dataset

Read More: How to Delete Blank Cells in Excel and Shift Data Up

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.

Remove blank lines

Read More: How to Find Blank Cells in Excel (8 Easy Ways)

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

Go to special

A Go to Special box will appear. Select Blanks and click on OK.

go to special box

Now all the blank cells of your dataset will be selected

highlighted cells

You can manually deselect those empty cells which you don’t want to remove.

highlighted blank lines

After that go to, Home> Cells and click on Delete.

Remove blank lines

Now All the blank lines will be removed.

blank rows removed

Read More: How to Apply Conditional Formatting in Excel If Another Cell Is Blank

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

find

Check the Match entire cell contents box in the Find and Replace box and click on find all.

find and replace

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.

empty cells

Now, you will see all the blank lines are highlighted.

highlighted cells

To remove these blank lines, go to Home> Cells and Click on Delete.

Remove blank lines

After that, all the blank lines of your dataset will be deleted.

blank rows removed

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)


Similar Readings


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.

Sort

All the blank lines of your dataset will be automatically removed after that.

Remove blank lines

Read More: How to Remove Blanks from List Using Formula in Excel (4 Methods)

6.   Filter to Remove Blank Rows

Using Filter is another way to delete empty rows. First go to Data> Sort & Filter > 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.

filter dropdown

Now you will only see the blank lines of your dataset. Select these rows and right-click. Select Delete Row from the dropdown menu.

delete rows

A Microsoft Excel confirmation box will appear. Click on OK in this box.

conformation box

As a result, all the blank lines will be removed.

delete empty rows

Now again click on the downward arrow besides Name, check (Select All) and click on OK.

dropdown menu

Now, you will see your dataset has only the filled lines. All the blank lines are removed.

Remove blank lines

Read More: How to Set Cell to Blank in Formula in Excel (6 Ways)

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,

=FILTER(A4:D18,A4:A18<>"")

Here, A4:D18 = range of your data

A4:A18 = range of the filtering column

<>”” indicates that all the blank lines will be removed.

FILTER function

After pressing Enter, you will get your dataset where all the blank lines are removed.

Remove blank lines

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.

custom formula

Now, Go to Data> Sort & Filter> Advanced

Advanced filter

An Advanced Filter box will appear. In the box Criteria range, select cells F5 and F6. After that press OK.

advanced filter box

As a result, all the blank lines of your dataset will be removed.

Remove blank lines

Read More: How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas

Conclusion

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.


Related Articles

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo