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