🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Using Excel Ranges

Quiz me Quiz Compact player layout Large player layout
Focus video player for keyboard shortcuts
Auto
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Microsoft Excel - Pivot Tables course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

Facebook Twitter LinkedIn WhatsApp Email

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Choosing between building quick and dirty PivotTables or future-proofing PivotTables for future updates.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Using Excel ranges
11.6 KB
Using Excel ranges - Completed
21.7 KB

Quick reference

Topic

Three different methods for holding PivotTable source data in Excel.

Where/when to use the technique

Choosing between building quick and dirty PivotTables or future proofing PivotTables for future update.

Instructions

Quick and Dirty

Method
  • Select a cell inside the data table
  • Go to Insert tab and choose PivotTable
  • The “Table/Range” will be an absolute cell reference
  • Land it in the desired location and build your PivotTable
Benefits/Drawbacks
  • Quick to set up
  • Works well for one time use
  • Painful to update as you need to modify the PivotTable source data every time the range grows

Using Excel Tables

Method
  • Make sure your data table is an official Excel Table
    • Select a cell inside the table, go to the Home tab and choose Format as Table
  • Ensure the table has a good name
    • Select the table, on the table tools tab change the name (top left) to something logical
  • Go to Insert tab and choose PivotTable
  • The “Table/Range” will be the table’s name
  • Land it in the desired location and build your PivotTable
Benefits/Drawbacks
  • Quick to set up
  • Data table automatically grows (vertically and horizontally) as you add data
  • PivotTable will never miss another piece of data
Login to download
  • 00:04 In this video, we're gonna look at two different ways that we can base our
  • 00:08 PivotTables off of data that's housed or landed in an Excel worksheet.
  • 00:13 The first way that we're gonna look at is what I would consider the quick and
  • 00:16 dirty method for building a PivotTable.
  • 00:18 That's where we don't really take a whole lot of care.
  • 00:20 We kinda look at it as a one-time use scenario,
  • 00:23 because it has some issues with updates.
  • 00:25 And we'll deal with that one first and
  • 00:27 then we'll go in to something that's a little bit more robust.
  • 00:29 So in order to get started with this, we look, we have a table of data, there's no
  • 00:33 blank rows, there's no blank columns in it, and it's x number of rows long.
  • 00:36 It doesn't matter how long, because the PivotTables are quite quick.
  • 00:39 What we're gonna do, is we're gonna click somewhere in the data,
  • 00:42 we'll say Insert > PivotTable.
  • 00:43 And I'm gonna land this on an existing worksheet right here in I3 and say okay.
  • 00:50 And we'll just throw class down the side and we'll throw, let me see here,
  • 00:54 units in the middle here, and maybe POSchithour across the top.
  • 00:58 So we've built a nice quick PivotTable here that shows us the number of units,
  • 01:03 maybe we'll even throw a category underneath so
  • 01:05 there's a little bit more to look at.
  • 01:06 There we go.
  • 01:07 Now, the challenge with this is that it comes down to updates.
  • 01:12 Watch what happens when I go all the way down to the very end of this data.
  • 01:16 And I'm gonna say we're gonna be in the 11 o'clock hour, and
  • 01:20 I don't really care what the chit number is, we'll just go and copy the one above.
  • 01:25 And we're gonna go in and put in something again, a ridiculous 1,000 units of sales.
  • 01:31 And this'll be worth $25.
  • 01:32 This is gonna be Coffee, and it's Non Alcoholic Beverage and Food.
  • 01:37 So same information, but we've got 1,000 units now.
  • 01:40 So now we're gonna go back, and we're gonna say end and
  • 01:42 up, and we're gonna refresh our PivotTable.
  • 01:47 Refresh.
  • 01:48 And as we look over on our PivotTable, now,
  • 01:51 we don't see our 1,000 units anywhere.
  • 01:55 Why is that?
  • 01:56 Let's go look at what the source data is for this pivot.
  • 02:00 If we go into the analyze tab, we can click the change data source button.
  • 02:05 And it comes back and it says well, your data range goes from A1 to G35.
  • 02:10 Okay, well this is interesting, let's click on this, and
  • 02:15 we'll just go from A1, all the way down to G35 is where it was.
  • 02:21 And this is the problem.
  • 02:22 You see how that last row that we added is outside of the data range area?
  • 02:26 So this is a bit of a challenge.
  • 02:28 Every time we add new data rows we'd have to go back through this interface and
  • 02:32 modify the setup for the PivotTable.
  • 02:34 And that's no good.
  • 02:35 We don't wanna do that.
  • 02:36 So, we could though.
  • 02:37 We could certainly go and make this down to row 36.
  • 02:41 And say, OK to this, and then when we go back up to our PivotTable now,
  • 02:45 we can see that those 1,000 units are in there.
  • 02:49 So this is a double hit on that whole refresh thing.
  • 02:51 Number one you got to refresh and number two you have to go and
  • 02:53 change the range to make sure it keeps up to date.
  • 02:55 That's no good.
  • 02:56 Lets go and take a look at data range 2 here.
  • 02:59 Similar story, same form, this time we're gonna do something different.
  • 03:03 Gonna click somewhere in the table, and we're gonna say, Format as Table.
  • 03:07 And we'll go with a nice beautiful orange this time.
  • 03:10 And it's gonna say, where's the data for our table?
  • 03:13 So you went to G35 no problem.
  • 03:16 The next thing I'm going to do is while I'm here, I'm just gonna make it,
  • 03:19 it's a best practice of mine to come back and
  • 03:21 just change this to give it a name in the top left hand corner, a pivot source.
  • 03:26 This becomes really important when you start working multiple pivot tables.
  • 03:29 Again now that we've named the table, it's much prettier.
  • 03:32 We can go back to Insert > PivotTable.
  • 03:35 And you'll notice that the table range pivot source is what I called
  • 03:38 that particular table.
  • 03:39 I'll just put this on an existing worksheet as well, I2.
  • 03:43 And we'll build the same PivotTable.
  • 03:44 We'll put Class, we'll put Category, we'll put Units down in the Values area and
  • 03:49 we'll chio or POSChitHour across the top.
  • 03:51 And, we'll just get rid of this PivotTable list so we can see that indeed,
  • 03:54 it's been built up.
  • 03:55 Now, let's try the same thing.
  • 03:57 All the way down to the end.
  • 03:58 10 o'clock hour.
  • 04:02 We'll go copy the chit number from up above.
  • 04:05 We'll put in our ridiculous 1,000 units and whatever it was,
  • 04:09 $25, coffee and non-alcoholic beverage and food.
  • 04:13 Something you can see right away is that our table has expanded to pick up
  • 04:17 this next row.
  • 04:18 This is really, really important because now,
  • 04:20 when I go back to my PivotTable and I right-click and
  • 04:22 refresh it, you can see that those 1,000 units just came in right away.
  • 04:26 I don't have to go back and tinker with the range at all.
  • 04:30 So here's the thing, when you're using a table, they're very quick to set up,
  • 04:35 and the tables automatically grow, both vertically and horizontally.
  • 04:39 If I were to go and say let's insert a new column in here, and we'll say column 1.
  • 04:44 If I go back and right-click and refresh this now, and
  • 04:47 now I show my field list again, you'll notice that column 1 shows up too.
  • 04:52 So they both expand both vertically and horizontally, which is really,
  • 04:55 really cool, so your PivotTable will never miss another piece of data.
  • 04:59 The other method,
  • 05:00 where we just build it against a table of data is very quick to set up.
  • 05:04 It works well for one time use but it's painful to modify.
  • 05:07 So I would definitely recommend that you look at tables as the landing, or
  • 05:11 the starting point for your PivotTables.

Lesson notes are only available for subscribers.

Source Data Best Practices
05m:17s
Preparing Source Data
05m:34s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement