Excel Remove Blank Rows

MS Excel, or Microsoft Excel, is powerful spreadsheet software that enables users to record large sets of financial data in cells and tables across multiple worksheets. While working with large data, there may be cases when we may see blank rows in our excel sheet. To make the data attractive and organized, the blank rows should be removed. There are several reasons for getting blank rows in sheet such as exporting data from database, getting workbook from other persons, removing the data manually etc.

With blank rows in the sheet, the overall presentation of the sheet's data doesn't look good. It typically looks like we are missing some data in certain rows within the sheet. In addition, a blank row or even a blank cell can cause formula errors if particular cells are referenced incorrectly in the formula. Hence, it is always better to remove/delete such unnecessary blank/empty rows from the worksheet.

This article discusses various easy-to-use methods to help us delete blank or empty rows within a worksheet. It is necessary to know each possible method of deleting blank rows in a sheet so that we can try different methods in different use-cases.

How to remove/ delete blank rows in Excel?

One benefit of using Excel to perform any certain spreadsheet-related task is the availability of many options. Similarly, when we need to delete or remove blank rows in a worksheet, it provides various options or methods. Some of the most commonly used methods are discussed below:

Not remove blank Rows by selecting blank cells

Before removing blank row, sometimes the row containing data are deleted. For example,

If the worksheet contains data, in a cell range A1:D5 as follows:

In this data, the empty cell present in C3 is removed. Here one of the methods to remove the blank cell is,

Select the data from cell range A1:C4

Press the F5 button, where the "Go To" dialog box will appear.

In that choose the Special Option

The Go To dialog box will appear , in that choose the "Blanks" radio button and Click ok.

The blank cell called C3 is selected and right-click towards the cell, and choose the option "Delete"

The Delete dialog box will open, in that choose the option called "Entire Row".

Click Ok. Here the entire row is deleted and the data present in cell A3 and B3 are deleted too.

In this method, the important data present in row are deleted. Hence the row containing empty cell with data are deleted. Therefore this method is not applicable for all the data.

Key column Method to remove the blank row

This method helps to remove the empty row without losing data. The steps to be followed are:

Enter the data in the worksheet as follows,

Select the whole data present in the worksheet , namely A1:C9 and press Ctrl+Home, and press Ctrl+Shift+End.

To apply filter option, click the Data tab and choose Filter option.

Click the filter option present in the column header of cell A1 and uncheck the "Select All " checkbox. Scroll down to the end of the list , to to check the "Blank" checkbox which is present at the bottom of the list.Click Apply.

Now the filtered rows will display in the worksheet. Select the filtered rows present in the worksheet .Press Ctrl+Home. Then press Ctrl+Shift+End. It displays the range of selected rows which needs to be deleted.

In the worksheet Right-Click any of the selected cell and press "Delete Row" option or click Ctrl+-(minus sign).

The Delete row option displays a dialog box,in that choose the option,"Delete entire Sheet".

Click Ok. It displays the data as follows:

To remove the filter , choose the Data tab, and click Clear Button. The result of the worksheet is as follows:

Here the blank rows are completely removed, but a rows containing containing data and empty cell are not removed.Here the row A3 is not removed and the data is saved.

How to delete the blank rows, if the table doesn't contain key columns?

This method is used,when the worksheet consists of multiple empty cells which is scattered across the particular table range, and the requirement is to delete the rows , which does not contain any data in the column.

The steps to be followed are:

Enter the data in the worksheet is as follows:

In the above data, there is no key column, which determines whether the empty is or not. Hence the helper column is added in the data.

To add the helper column, the blank column is added in the end of the data namely cell D1.

In the cell D2, enter the formula as =COUNTBLANK(A2:C2). Press Enter. It displays the count of cells which does not contains the data.Use the fill handle to cell C6 to display the count of cells which doesn't contain data.

The data is converted to table. The filter option will display in the header column in the table. Click the filter option in the blank cell column header.In the option displayed uncheck all the options, except the number 3. Because the count of cells containing number three is empty row. Click OK.

Then select the filtered rows and press Ctrl+Home , and click Ctrl+Shift+End.It displays the range of selected rows which needs to be deleted. Right click any of the selected row and choose the option "Delete Row" or press CTRl+-(minus sign).The Delete row option displays a dialog box,in that choose the option,"Delete entire Sheet".

Click Ok. It displays the data as follows:

In the worksheet the empty row is deleted. If needed, the helper column can be removed,or the filter option is applied to blank cellsto check the count of one or more blank cells present in the row.

Click the filter option present in the Blank Column, and check the option 1 and 2 and uncheck the value zero.

After applying the result will be displayed as follows:

Removing Blank Rows using Excel's Context Menu

If we need to remove or delete a single row or only a few rows, we can use Excel's context (Contextual menu/ Right-click Menu menu). It is most commonly used to remove blank rows and includes the steps we must perform manually. This method includes the following steps:

• First, we need to locate the blank rows by navigating the sheet.
  
• Next, we need to select the entire row. To select the entire blank row, we can click on the row's header. In the following example sheet, we select the 5^th row that is empty.
  
  We can also select multiple blank rows. We need to click on each corresponding blank row's header while holding down the Ctrl key. This will select the respective blank rows in the sheet.
• After selecting the desired row(s), we need to press the right-click button via the mouse on any respective row header and select the Delete option from the list.
  
  Alternately, we can also navigate the Home tab, click the Delete button and select the Delete Sheet Rows option.

Removing Blank Rows using the Excel's Keyboard Shortcut

The quickest method to perform any specific task in Excel is using keyboard shortcuts. To use the keyboard shortcuts for removing blank rows, we can perform the below steps:

• First, we must select/ highlight any random cell of the desired blank row. We can use the keyboard's arrow keys to navigate the worksheet or click on the corresponding cell using the mouse.
  
• After selecting the cell in the effective row, we must use the keyboard shortcut 'Shift + Space'. This will immediately select the entire row where the cell was selected.
  
  However, we have to use the Ctrl key just like the previous method to select multiple blank rows.
• After selecting the entire row, we can press the keyboard shortcut 'Ctrl + - (minus sign key on the keyboard)'.
  
  After pressing the 'Ctrl + -', the selected row(s) will be removed immediately.

Removing Blank Rows using Excel's Sorting

Another easy method to remove blank rows includes using Excel's sorting feature. The sort feature helps us arrange the data so that the blank rows appear at the bottom of the data. Later, we can decide to delete such rows at once; otherwise, we can ignore them.

We can perform the following steps to delete blank rows by sorting:

• First, we need to select the entire data range. We can click on the first cell of our data range and click on the last cell of the data range while holding the Shift key. This will select the effective range of data. In the following example sheet, we have three blank rows in our selected data range, as shown below:
  
• Next, we need to go to the Data tab on Excel's Ribbon, navigate the Sort & Filter group and click on the Sort option. This will open a 'Sort dialogue box'.
  
• We need to click on the checkbox associated with the option 'My data has headers' in the appearing dialogue box. If there are no data headers in our selected data range, we don't need to mark this checkbox.
  
• After that, we provide data in respective drop-down lists in the dialogue box. We must select the column to sort, the sorting option, and the order. In our example sheet, we sort the data by the first column name, sort on cell values, and select the order 'A to Z'. However, the other sorting orders like 'Z to A' also work well.
  
• After selecting the desired rules in the dialogue box, we must click the OK button. This will arrange all the blank rows of our data set at the bottom, as shown below:
  
  We can select all the blank rows of our data range and delete them at once from the contextual menu as discussed above in this article. This method works well when we have large data sets and several rows in the sheet.

Removing Blank Rows using Excel's Filter

Excel has a Filter feature that allows us to find or arrange any specific data type in the sheet. We can use Filter and find one or more existing blank rows in our sheet and delete them accordingly. To use filters and remove blank rows, we must perform the following steps:

• First, we need to select the entire range of data, including the blank rows.
  
• Next, we need to navigate the Data tab and click the Filter button under the section Sort & Filter.
  
  Alternately, we can apply Filter using the keyboard shortcut 'Ctrl + Shift + L'.
• After completing the previous step, we will see a drop-down arrow/ toggle in each column. These toggles can be used to apply different filters. We need to click on any specific drop-down toggle and choose 'Select All' once to ensure that all the checkboxes are deselected.
  
• After that, we must select the checkbox associated with the Blanks, as shown below:
  After selecting Blanks to filter data, we must click the OK button.
  
• After the data is filtered, we notice that our column headings are visible with only the blank rows. The rows where the row header numbers appear in blue are the existing blank rows of our selected data range.
  
• We must select all the blank rows with blue headers and delete them from the contextual menu or by pressing the Delete key on the keyboard.
  
  After deleting the rows, we can clear filters to get back the data as earlier but without the blank rows. Thus, we must go to the Data tab > Clear button under Sort & Filter.
  
  Apart from the above steps, we can also apply Filter to remove blank rows in a slightly different way in our worksheet. After applying the Filter to our data set, we can deselect the 'Blanks' and select all other checkboxes to keep all other data under the drop-down toggle. It will look like this:
  
  After applying the filters unchecking the Blanks, we will notice that all our blank rows are hidden from the selected range while other data is visible as earlier. Later, we can copy the filtered range using the keyboard shortcut 'Ctrl + C' and paste it on another sheet or another file using the keyboard shortcut 'Ctrl + V'. This way, we only keep our effective data to a new location, removing all the blank rows.

Removing Blank Rows using Excel's Find Command

Excel's Find command can help find and select blank cells from our supplied data within a worksheet. Once the blank cells are located, we can delete the entire row and if they are completely blank, then delete these rows. We can delete multiple blank rows in our worksheet at once by using it.

The method includes the following steps:

• First, we need to select the range of our effective data in any desired column within the sheet. We must not select the entire column. For this reason, we can click on the first cell, and the last cell of the data range in the specific column while holding the Shift key.
  
• After selecting the effective range of data in any column, we must navigate the Home tab, click the Find & Select button, and select the Find option from the list.
  
  Alternately, we can press the keyboard shortcut 'Ctrl + F' to quickly launch the Find menu.
• We must click the 'Options' or 'Advanced' button in the Find window to access more preferences. In the advanced window under Find, we must specify the settings as below:
  
  In the above window, we notice that we keep the 'Find what' box empty to locate the blank cells. After making the changes in the corresponding boxes as displayed, we must click on the 'Find All' button. This will list all the rows with blank cells in the selected column, as shown below:
  
• We can check the data in all extracted rows and select one or more rows to delete or remove. If all the rows are empty, we can select all these rows at once by pressing the keyboard shortcut 'Ctrl + A'.
  
• After selecting the rows, we need to close the Find menu by clicking the Close button. All such cells will still be selected within the sheet.
  
• After that, we can delete all the rows by pressing the keyboard shortcut 'Ctrl + -'. In the next window, we must click the 'Entire row' option and click the OK button.
  
  Also, we use the Delete option from the contextual menu or use other alternate options to delete respective selected rows accordingly.
  

Removing Blank Rows using the Excel's Go to Special Feature

Another method that helps remove blank rows from Excel sheets involves using the 'Go-To Special' feature. Excel's 'Go-To Special' feature is a good option to locate empty/blank rows if our worksheet has many blank rows scattered across a large data set. This unique feature helps us select all the blank cells from the selected column, which we can then check to decide whether to delete them or keep them or their respective rows.

This method includes the following steps:

• First, we need to select any desired column of our data set containing some blank rows. It is necessary to note that we should select only the particular range of our data in the respective column, which contains the dominant data and not the entire column.
  
  In the above example sheet, we only select the column range from A1 to A16.
• Next, we need to navigate the Home tab, click on the Find & Select button and select the 'Go-To Special' option from the list.
  
  Alternately, we can also press the keyboard shortcut 'Ctrl + G' to open Go To menu and then click on the Special button from the window.
• In the Go To Special window, we must click on the circle radio button associated with Blanks and click on the OK button.
  
  This will select or highlight only the blank cells from our selected data range.
  
• After that, we must scroll the sheet and check the respective blank cells' rows to ensure they are entirely blank rows. Later, we must delete the corresponding rows by going to Home > Delete > Delete Sheet Rows from the Ribbon.
  
  These steps will remove blank rows immediately from our selected range of data. We can delete hundreds to thousands of blank rows in our data within a few seconds.

Removing Blank Rows using Excel's Power Query

Excel's Power Query tool can be defined as one of the best options to delete several blank rows with only a few clicks. We can export or open our data inside the power query editor and remove blank rows easily. Excel's power query considers blank rows as null values. We can perform the following steps and remove certain blank rows from our Excel sheet using the graphic view of Power Query Tool:

• First, we must select the range of our effective data and open it in query editor by going to Data tab > From Table/Range.
  
• Next, we must go to the Home tab in the power query editor, click on the Remove Rows and select the Remove Blank Rows option from the list.
  
  In Excel 2013 and earlier versions, we must go to the Power Query tab > From Table/Range > Remove Rows > Remove Blank Rows. In the latest versions of Excel, the Power Query Tool comes preinstalled. However, we must download and install the Power Query tool from the Microsoft official website in Excel 2013 and earlier versions.
• After the blank rows are deleted, we can copy the desired data and paste it back to our original or a new sheet and adjust the formatting accordingly.
  
  The above steps generate the code to select the non-null rows and help us remove rows that contain data with null values.