How to Delete Blank Rows in Excel

When you are dealing with Microsoft Excel spreadsheets, very often you receive spreadsheets with lots of blank rows between actual records and you have to clean them before performing actual work on that spread sheet. I have found 3 easy and most efficient way to delete blank rows in Excel. They helped me a lot and sufficing my needs till date :).  Below are the steps how you can perform this activity.

Option 1: By using Find Functionality

Below are the steps that we have to take to find, select and delete the blank rows using Find and Replace functionality of Excel.

1. Open the excel sheet which have records with blank rows between 2 records (Please make a copy of spreadsheet if you are working on actual data)
2. Select the columns from top from which you want to select and delete blank records.
3. Hit “Ctrl+F” from your keyboard.
4. This will populate Find and Replace dialogue box.
5. Expand the available options by clicking on the “Options >>” button.
6. Ensure “Find what” field is blank and in “look in” Select “Values”.
7. Click on “Find All”.
8. This will show all the blank fields on the “Find and Replace” dialogue box.
9. Select all the records by pressing “Ctrl+A” on our keyboard or by clicking on the column heads on the “Find and Replace” dialogue box.
10. Navigate to Home > Delete > “Delete Sheet Rows”. Please ensure you don’t loose the selection and you don’t click anywhere else on the sheet, otherwise you will have to start all over again.
11. You are done.

Option 2: By using Filter Functionality

We can also find and delete blank rows using filter functionality of excel. Below are the steps that we have to take to find, select and delete the blank rows using Filters.

1. Open the excel sheet which have records with blank rows between 2 records (Please make a copy of spreadsheet if you are working on actual data)
2. Ensure you have headings for all the columns.
3. Select the columns from top from which you want to select and delete blank records.
4. Navigate to Home > Sort & Filter > Filter. This will apply filter on top of the records.
5. Click on the filter on the column name and select only “Blank” this is usually at the bottom, and click on “OK”.
6. This will select all the blank records. make sure other columns are blank too, before making delete operation.
7. Navigate to Home > Delete > “Delete Sheet Rows”. Please ensure you don’t loose the selection, otherwise you will have to start all over again.
8. You are done.

Option 3: By Using Find and Select

Below are the steps that we have to take to find, select and delete the blank rows:

1. Open the excel sheet which have records with blank rows between 2 records (Please make a copy of spreadsheet if you are working on actual data)
2. Select the columns from top from which you want to select and delete blank records.
3. Click on “Find & Select” and select “Go To Special”.
4. Click on “Blank”, Make sure its get selected and click o “OK”
5. This will select all the blank cells at once in the spreadsheet under selected area.
6. Navigate to Home > Delete > “Delete Sheet Rows”. Please ensure you don’t loose the selection, otherwise you will have to start all over again from step 2.
7. You are done.

That’s all from me on this. There might be some other easy way that can be used. Kindly let us know if you have more options.