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.
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Using Excel Ranges.xlsx19.5 KB Using Excel Ranges - Completed.xlsx
29.6 KB
Quick reference
Using Excel Ranges
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
- 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.