Remove (Delete) Blank Rows in Excel

Removing blank rows implies deleting the empty rows of an Excel worksheet. An empty (or blank) row is one that does not contain any data values. Such blank rows can cause errors in the applied formulas and make the excel dataset look unorganized and untidy.

For example, some unwanted data was removed from row 4 of the following excel dataset. This deletion has created a vacuum in the worksheet, suggesting incomplete or missing entries. Hence, it is essential to eliminate such blank rows from the dataset.

Blank rows appear in a worksheet due to different reasons like copying data from another database, importing data from the Internet, receiving data from other team members, and so on. The purpose of deleting blank rows is to deliver clean datasets that are fit for further processing and analysis.

This article discusses the different techniques for removing (deleting) blank rows in Excel.

The techniques of removing blank rows from an Excel worksheet are listed as follows:

1. Manual deletion
2. Deletion using the “go to special” window
3. Deletion using filters
4. Deletion using the COUNT function

Let us explore these methods one by one with the help of examples.

This method is quite helpful when the dataset is small. Moreover, it is an easy technique that does not take much time when applied.

Example #1

The following image shows the revenues generated (column B), costs incurred (column C), and profits earned (column D) by an organization for the different months (column A). The negative figures of column D represent losses.

The dataset pertains to the year 2016. Further, there are three blank excel rows in this dataset, which we want to delete using the following methods:

1. “Delete sheet rows” option of the Home tab
2. “Delete” option of the context menu
3. “Ctrl+minus (-)” shortcut

Step 1: Select the first blank row (row 5) of the dataset. For this, select any cell of row 5 and press the keys “Shift+space” together.

The selection is shown in the succeeding image.

Note: “Shift+space” is the shortcut key to select the entire row of the worksheet.

Step 2: Once the entire row has been selected, use any of the following methods to remove (delete) the blank Excel row:

Method “a”: From the Home tab, click the drop-down arrow of “delete” from the “cells” group. Next, select “delete sheet rows,” as shown in the following image.

Method “b”: Right-click the selected row (row 5) and choose “delete” from the context menu. This is shown in the following image.

Method “c”: Press the keys “Ctrl+minus (-)” together. Had we selected a cell of row 5 rather than the entire row (in step 1), the “delete” dialog box would have opened. Next, one could have selected “entire row” and clicked “Ok.”

Note: “Ctrl+minus (-)” is the keyboard shortcut to delete the selected row or column entirely. For this shortcut to work, it is essential to select the row or column to be deleted in the foremost step.

Step 3: The selected row (row 5 selected in step 1) is deleted. Consequently, the data of the initial rows 6, 7, and 8 has shifted upwards to rows 5, 6, and 7.

Hence, the vacuum at row 5 of the dataset has been filled up. This is shown in the following image.

Step 4: Select the next blank row, which is row 8 of the current dataset. Next, press the F4 key to delete this selected row in excel.

The selected row is shown in the following image.

Note: The F4 key repeats the last action performed by the user. In this example, the last action performed was the deletion of the entire row 5.

In this way, all the blank rows of the given dataset are deleted. Hence, the size of the dataset has been reduced by eliminating unwanted rows.

This method works well when a large number of rows have to be deleted from the dataset. In this technique, the blank rows are searched prior to being deleted.

Example #2

The following image shows a dataset similar to that of example 1. Notice that this time, the respective years have been added in column B. Moreover, the data for 2017 has also been added in the bottom rows.

We want to delete all the blank excel rows with the help of the “go to special” dialog box.

In this method, the blank rows are filtered, selected, and deleted.

Example #3

Working on the data of example #2, we want to remove the blank excel rows by filtering them.

The steps for the given task are listed as follows:

Step 1: Select the entire dataset (or the entire worksheet) and add excel filters. For adding filters, click the “sort and filter” drop-down from the “editing” group of the Home tab. Next, choose “filter.”

Alternatively, select “filter” from the “sort and filter” group of the Data tab. The filters have been added to the dataset, as shown in the following image.

Step 2: Click the filter of “month.” Uncheck the “select all” option to deselect all the months. Select “blanks,” as shown in the following image. Next, click “Ok.”

Note: Be careful while selecting the blank cells since these will be deleted in the subsequent steps. Ensure that no data cells are selected.

Step 3: At present, only the blank rows of the dataset are visible. These are shown in the following image.

Step 4: Select all the filtered rows. Ensure that the top row containing the column headers is not selected. Next, press the keys “Alt+semicolon (;)” together. For this, hold the “Alt” key and, at the same time, press the semicolon.

The visible blank cells are selected, as shown in the following image.

Note: “Alt+semicolon (;)” is the shortcut to select only the visible cells of a selection. It ignores the hidden rows and columns of the selection.

Step 5: Once the visible cells are selected, press the keys “Ctrl+minus sign (-)” together. If a warning appears, which asks if the entire sheet row has to be deleted, click “Ok.”

Step 6: All the blank excel rows are deleted from the given dataset. In the filter of “month,” check the “select all” option to view the entire dataset. Clear the filters by clicking the “filter” option (or the “clear” option) from the Home tab or the Data tab.

Hence, the cleaned dataset is shown in the following image. The rows appearing in blue were earlier blank. But, now they have been filled by an upward movement of the data values.

In this method, the COUNT function is applied to filter and delete blank rows.

Example #4

Working on the data of example #2, we want to remove the blank rows by applying the COUNT excel formula. The rows which return zero (0) as the result of the COUNT formula should be filtered and deleted.

The steps for removing blank rows by applying the COUNT formula are given as follows:

Step 1: Insert a new column (column F) titled “count.” This is shown in the following image.

Step 2: Enter the following COUNT formula in cell F2.

“=COUNT(A2:E2)”

Press the “Enter” key. To obtain the outputs for the entire column F, drag the formula of cell F2 till the last data cell of column F.

The results are shown in the following image. For all the blank rows, the COUNT formula returns zero. For the remaining rows, this formula returns the count of numerical cells.

Note: The COUNT formula counts the number of cells containing numerical values in the given range (like A2:E2). If no numerical values are found in a particular range, the formula returns zero.

Step 3: Add filters to the dataset (refer to step 1 of example #3). Click the filter of the “count” column. Deselect option four (4) and select option zero (0). This is shown in the following image.

Click “Ok” once the required checkbox is selected.

Step 4: The dataset shows those rows for which the COUNT formula has returned zero. Select these filtered rows, which show 0 in column F. Press the keys “Alt+semicolon (;)” to select the visible cells.

The selection is shown in the following image. Next, press the shortcut “Ctrl+minus (-)” to delete these blank rows. If a warning is displayed asking whether the entire row should be deleted, click “Ok.”

Step 5: The blank rows have been deleted. From the filter of the “count” column, check the “select all” option. The cleaned dataset is shown in the following image.

We have not removed the filters though one can remove them if required. The column “count” can also be deleted since it has met the goal of eliminating blank rows from the dataset.