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

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.