About this lesson
Learn tricks for consolidating your data so it's vertically contiguous (without blank rows) – for Tables, PivotTables and Charts.
Download this lesson’s related exercise files.Contiguous Data.xlsx
17.7 KB Contiguous Data - Completed.xlsx
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.
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!
- 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.