How To Delete Blank Rows In Your Data Range In Excel

You may find several blank rows within your data range that you need to delete. Depending upon how big your data is, there are several options to delete those rows. Let’s take a look at how to do that in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here we have a small range of data that has several blank rows:

72715-1

With such a small number of rows, I can easily select them by holding down my Ctrl key and clicking on the row numbers:

72715-2

From here I have several options to delete the blank rows:

1) right click on a selected row number and click on Delete
2) use the keyboard shortcut Ctrl + “-” (Ctrl key and the minus key from the keypad)
3) from the Cells group of the Home tab, click on the “Delete Sheet Rows” option under Delete:

72715-3

Any of these will delete the blank rows. But if I have several blank rows scattered over hundreds or thousands of rows of data, manually selecting the blank rows would be tedious and time consuming. There is a better solution!

First select your data range:

72715-4

Next, hit the F5 function key which will bring up the Go To dialog box:

72715-5

Next, click on the “Special” button, or use the keyboard shortcut Alt + S

Then, click on “Blanks” or use the keyboard shortcut Alt + K

Then click OK. This will select all the blank cells in your selected range:

72715-6

Now, from the Cells group of the Home tab, click on the “Delete Sheet Rows” option under Delete, or use the keyboard sequence Alt H D R. This will delete all the rows with blanks and leave you a nice, clean data range:

72715-7

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , ,

No comments yet.

Leave a Reply