How to Delete Blank Rows in Excel

In an Excel spreadsheet, there could be blank rows in the data set that can make navigating around the data difficult and Excel uses blank cells and rows to determine the ranges so it can also interrupt the formulas.

But, users can quickly delete these blank rows as Excel has several ways to delete the blank rows We have quick and easy steps below on how to delete the blank rows in Excel.

Delete Blank Rows using the Filter Option

The filter option for deleting the blank rows is one of the easiest ways to remove or delete the blank rows from your data set.

1. First, select the entire data range including blank rows and then go to the “Data” tab and click on the “Filter” icon under the “Sort & Filter” group.
2. Or, select the entire data range including blank rows, and then press shortcut keys “Ctrl + Shift + L” to apply the filter.
   
3. Once you are done with any of the above steps, you will get the filters added to the headers of the data range.
4. Now, click on any of the applied filter buttons and then uncheck the “Select All” option from the filter drop-down menu to unselect all the pre-selected options then checkmark only the “Blanks” option and then press OK.
   
5. At this point, you will get the blank rows filtered and you just need to select them all and right click and select “Delete Row” or press “Ctrl + (-) keys and your blank rows will get deleted.
   
6. In the end, click on the applied filter button again and checkmark “Select All” or simply press the “Ctrl + Shift + L” keys to unselect the filter and you will get your data sorted.
   

When you delete any row, Excel deletes the entire row of the spreadsheet means even if you have data only in Columns A to D, Excel will delete the whole row from Column A to the end of the spreadsheet columns and you can lose the value if any in the cells in that row outside of your data set.

So, it is recommended to convert your data set into table format using the “Ctrl + T” keys as in table format Excel deletes the rows within your selected table data only.

Delete Blank Rows using the Sort Option

• First, select the entire data range including blank rows, and go to the “Data” tab.
• After that, click on the “Sort” icon under the “Sort & Filter” group and you will get a “Sort” dialog box opened.
• Now, Select the data sorting selections and click OK. (In this example we have selected the data sort by “Months”)
  
• At this point, you will get your data sorted with blank rows moved at the end of the data set.
  

Delete Blank Rows Manually

Deleting the blank rows manually is a good option to choose when you have small data set with a few blank rows and where you can easily identify those blank rows on the screen.

• Select the blank rows one by one and right-click and select delete to delete the selected blank row.
• Or, select all the blank rows together and then right-click and select delete to delete all the selected blank rows in one go.

You can use the shortcut keys Shift + Space to select the row and then Ctrl + - keys to delete that selected row.

Delete Blank Rows Using Go TO Special

• First, select the entire data range and go to the “Home” tab and then click on the “Find & Select” icon.
• After that, click on the “Go To Special” option from the drop-down list.
  
• Once you click on “Go To Special” you will get the “Go To Special” dialog box opened.
• Now, select the “Blanks” option radio button and click OK and you will get the blank rows selected.
  
• In the end, right click and select the “Delete” option and you will get the “Delete” dialog box options which will ask you to select the shift cells option.
• Select the “Shift cells up” and Click OK and you will get your blank rows deleted.
  

Note: Go to the Special option and selects blank rows as well as blank columns altogether, so if your data set has any blank column within any row, it will also remove that blank column and replace it with another cell value based on the shift cells option which could mess your data.