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.
Multiple versions of this lesson are available, choose the appropriate version for you:
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.
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 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!
- 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
Lesson notes are only available for subscribers.