This lesson is exclusive to members

Microsoft Excel 2016 Bootcamp - Zero to Hero Training

Remove blank rows & columns

Daniel Walter Scott || VIDEO: 1 of 24

Download Exercise Files Download Completed Files

You need to be a member to view comments.

Join today. Cancel any time.

Sign Up

In this video, we want to go through and remove any empty cells, or in this case, any completely empty row.

 

There's two really quick and easy ways. We need to select the whole data sets. So I've clicked at the top left of my set. Come down to the bottom here, I'm scrolling down, holding 'Shift' on my keyboard. And selected this last column here, the whole thing's selected.

 

A real quick and easy way would be just to re-order these. 'Sort' them, let's say we sort them by first name. Up here, where it says 'Sort & Filter' I'm going to say I want to go from 'A' to 'Z'. And it's listed everybody's name from alphabetically A, all the way through, and you notice that those three missing blank cells which were at the bottom here, and hey presto, really just gone, because they are all lined up at the bottom here.

 

Let's say you don't want to re-order it. Another really easy way is to have the whole thing selected. I got it all selected. And there's an option over here. Make sure you're on your 'Home' tab. Over here, where it

says 'Find & Select', go down to 'Go to Special'. There's one in here that says 'Blanks'. Click 'OK'. It's got them selected, see we've got all selected there. And then an option up here, where it says 'Delete'. I'm going to use this little drop down, I'm going to use 'Delete Cells'.

 

It's going to ask me what I'd like to do once it gets deleted. I want to shift the cells up, great. And just moves them all up. And we kind of end up at a very similar place. Blanks are gone, but we haven't re-ordered our list. Either way works.

 

Before we go on to our next video, of tidying up these lists I just want to kind of do one quick little thing.

Let's say that the columns here are in a bit of a bad order, so I want to move 'Finishing Time' right next to the names because, let's say that's the most important for us in terms of this runner's time for Half Marathon.

 

So I could select 'B', right click 'Insert column', then cut and paste it across. There's a quick and easier way, I can click on 'F', selects the whole column here, and I can hold 'Shift' down on my keyboard. And what happens is, if I grab any of these edges down here, black edges here, you can see, it gets a little crosshair. I click and drag it. You can see, you get this little I-beam and it can go anywhere. I'm going to put it just next to the names.

 

That's a quick and super easy way to re-order your columns as well. Hope that was helpful. Let's go into the next video.