Contiguous Data

Quick reference

• 00:03 A really important technique in Excel is the ability to quickly create,
• 00:08 what we call, a contiguous data range.
• 00:11 In the last couple of videos that we've looked at,
• 00:14 we've highlighted the importance of not having any blank rows or
• 00:17 blank columns interspersed in our data.
• 00:19 And as you can see here, we've got exactly that problem.
• 00:23 This leads to some weird things because now, if I were to go and
• 00:27 click on a cell in the middle of what looks like a table and click sort.
• 00:31 You'll notice that it doesn't pick up the entire range of data.
• 00:33 And this is one of the challenges that happens if your data range is not
• 00:36 contiguous.
• 00:36 And it effects sorting, it effects filtering, pivot tables and
• 00:40 many other things.
• 00:41 So, we need to find a way to quickly get rid of all the blank rows in this.
• 00:46 And here's the secret,
• 00:47 the fastest way that I can show you in order to make that happen.
• 00:52 What we do is we take a look along our table and
• 00:54 we come to the very last column here, the column after it actually.
• 00:59 What I'm gonna do is I'm gonna press, or type in, a one and press enter.
• 01:04 And now I'm gonna press Cmd+down arrow.
• 01:06 And you'll notice that that will take me all the way down to the last used row of
• 01:10 the worksheet.
• 01:12 So, that's a very quick way to get to the very last row of data, which is good.
• 01:16 Now what I'm gonna do is, I'm gonna hold down my Shift key and
• 01:19 I'm gonna press Cmd+up arrow,.
• 01:24 So, that was Shift+Cmd+up arrow.
• 01:25 And you'll notice that what's happened is,
• 01:28 it's actually selected the entire area from the last cell that I was in,
• 01:32 all the way up to the number one that I put in before hand.
• 01:35 Because it's looking in the first non-blank item.
• 01:39 I'm now still gonna hold down my Shift key and I'm gonna press my down arrow once.
• 01:45 And what that does is it backs off that range by one.
• 01:49 By holding down the Shift key,
• 01:51 I'm working with contiguous range in this particular area.
• 01:55 Now, here comes the trick and
• 01:56 you may remember that we did this way back when on the formula side.
• 01:59 We're gonna write a formula that's gonna go in every cell in this range.
• 02:03 We're gonna type in equals.
• 02:05 You'll notice that it's actually building the formula down the bottom, but
• 02:08 it doesn't matter.
• 02:09 The entire thing is gonna be committed to every cell inside here.
• 02:12 We're gonna say one plus, and I'm gonna up arrow once to D18.
• 02:20 So, it's gonna be one plus the cell above it.
• 02:22 So now, if I press Cmd+Enter,
• 02:24 you'll notice that I get a nice contiguous list of numbers.
• 02:28 So, I'm just gonna go through this one more time really quickly,
• 02:31 coz I really wanna make sure that you understand how this part works.
• 02:34 Put in my one, I'm gonna press Cmd+down arrow.
• 02:38 Okay, so that selected the last reused row.
• 02:41 I'm gonna hold down Shift+Cmd and press up arrow.
• 02:45 I'm gonna let go of command, I'm still holding down shift.
• 02:48 I'm gonna press my down arrow once.
• 02:50 So, that gets me the range I need.
• 02:52 Now, I can type in =1+up arrow to the cell above,
• 02:58 Cmd+Enter to commit it to all the cells in that range.
• 03:03 Now, this is good, but these are still formulas and
• 03:05 they're still gonna change on me.
• 03:06 So, this is going to be a little bit of a problem if I'm not careful.
• 03:09 So, what I should do right now is I should right click on column D and say copy.
• 03:13 And then right click on column D and say paste special and paste values.
• 03:20 And that will lock all those formulas, get rid of all the formulas and
• 03:23 convert them to hard coded numbers.
• 03:24 So, you can see that this is not a formula anymore, it is ten.
• 03:28 Why did I do that?
• 03:29 Well, here's why.
• 03:30 If I go and select any one of the cells inside my original city column now,
• 03:35 and I click on sort, you'll notice that it picks up the entire range.
• 03:39 Which it says, hey, this is a continuous range.
• 03:41 So I'll say, well, that's great.
• 03:41 Let's go and sort this based on the city, A-Z.
• 03:45 Say OK.
• 03:46 And it sorts most of the blank rows into one place.
• 03:49 Not sure why this one is different, but it is, so we'll just delete it.
• 03:53 And we'll get rid of these guys over here as well.
• 03:57 And now, I've got a nice contiguous range.
• 04:00 But my data's not in the same order it was when I started.
• 04:02 You can see that, because the numbers over here are out of order.
• 04:05 But that's no big deal.
• 04:06 I can just come back over here, if I want, if it's important to me and say, sort A-Z.
• 04:11 And now, all of my data is even back in the same order.
• 04:16 And this column's no longer relevant to me.
• 04:18 You can just right click and say Delete.
• 04:21 And now I have a nice contiguous range that I can use for sorting and filtering,
• 04:25 or whatever else I wanna do with it.
• 04:27 But no blank rows and no blank columns,
• 04:29 there weren't any of those in this data sim anyway.
• 04:33 But that's a really quick way to go through and
• 04:35 actually make that range contiguous.
• 04:37 So, remember the Cmd+down arrow gets you to that last used row and
• 04:41 then Shift+Cmd+up arrow will select that entire range, =1+ the cell above,
• 04:46 Cmd+Enter you now have a contiguous range that you can work with.
• 04:50 So, a quick trick for making it work.
• 04:52 And believe me, when you practice,
• 04:54 it you'll be able to do that in ten seconds or less.
• 04:56 It's really not a big thing once you're used to doing it.

Lesson notes are only available for subscribers.