Contiguous Data

Quick reference

• 00:04 Looking at the data in this worksheet,
• 00:06 you can see that it's not in a good format right now.
• 00:09 It's not gonna be working very well with a sort or a filter or a lot of other
• 00:14 powerful tools that Excel has, because the data range is not what we call contiguous.
• 00:20 What we mean by contiguous is a rectangle that goes from the top-left corner to
• 00:25 the bottom-right corner of your data, with no blank rows or columns anywhere in them.
• 00:30 And as you can see here, we got blank rows scattered throughout our data.
• 00:34 So what I'm gonna do right now is I'm gonna show you the tricks that I use to
• 00:38 actually clean up my data quickly.
• 00:40 Now you'll have to bear with me on these and
• 00:43 watch carefully because there's a lot of steps to what we're gonna do, and
• 00:46 it involves flying around the worksheet all over the place.
• 00:49 So here's what's gonna happen.
• 00:51 Right beside the last column header here,
• 00:54 in the blank cell, I'm gonna type in a 1 and press Enter.
• 00:59 What I'm gonna do next is I'm gonna go to the last row of the worksheet.
• 01:02 So I'm gonna press End + down arrow.
• 01:04 And that'll take me down to row 1,480, 576.
• 01:08 I'm then gonna arrow once to the left and then press End Up arrow.
• 01:16 And that will take me to the last row of data in my spreadsheet, which is C19.
• 01:20 I'm then gonna arrow one key to the right.
• 01:24 So I'm now immediately below where I put in that number 1.
• 01:29 Beside my table.
• 01:31 At this point, I'm gonna hold down the Shift key.
• 01:34 I'm gonna press End, I'm still holding down Shift,
• 01:37 I'm gonna press my Up arrow key.
• 01:39 And that's gonna select the entire range, all the way up to the number 1.
• 01:44 I also want you to recognize I am still holding down my Shift key
• 01:48 because I'm gonna arrow down once.
• 01:50 Because I don't really want to select the cell that has the 1 in it.
• 01:54 And now, I'll let go of my Shift key.
• 01:56 All right? So we started from the top, from number 1,
• 01:59 we pressed End + down arrow Left arrow once, End, Up arrow,
• 02:05 arrow to the right once, and then we held down the Shift key while we pressed End,
• 02:10 up arrow, down arrow, and that's when we let go of Shift.
• 02:15 Why have I done this?
• 02:16 It's to select this range easily without having to reach for
• 02:19 the mouse because this range could be 19 rows long or it could be 19,000 rows long.
• 02:23 It doesn't matter, whatever's happened here,
• 02:26 I've still managed to select the same range.
• 02:28 What I'm gonna do right now is I'm gonna type in a formula.
• 02:31 I'm gonna go with = 1 +.
• 02:35 The cell above.
• 02:36 Now, notice that when I'm doing this, it's doing it from cell D 19.
• 02:41 An d this is because this is what we consider the active cell, this white cell.
• 02:46 We have one big selection, but we only ever have one active cell, and
• 02:50 it's the white one.
• 02:51 So this is the point from which all of my formula is going to be written right now.
• 02:55 This is, and this is a relative formula, so it's saying equals one plus the cell
• 02:59 above, but I'm gonna commit it all the way up this range.
• 03:02 An d the way I do that is hold down the Ctrl key, and press Enter.
• 03:08 And what I'll get is a list in numerical order of all my lines of data.
• 03:13 I'm now gonna right click on it and say Copy.
• 03:17 And then I'm going to right-click again and
• 03:20 say Paste Special Values, that's the clipboard with the 123.
• 03:25 What I have now is I have actually got a contiguous data range,
• 03:29 even though it doesn't look like it.
• 03:31 But I have a contiguous data range that has a numeric order assigned to it.
• 03:36 This is important because now I can click on any cell inside here.
• 03:40 And I can go to Data, and I can say sort data zed, and it will sort my region.
• 03:45 Although it did pick up my header, so that's not so good.
• 03:48 Let's go and undo this, and do a little bit more advanced sort.
• 03:52 We'll go to sort, say you know what, my data has headers.
• 03:57 Now it will recognize that I do actually have city, state, gasoline.
• 04:01 So let's just sort it by city, in alphabetical order.
• 04:06 At this point, I now have the ability to do the sorts and
• 04:08 filters I need in order to get all the blank rows out.
• 04:11 So I can now go and say let's delete these blank rows, they aren't important anymore.
• 04:16 But if it was important Important what order my data was in,
• 04:19 I've got a numeric index so that I can resort it now.
• 04:22 So I can go back and say great, let's go with, let's hit Cancel on that because I
• 04:26 selected a specific set of cells, we don't wanna do that.
• 04:29 We'll hit Cancel.
• 04:31 Click any cell inside.
• 04:32 We'll say Sort.
• 04:34 Excel remembers that I had headers.
• 04:36 So this time I'm gonna sort by column 1.
• 04:39 From smallest to largest,
• 04:40 that puts my data back into the original format that it was in.
• 04:44 I can now say hey, I don't need this column anymore, let's delete it.
• 04:49 And I've cleaned up my data into a nice tabular format to allow proper sorting and
• 04:54 filtering and all the other tools that I may want to use.

Lesson notes are only available for subscribers.