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
19.5 KB
Using Excel Ranges - Completed
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
Login to download
  • 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.

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