How To Delete Blank Rows in Excel

Personally I always get annoyed by Blanks Rows or cells while importing data from an Excel to CSV file or a text file. I guess it’s a very common problem which most of us have faced while trying to import data from excel sheet to text file or zip file. So today in this tutorial I am going to throw some light on how to delete blank rows in Excel. There are many methods to delete blank rows in an excel depending on the data in spreadsheet.

Method 1: Delete Blank Rows Individually

This is a very simple and self explanatory method. However it could be used only when you have a small range of data for deletion.

1. Please follow below steps to delete cells using this method.
2. Make a copy of your spreadsheet for backup, so that in case of any unfortunate event you can still recover your data.
3. Open the MS Excel Spreadsheet from which you want to delete blank rows.
4. Hold Ctrl Key and Select the blank rows that you want to delete one by one.
5. After you have made the selection, release Ctrl key.
6. Right Click and Select Delete.

Method 2: Delete Blank Rows by Arranging data

This method is also very simple. All you have to do is to sort the rows alphabetically in ascending or descending manner. All blank rows will appear in an ordered fashion and now you could easily delete the blank rows.

Although this method is very quick and easy to implement still its not recommended for those users whose data is ordered in a particular fashion and cant be re-ordered to eliminate blank cells.

Method 3: Delete Blank Rows where in Blank Cells are in same column

This method is useful if the blank rows that you want to delete are present in same column. Please follow below mentioned steps to delete blank rows in excel:

1. Make a copy of your spreadsheet for backup, so that in case of any unfortunate event you can still recover your data.
2. Open the MS Excel Spreadsheet from which you want to delete blank rows.
3. Select the area or rows that contain the blank rows which you want to remove. Example: Suppose your data is populated from Row 1 to Row 25, then select Row 1, hold Shift Key and scroll over to Row 25.This way all your data from Row 1 to Row 25 will be selected for deletion of duplicate cells.
4. If you want to select the whole spreadsheet then hit Ctrl + A Key twice.
5. Now go to Home in the above Ribbon and select “Find and Select” option.
6. Instead of following above step you could also use a key board shortcut, Press F5, this will open “Go to” applet. Select Special Button. Now goto Step 8.
7. Select “Go to Special” from the dropdown.
8. Now check “Blanks” in the “Go to Special” window.
9. Hit “Ok” after selecting Blanks option.
10. Now go back to the Home ribbon available at Top.
11. Select “Delete” option.
12. Select “Delete Sheet Rows”.
13. This tip will save lot of time if you are working with large data.

Method 4: Delete Blank Rows using a shortcut

This method is not known to many people. Its very quick and easy to implement on both small and large files. Please follow below mentioned steps to delete blank rows in excel using this shortcut:

1. Make a copy of your spreadsheet for backup, so that in case of any unfortunate event you can still recover your data.
2. Open the MS Excel Spreadsheet from which you want to delete blank rows.
3. Select the area or rows that contain the blank rows which you want to remove.
4. Now Press “F5”, Go to Window will be displayed.
5. Now Press “Alt + S”, Go to Special Window will be displayed.
6. Now Press “K” and Hit Enter. Using this Blanks options will be selected from “Go to Special Window.
7. Now press “Ctrl + U” and Hit Enter, Rows will be deleted.

In short we used following shortcut key to delete blank rows in excel “F5 > ALT+S > K > Enter > CTRL+U > Enter”

This was all about the various methods to delete blank cells in excel. However please note that if you want to keep some blank cells then those also would be deleted using these methods. So please keep a backup copy always with you before starting this process or you could also use “Ctrl + Z” key to undo your last action on the worksheet.

Please share your feedback, concerns and issues in the comments section below.

Cheers!

Article by Ankit

Ankit is the founder of Geek Revealed. He is tech Geek who loves to sit in front of his square headed girlfriend all day long. :D   You can find Ankit on FaceBook or Google Plus