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

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will introduce you to 7 easy ways to remove blank lines in Excel. If your Excel dataset contains unnecessary blank lines or empty rows, you may need to remove those. Because it not only creates confusion but also may become the reason of errors in calculation. So, we require those to be removed from our worksheet. Here, we will have the following effect when we remove the blank lines.https://www.exceldemy.com/how-to-delete-blank-cells-in-excel-and-shift-data-up/

Illustrating How to Remove Blank Lines in Excel


Download Practice Workbook


7 Ways to Remove Blank Lines (Rows) in Excel

In this article, we are going to show 7 handy 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 7 different techniques.

Sample Dataset to Remove Blank Lines in Excel


1. Manually Remove Blank Lines

This is the easiest method to remove blank lines in Excel. However, it is convenient only for small datasets. For large datasets, it becomes an inefficient method.

Steps:

  • Firstly, choose the blank lines by holding Ctrl.

Manually Choosing Cells to Remove Blank Lines in Excel

  • Secondly, right-click and from the available options choose Delete.

Choosing Delete Option

  • After that, from the Delete prompt, first, choose Shift cells up and then press OK.

Deleting by Shifting Cells Up

  • As a result, we will have a dataset free of blank lines.

Manually Remove Blank Lines in Excel

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


2. Applying Keyboard Shortcut to Remove Blank Lines

This method too is a manual one. However, it is faster than the previous method. This also becomes obsolete as the datasets become larger and larger.

Steps:

  • At the beginning, choose the blank lines and press Ctrl+Minus(-).

Using Keyboard Short Cut to Remove Blank Lines in Excel

  • After that, select Shift cells up from the Delete prompt and press OK after that.

Shifting Cells Up to Remove Blank Lines in Excel

  • Consequently, Excel will remove the blank lines from the dataset.

Applying Keyboard Shortcut

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


3. Using Go To Special Command to Remove Blank Lines

Here, we will use the Go To Special command. We will look for blanks in the Go To Special dialogue box.

Steps:

  • Firstly, select the B4:E18 cell range.
  • Then, go to Home >> Editing >> Find & Select >> Go To Special.
  • As a result, a prompt will be on the screen.

Choosing Go To Special Command

  • After that, from the prompt, first, choose Blanks.
  • Then, press OK.

Selecting Blanks Option to Remove Blank Lines in Excel

  • As a result, all the blank spaces within the dataset will be highlighted.

Highlight Blank Cells

  • Thereafter, choose the blank rows or blank lines and right-click.
  • Then, from the available options choose Delete.

Selecting Delete Option

  • Now, choose the Shift cells up.
  • Then, click OK.

Shifting Cells Up to Delete

  • As a result, all the blank lines will be deleted.

Using Go To Special Command to Remove Blank Lines in Excel

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


4. Utilizing Find Command to Remove Blank Lines

In this instance, we will use the Find command to delete the blank lines. Here too, we will have to select the blank lines that we will delete.

Steps:

  • To begin with, select the B4:E18 cell range.
  • Then, select Home >> Editing >> Find & Select >> Find.
  • Consequently, a prompt will be on the screen.

Choosing Find Command to Remove Blank Lines in Excel

  • In the prompt, first, keep the Find what box blank.
  • Secondly, mark the Match entire cell contents box.
  • Finally, click OK.

Finding Blank Cells

  • As a result, the list of all the blank cells will be on the screen.
  • From the list, choose the blank lines and then click on Close.

Choosing Blank Lines

  • Consequently, the blank lines will be highlighted on the dataset.

Highlighting Blank Lines to Remove Blank Lines in Excel

  • Finally, remove the blank lines as the Previous Methods.

Utilizing Find Command to Remove Blank Lines in Excel

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


Similar Readings


5. Implementing Sort Command to Remove Blank Lines

In this method illustration, we will sort our data by using the Sort command and then delete the blank rows.

Steps:

  • First, choose the B4:E18 cell range.
  • Thereafter, go to Home >> Editing >> Sort & Filter >> Sort A to Z.

Choosing Sort Command

  • As a result, it will sort the dataset separating the blank lines from the non-blank ones.

Selecting Sorted Blank Lines

  • Finally, delete the blank lines as we did in the Previous Method to get a blank line free dataset.

Implementing Sort Command to Remove Blank Lines in Excel

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


6. Utilizing Filter Command

In this example, we will use the Filter command to get rid of the blank lines from our dataset.

Steps:

  • Firstly, choose the B4:E18 cell range.
  • After that, choose Data >> Sort & Filter >> Filter.
  • As a result, all the column headers will have a filter handle.

Selecting Filter Command to Remove Blank Lines in Excel

  • Now, choose any of the filter handles first.
  • Afterward, mark the Blank box.
  • Finally, press OK.

Selecting Filter Handle

  • As a result, all the blank cells will be filtered.
  • From the filtered data, choose the blank lines and delete them by following the Previous Method.

Filtering Out Blank Lines to Remove Blank Lines in Excel

  • After that, click on the filter handle again.
  • Then, mark the Select All box and click on OK.

Filtering Non-Blank Values

  • Consequently, we will have a dataset free of blank lines.

Utilizing Filter Command to Remove Blank Lines in Excel

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


7. Applying FILTER Function

In this final method, we will use the FILTER function to remove blank lines from our dataset. This is the quickest and most efficient method to do so.

Steps:

  • First, click on the B20 cell and type,
=FILTER(B5:E18,B5:B18<>"")
  • Then, press Enter.
  • As a result, we will get a blank line free dataset.

Applying FILTER Function to Remove Blank Lines in Excel

Read More: Find If Cell is Blank in Excel (7 Methods)

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 Bala

Prantick Bala

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo