Remove Blank Lines in Excel (4 Ways To Clean Up Your Data)

If you work with a lot of data in Excel, it’s very important that information is entered consistently. Blank cells and empty rows can make it more difficult to analyze your data.

In this article, we’ll explore 4 ways to remove blank rows from your spreadsheet. These examples all assume you’re working with an Excel data list.

Method #1: Delete Blank Rows with the Shortcut Menu

The most basic way to remove blank rows is to delete them one by one.

Here is how you delete an individual blank row.

1. Right-click a row selector wherever unneeded blank cells appear.
2. From the shortcut menu, click Delete or press Ctrl + – (minus sign).

To delete several contiguous blank rows in Excel, drag across multiple row headings then right-click anywhere in the selected area and click Delete or press Ctrl + – (minus sign).

To delete blank rows that are located in various positions, click the first row heading as you normally would, then press Ctrl and click additional row headings as needed. Right-click anywhere in the selected area and click Delete or press Ctrl + – (minus sign).

Method #2: Remove Blank Rows with a Filter

If you would like to temporarily remove blank rows from an Excel data list to do some analysis, filtering rows with blank cells can be a quick and effective way to exclude those records – especially if you know how to filter data using keyboard shortcuts!

When you’re done analyzing your data, you simply remove the filter to restore your data list.

Here is how you hide all the rows in a data list that contain blank cells.

1. Select all of the records in your data list, including the column headings.
2. Click the Filter button in the Data > Sort & Filter group (or press Ctrl + Shift + L) to enable filtering.
3. Click the down-arrow in the column heading where the blank cells are located.
4. Click to clear the checkbox beside (Blanks) then click OK.

Filtered rows are hidden from view until you remove the filter.

Method #3: Move Blank Rows to the Bottom by Sorting

You can also move blank rows to the bottom of your data list by sorting any column that contains blank cells.

It doesn’t matter whether you sort a column in ascending or descending order. Blank cells are always displayed last. How’s that for simplicity!

1. Select all of the records in your data list, including the column headings.
2. Click the Filter button in the Data > Sort & Filter group (or press Ctrl + Shift + L) to enable filtering.
3. Click the down-arrow in the column heading where the blank cells are located.
4. Click Sort Smallest to Largest.
   Blank cells are moved to the bottom of the data list.

Method #4: Find and Select Blank Cells Using Go To Special

One of my favorite tools in Microsoft Excel has to be Go To Special. It can locate and select cells with common attributes very quickly.

Here is how you can quickly delete all the blank rows in your spreadsheet with next to no effort.

1. Select all of the records in your data list.
2. Click Find & Select in the Home > Editing group then click Go To Special… from the drop-down menu.
3. In the Go To Special dialog box, click to select Blanks then click OK.
   All of the blank cells are selected.
4. Right-click any one of the selected cells then click Delete from the shortcut menu or press Ctrl + – (minus sign).
5. In the Delete dialog box, click Entire row.

Final Thoughts on Removing Blank Rows

As you evaluate the various methods outlined above, you need to decide whether you want to permanently delete blank rows from your data list or simply hide them.

If you decide to filter blank rows in Excel, you can easily bring the filtered rows back. If you delete entire rows, those rows are permanently removed.