Using Excel Ranges

Quick reference

• 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.