Locked lesson.
About this lesson
Power Query is the best tool for consolidating your data so it's vertically contiguous (without blank rows or other garbage) – for Tables, PivotTables and Charts.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Contiguous Data - Completed.xlsx12.3 KB Contiguous Data - Extra Practice.xlsx
17.7 KB
Quick reference
Topic
Contiguous data.
Description
Tricks to quickly build a vertically contiguous range of data (without blank rows).
When to use
Often when you import data from another application you’ll find that it contains blank or garbage rows. Because Excel’s most powerful tools, such as Tables, PivotTables and Charts, require contiguous ranges to work with, we need a way to quickly convert our data to this format.
Instructions
- 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 End then the down arrow to go to the last row of the worksheet
- Press the left arrow key once
- Press End then the up arrow key to go to the last row with data in it
- Press your right arrow key to get to into the column you placed a 1 within
- Hold down SHIFT, then press END, then press the Up arrow key
- 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 CTRL 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 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.