Saturday, January 20, 2018

How to Delete Blank Rows in Excel

This is the text version of a YouTube Live session of Go Ask Debbie: How to Delete Blank Rows in Excel.

I thought I'd just do a quick tip here to show people something that I've been often in Excel.

That is a lot of times when you start using data in Excel, you want to add formulas.

When you have a formula where there are blank rows, Excel stops at the first blank row, so your formula has to be manipulated a little bit - it's not so easy to calculate formulas.

So really when you're working with data in Excel, you want to get rid of all these blank rows.

They're not necessary. You may think that it looks nicer for formatting, but when you're trying to use Excel functions, really what Excel is for in calculating and manipulating data, you want to get rid of these rows.

So if the blank rows could be highlighted within your data, it's easy with a small list.

But if you have a large spreadsheet, Excel will recognize the blank rows without highlighting your data set.


But I'm going to go ahead and highlight this data set here.


















Then you're going to go to your Home Tab,

and then over here on the right, you're going to click on the Find and Select button,

and then this time we're going to select Go to Special... 




a pop-up window will appear 

and you're simply going to select Blanks on the left-hand column and then hit OK.




You'll notice then that Excel highlights, in gray, all of the blank rows.




You'll see the first cell A4 is highlighted in white because that's the active cell.

But we really want to recognize these rows that are highlighted in gray.

These are truly all of our blank rows that we see on the screen here,

and to Delete those all in one swoop, we're just simply going to hit our CTRL key and then the Minus (-) sign.

Excel is going to open up another Dialog Box asking us to verify what we'd like to delete.


We want to delete the Entire Row for all these blank rows and then hit OK.




You'll see all the blank rows have disappeared, and now when I go to use Excel's function for Auto Sum, Excel knows exactly what I'd like to calculate.




There are no blank rows that are stopping the formula from calculating properly.

So again when we do this we're going to


  1. Highlight our data set from our Home tab.
  2. Click on Find and Select
  3. and then Go to Special... 
  4. Click on Blanks
  5. Hit the OK button
  6. and then CTRL - (CTRL and Minus)
  7. and select Entire Row
  8. and OK


It really is that simple.

So now imagine if you've got a spreadsheet with tens of thousands of rows worth of data and you have 500 empty rows.

This makes it a lot faster than manually searching through blank rows and deleting them one at a time.

Thanks for taking this session of Go Ask Debbie with YouTube Live.

If you like what you see today, please Like the YouTube video.

Please feel free also to SUBSCRIBE to my channel to receive tips like this, and more, on a regular basis.

Please COMMENT if you have any other ideas for tutorials that you'd like to see on this channel.

Thanks for taking Go Ask Debbie and remember, be sure to SUBSCRIBE to my channel to continue seeing tips like this.

Thanks and everyone have a great day!

Watch the video here:






No comments:

Post a Comment

How to Delete Blank Rows in Excel