How to Remove Blank Lines in Excel (7 Methods)

Dataset Overview

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 methods.

Method 1 – Manually Remove Blank Lines

This method is straightforward but best suited for small datasets.

Steps

• Select the blank lines by holding down the Ctrl key.

• Right-click and choose Delete from the available options.

• In the Delete prompt, select Shift cells up and click OK.

• Your dataset will now be free of blank lines.

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

Method 2 – Applying Keyboard Shortcut

Although manual, this method is faster than the previous one.

Steps

• Select the blank lines.
• Press Ctrl + Minus (–).

• Choose Shift cells up from the Delete prompt and click OK.

• Excel will remove the blank lines.

Method 3 – Using Go To Special Command

Steps

• Select the cell range (e.g., B4:E18).
• Go to Home > Editing > Find & Select > Go To Special.

• In the prompt, choose Blanks and click OK.

• All blank cells within the dataset will be highlighted.

• Right-click on the blank rows or lines and choose Delete.

• Select Shift cells up and click OK.

• Blank lines will be deleted.

Read More: How to Remove Blank Cells from a Range in Excel

Method 4 – Utilizing Find Command

Steps

• Select the cell range (e.g., B4:E18).
• Go to Home, select Editing, click on Find & Select and choose Find.

• In the prompt, leave the Find what box blank.
• Check the Match entire cell contents box.
• Click Find All.

• A list of all blank cells will appear.
• Choose the blank lines from the list and click Close.

• Blank lines will be highlighted.

• Remove them as in previous methods.

Method 5 – Implementing Sort Command

Steps

• Select the cell range B4:E18.
• Go to Home, select Editing, click on Sort & Filter and choose Sort A to Z.

• This will sort the dataset, separating blank lines from non-blank ones.

• Delete the blank lines as described in the previous method to obtain a dataset without blank lines.

Method 6 – Utilizing Filter Command

Steps

• Choose the B4:E18 cell range.
• Go to Data, select Sort & Filter and click on Filter.
• All column headers will now have a filter handle.

• Click any filter handle and select the (Blanks) box.
• Press OK to filter out all blank cells.

• Delete the blank lines following the previous method.

• Click the filter handle again, mark the Select All box, and click OK.

• Your dataset will be free of blank lines.

Read More: How to Remove Blank Cells in Excel

Method 7 – Applying FILTER Function

Steps

• Click on cell B20 and insert:

• Press Enter.
• You’ll get a dataset without blank lines.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Delete Blank Cells and Shift Data Left in Excel
• How to Ignore Blank Cells in Range in Excel
• How to Leave Cell Blank If There Is No Data in Excel
• How to Remove Unused Cells in Excel

<< Go Back to Blank Cells | Excel Cells | Learn Excel