Using Excel Ranges

Quick reference

• 00:03 In this video I want to look at two different ways to connect
• 00:07 a pivot table to data that lives inside an Excel worksheet.
• 00:11 The first one that we're going to look at is actually the way that I
• 00:14 do not recommend you use.
• 00:16 Now, let's take a look at our data before we actually jump into that.
• 00:19 It looks like it's got a good header row,
• 00:21 there's no blank row between the header and the data, so we're going to assume
• 00:24 that there's no blank rows in the dataset at this point in time as well.
• 00:27 The data looks consistent down the actual columns, which is good.
• 00:31 And it's got, we don't know how many rows, we haven't looked, but you know what?
• 00:35 Pivot tables work really quickly to summarize anyway, so
• 00:37 let's not worry about that right at the moment.
• 00:40 What I'm going to do is select the cell somewhere inside my data,
• 00:44 we'll go to Insert, we'll choose PivotTable, and
• 00:46 you'll notice it picks up the data range from A1 to G35, no problem.
• 00:50 Let's drop a PivotTable right on an existing worksheet.
• 00:53 I'll put it right over here in cell I2 and say, OK.
• 00:58 Now that I have this, of course, I can configure my PivotTable.
• 01:00 I'm going to go and say, let's put Category on Rows.
• 01:04 Why don't we go and put the units on values, and let's put the hour on columns
• 01:10 and then I'll dismiss the field list so that we can see what we've done.
• 01:15 And there we are,
• 01:15 we have our nice little pivot table, so everything looks like it's working great.
• 01:18 But the problem is what happens when we go and we update things.
• 01:23 So here's what's going to happen.
• 01:24 I'm going to click in my data table and press end down arrow, and
• 01:28 I'll go down one more row.
• 01:29 And let's add a new record for the 11 o'clock hour.
• 01:32 And we'll put in a random number for the chit number, there we are.
• 01:36 And we're going to sell, let's see,
• 01:39 how about 1,000 units of a $25 sales item for coffee.
• 01:44 And this will be a non-alcoholic beverage for food, and there we are.
• 01:48 So we've added a new record to our data set.
• 01:51 I'm going to now press Ctrl+Home, and go back up to the top.
• 01:54 And we should be able to see this fairly easy in our non-alcoholic beverage when we
• 01:58 go and add this for the 11 o'clock hour, we've added 1,000 units.
• 02:02 We should hope that those will show up, so we're going to say right-click and
• 02:05 we'll refresh our pivot table, and nothing shows up.
• 02:09 And this is the concern that I have with the method of creating a pivot table
• 02:12 against a range.
• 02:13 Why, well, to figure this out,
• 02:15 we're going to go to the pivot table tools Analyze tab.
• 02:18 And again, in earlier versions of Excel,
• 02:21 this will actually have a pivot table tools up
• 02:23 in the top with analyze under here's a single button moving in Office 365.
• 02:28 Microsoft is moving to this inline tab naming, so
• 02:30 it's the same thing just has a slightly different representation here.
• 02:34 On the Pivot Table Tools analyze, you will find a button on here that actually allows
• 02:39 you to go back and change the data source.
• 02:42 When I click on that,
• 02:43 you'll notice that it tells me that the range of data is from A1 to G 35.
• 02:47 Well, the challenge with this of course, is that when I scroll down,
• 02:52 it now goes to A36.
• 02:54 So I need to update this particular piece of information to go to 36,
• 03:00 and now when I click OK, you'll notice that it actually brings in my new values.
• 03:04 That's great and everything, but I don't really want to have to go back and
• 03:08 change the data source on a regular basis.
• 03:11 So here's what I would prefer that you do instead.
• 03:13 We'll start again with data range 2 over here.
• 03:15 And what we'll do is we'll check, does it look like it's in good form,
• 03:19 good header row, no blank rows, consistent data, excellent.
• 03:23 So we're going to again go to Home > Format as Table and make a color choice.
• 03:28 I'm going to go with blue.
• 03:29 It will pick up the range, and notice that this time we have a checkbox flag for
• 03:34 headers because we know that point of sale chit hour is text,
• 03:38 the next is a value, so it can identify that for us.
• 03:41 We'll say OK.
• 03:43 The next most important thing you should do is go and rename your tables.
• 03:46 As soon as you create your table,
• 03:48 you'll be on the Table Tools Design contextual tab.
• 03:51 Right over here on the left-hand side,
• 03:52 you'll see that you can actually set this up and give it a name like Transactions,
• 03:56 which is a great name for a table of transactions.
• 03:59 Why this is important?
• 04:00 Watch this now.
• 04:01 Click a single cell inside Insert > PivotTable.
• 04:06 Notice that it doesn't give you dollar signs anymore.
• 04:08 It's actually referring to the name of the table and this is super, super important.
• 04:12 Let's click Existing Worksheet and we'll drop this pivot table right into cell I2.
• 04:17 And once again, we'll put units on our values,
• 04:20 power on our columns and we'll grab our category and
• 04:24 put it on rows to rebuild the pivot table just as it was before.
• 04:29 Now, I'm going to go back to data range one here.
• 04:32 I'm going to go and copy the record that we added before.
• 04:36 Copy that, go back to data range two, all the way down to the bottom here,
• 04:41 get to the next row.
• 04:42 And what I'm going to do is I'm going to hit Ctrl+V to paste it.
• 04:46 Notice that it's already picked up some new highlighting.
• 04:49 This is because the table expanded to pull this in.
• 04:52 Why is it super important?
• 04:54 It's super important because now when I go over to my pivot table here and
• 04:58 I right-click and I say refresh, notice that my new values come in right away.
• 05:02 On the PivotTable tools analyze tab, this change data source button here,
• 05:06 this thing is dead to me.
• 05:07 I don't need to use this anymore because I build my stuff off of tables which
• 05:11 automatically expand when new rows are added.
• 05:14 And I highly recommend that you get the same practice,
• 05:17 never build a pivot table against the standard Excel range ever again.

Lesson notes are only available for subscribers.