How to delete blank rows in Excel

Method 1: Remove Blank Rows by Using Excel Find Functionality:

In this method we will take the advantage of Excel Find feature. Below is a step by step tutorial for doing this:

Employee Table Example For Deleting Blank Rows

  • First, select your data set and hit Ctrl + F keys to open the “Find and Replace” dialog.

Find and Replace Dialog in Excel

  • Next, Click the “Options” button, and select “Values” from the “Look in” dropdown. Let the “Find What” textbox remain blank and click “Find All”.

Selecting-all-blanks

  • After this the “Find and Replace” dialog will display all the blank cells. Click any one record and press Ctrl + A to select all the blank cells.

Blank Rows Selected

  • Now, Close the “Find and Replace” dialog and without clicking anywhere else navigate to ‘Home’ > ‘Delete’ > ‘Delete Rows’.

Data after removing the blank rows

  • This will delete all the selected rows.

Method 2: Eliminate Blanks by Using Excel Filter Functionality:

In this method we are going to use Excel’s Auto filter functionality to delete the blank rows. Follow the below steps to use this method:

Employee Table Example For Deleting Blank Rows

  • First of all select the range from which you need to remove the unfilled rows.

Sort and Filter Option in excel

  • Navigate to “Home” > “Sort and Filter” > “Filter” or alternatively you can also press the Shift + Ctrl + L keys to apply a filter.

Select only blanks

  • After this select a column, click the filter dropdown, uncheck all the values excepting “Blanks” and click “Ok”.

Delete blank rows in excel

  • Now, with all the unfilled rows selected, navigate to ‘Home’ > ‘Delete’ > ‘Delete Rows’. Finally remove the filter and you will see unfilled rows eliminated.

                                           Brought to you by

Feedback and Knowledge Base