Locked lesson.
About this lesson
Learn tricks for consolidating your data so it's vertically contiguous (without blank rows) – for Tables, PivotTables and Charts.
Exercise files
Download this lesson’s related exercise files.
Contiguous Data.xlsx17.7 KB Contiguous Data - Completed.xlsx
17.7 KB
Quick reference
Topic
Contiguous data.
Description
Tricks to quickly build a contiguous range of data.
Where/when to use the technique
Often when you import data from another application you’ll find that it contains blank or garbage rows. Since Excel’s most powerful tools require contiguous ranges to work with, we need a way to quickly convert our data to this format.
Instructions
Building a contiguous range
- Find the cell that contains the header row for your last column of data and select it.
- Press your right arrow key once to select the blank column beside it.
- Type a 1 in the cell and press Enter
- Press Command + Down Arrow to go to the last row of the worksheet
- Hold down SHIFT, then Command + Up Arrow
- Still holding down SHIFT press the Down Arrow key once
- Let go of the SHIFT key
- Type =1+ and press your Up arrow key once
- Hold down Command and press Enter (you should now have a set of increasing numbers in your column
- Select the column header with your mouse
- Right click and copy the column
- Right click the same column and choose to PasteSpecial --> Values
- You have a contiguous range!
Implications
- You can now sort and filter your data
- You also have a numeric column at the end you can use to re-sort the data into its original order when you’re done
- After you’re finished manipulating your data, you can delete the sort column.
- 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.