Contiguous Data

Quick reference

• 00:03 One of the things you find when working with Excel's power tools is that many of them require a contiguous range of data to work with.
• 00:11 Now by contiguous we mean that there's no blank rows or blank columns in the data range.
• 00:17 What you see here is something that's quite typical of data that gets
• 00:20 imported from another program, is that there will be blank rows and maybe if it
• 00:24 comes in from say an ASCII format or a print out from another system you'll see that it has some
• 00:29 header rows and they repeat and there's blank rows. Really, really common stuff to see.
• 00:34 So most users that don't know Excel really well, the first thing they
• 00:38 try and do is when they do realize they need contiguous data is they go through and they start
• 00:41 individually highlighting and right clicking and deleting rows. They'll go down and do that all the way through the workbook to try and
• 00:49 get rid of all the stuff that shouldn't be there, all the blank rows and whatnot and build that range.
• 00:53 What I'm going to do is show you a trick here that works on the data range no matter how many rows it is, thousands,
• 00:58 hundreds of thousands or just a few, this will always work.
• 01:02 Actually before we move on, I should just mention here: There's other ways to do this
• 01:07 using GoTo Blanks, for example
• 01:09 that some people might even think is easier. But the method you're about to see has a distinct advantage
• 01:15 over the classic routes of actually going through this task that we're about to do.
• 01:19 Normally if you use GoTo Blanks and you try to filter and delete rows,
• 01:22 if you end up with more than about 8200 non- contiguous - so rows that are not together -
• 01:29 Excel will give you an error when you actually try and delete them.
• 01:33 The method that I'm going to show you now will actually never have that happen.
• 01:37 So this is why we actually go through the extra steps that you might not have seen here before:
• 01:43 To try and come up with a way to make sure that we never run into an error when we're actually trying to go and cull our data out.
• 01:49 Alright, let's get to it.
• 01:50 We go over to the very last column, the first column that has nothing in it.
• 01:55 We type in 1 and hit Enter.
• 01:58 And then what we're going to do is we're going to go press our End key and the down arrow
• 02:03 and that will take us to the very very last row of the spreadsheet.
• 02:07 We're going to press our left arrow once to move over.
• 02:11 So this will get us to the very last row in the spreadsheet for the last column that had data.
• 02:16 Now we press End up arrow.
• 02:19 And that will take us to the last cell with data in it. This is an important cell to be aware of.
• 02:25 Now we're going to go right arrow once.
• 02:28 So this is the first blank cell in the blank column after with our last row of data.
• 02:34 And here's where we go from here. We're going to hold down the shift key,
• 02:39 we're going to press End once and we're going to press our up arrow key once and that will select the entire range
• 02:49 from start to finish.
• 02:51 I'm still holding down shift
• 02:54 I'm going to press my down arrow once and what that's done is it's shortened the range
• 02:58 so it's not including that number one that I put in cell E3.
• 03:02 Now I can let go of the shift key.
• 03:04 And at this point what I'm going to do is I'm going to hit
• 03:07 equals
• 03:09 up arrow once to select, in this case, cell E32
• 03:13 plus one.
• 03:15 So what this is doing is writing a formula to add one to the cell above.
• 03:19 Now my hands are off the keyboard at this point,
• 03:22 I'm going to hold down my Control key
• 03:24 and press Enter.
• 03:26 And what you can see is that it's put an entire range of numbers that are increasing all the way down my spreadsheet.
• 03:34 Okay this is cool, I've got a nice ordered set of numbers.
• 03:37 The next thing I do is I click on the column. Right click.
• 03:41 Copy.
• 03:42 And then we're going to right click and we're going to paste values.
• 03:46 So this solidifies them as a set of numbers.
• 03:49 This is important because it allows us to sort our data back into the order once we do what we're going to do next.
• 03:55 So here's the piece where we actually get rid of all the blank rows. I can click anywhere in here at all and I can sort from A to Z
• 04:03 and that's going to actually put all my blank rows at the top.
• 04:07 Now I can highlight those,
• 04:09 right click, delete them.
• 04:12 Then I can come back over here and I can say you know what I want to sort
• 04:16 by column one,
• 04:19 smallest to largest.
• 04:21 There we go everything's back into the original order.
• 04:25 Right click on column E and delete it.
• 04:28 And that is all it takes to get rid of, no matter how many blank rows are in there. The other thing that's nice about this is once
• 04:34 you've got them all sorted into order there now you can scan through your data list, maybe use the filter commands or something else.
• 04:41 But you can scan through that data list and you can find all the other things, maybe page breaks or in some reports you get
• 04:49 underlines that are made with equal signs or minus signs.
• 04:53 You can get rid of all those because they'll all be sorted together in a group as well.
• 04:57 The other really nice thing about using this technique where you sort first is that you'll never run into the issue where you have too many,
• 05:03 too many ranges that can't be deleted because that'll happen if you have huge, huge long datasets. You can sometimes get into that part
• 05:10 where when you try and filter and delete it it doesn't work that way. So sorting it this way
• 05:15 and then deleting everything and then sorting it back always puts your data back in the
• 05:19 source order and once you actually get used to doing that process it only
• 05:22 takes about 10 seconds to do the whole thing. Believe me if you've done it enough you can make that
• 05:28 muscle memory trigger that fast.

Lesson notes are only available for subscribers.