Hello again and welcome back to our course on Excel 2016 Advanced.
In this section we’re going to look at creating Pivot Tables in a little more detail than
we did the preceding section.
And I’m going to create another Pivot Table from the same starting point but I’m going
to take it step-by-step and look at more of the options that are available.
Now the first thing I’ve done is to convert the table with the source data in it back
to being a range again.
So it’s no longer a table.
I already mentioned that it’s a good idea to use a table as the basis for a Pivot Table.
So I am going to turn it back into a table again but I’m only going to use part of
it and in doing that I’ll show you how easy it is to change the table later on and how
that pretty much automatically changes the Pivot Table as well.
So on this occasion I’ve still got the same source data.
I’m going to go up to the Insert tab.
I’m going to click on Table.
Now the Create Table dialog identifies the range from A1 to D34,109.
What I’m going to do is just use say the first 15,000 rows in the table.
So I’m going to click to the right of 34,109, change it to 15,000.
So I’ve only got a little under half of that data as a table.
And I’m going to change its name to Sales Table.
Now let me select the table.
So I’m going to go up to the top left hand corner, get the diagonal black arrow, click,
and click again to get the table and the header.
And then I’m going up to the Insert tab and I’m going to click on Pivot Table.
And that gives me the Create Pivot Table dialog.
Now of course taking this approach we’re seeing all the option that you didn’t see
before.
But let’s start at the top of the Create Pivot Table dialog.
Well Excel is offering me the table that I had selected which was Sales Table.
That’s fine.
Note the alternative there, Use an external data source.
I might choose one of my connections and get data from one of my connections.
You can in fact turn any appropriate transactional data into a Pivot Table and use that as the
source of a Pivot Table.
You can also choose to use this workbooks data model.
I mentioned the data model in a much earlier section.
And if you are, for instance, combining various data sources building a data model you may
in fact use that data model as the source of a Pivot Table.
That’s outside the scope of what we’re doing here.
It’s a very important option if you are combining data from a number of sources.
The second set of option buttons there let you choose whether to put the Pivot Table
on a new worksheet which is what I normally do or you can add the Pivot Table to an existing
worksheet.
Note also the checkbox at the bottom that determines whether you want to add this data
to the data model.
And again we’re not really dealing with the data model on this course.
So I’m going to stick with data from the table and it’s going to go onto a new worksheet.
Click OK.
Now what you can see here is the sort of bare bones of a Pivot Table.
There isn’t actually a Pivot Table there at the moment.
There’s a space for one but until I select at least one field I won’t have a Pivot
Table.
I’m going to rename the sheet.
And one other thing to point out here is that this panel on the right, if you click on the
header you can actually move it around.
And sometimes when you’re working with Pivot Tables, particularly if you’ve got data
say going right across a worksheet, it might be easier to float the panel.
You can expand and contract it as well of course to see the drop zones or not.
But it can sometimes be useful to move that around.
I’m just going to drop it back into place over here.
Now when you’re dealing with the fields of data for a Pivot Table of course they may
vary greatly between different sets of data.
But there are certain defaults that apply and well worth knowing about.
And the first field that I’m going to add to this Pivot Table is the Date field.
And Date fields by default are added as rows.
So if I select Date here it goes into the row section at the bottom.
And note that it picks up a default categorization of months.
So the dates there are divided into the months of the year.
Note here that we only have four months covering the data.
January, February, March, and April.
Note also that values by default occupy the values drop zone.
So now we have the sum by month of the data over those four months that cover all of the
data in the first 15,000 rows of the sales data.
Now if we return to the dates for just a moment, if I click on say Mar, the month of March
and expand it you see each individual day of the month of March.
And with that day the sum of value of sales and that is of course covering all of the
branches.
Note that if I hover over each cell I can see the figures for that cell.
So the sum of value and it says it’s in the row March, 5th of March and so on.
If I didn’t want the month categorization I could just switch it off here and then I
finish up with all of the days in sequence.
That’s not normally a recommended arrangement, particularly if you have a very large number
of days but it’s as easy to do as that.
Similarly of course I could switch off Date and then all I have are my month totals but
without the ability to breakdown into individual days.
Let me just collapse March again.
Now let me introduce Branch.
Branch is a Text field.
By default it will be added to the rows.
Watch where it goes in the rows.
It goes below Date.
And if I now were to expand say January, under each day in January I can see the figures
for the four branches.
Note the hierarchy there now under rows, Months, Dates, Branch.
How do you think it would look if I dragged Branch up to the top?
Now of course we have the four branches.
Under each branch we have the month figures that we can then breakdown into the data for
this.
Now let’s add Department.
It’s another Text field so that will get added as a row.
But what I’m going to do is to drag Department over to columns.
Now you can see that the table gives us a much more detailed breakdown by Department,
by Branch and by Date as well.
And of course we can pivot these in any way that we want.
So if I wanted to put branches in the columns and Department in the rows that’s fine as
well.
Note that for the period in the data covered by the reduced version of the tale we only
have some of the departments.
Let’s go back into the store data and just change that table at this point.
So the Table Tools Design tab has the option Resize Table.
Notice the 15,000 there.
Let’s set it to that maximum figure which was I believe 34,109, click on OK.
Let’s go back and take a look at the Pivot Table.
Now on the Analyze tab in the Pivot Table Tools there is a Refresh button.
Click on Refresh.
And now of course we can see all of the departments and the figures for the whole period of time
covered by our data because we’ve got all of the data now in that sales table.
Now I want to look now quite briefly at one or two examples of other ways that you can
control the data in a Pivot Table.
And also how you can control the formatting of the Pivot Table.
Let’s start with looking at data.
When I added the date to the Pivot Table we had days and months and there’s actually
a reason for that.
Now note I’ve got the month selected on the left there, Jan, and on the Analyze tab
which is available as one of the Pivot Table Tools when I have a selection inside the Pivot
Table there is a Group group.
And one of the buttons in there is Group Field.
Now for a date field or for a date and time field there are a number of standard ways
of grouping those and this is set up to provide days and months which is exactly what you’ve
seen so far.
If instead of providing days and months I only wanted to be able to work in months I
could actually uncheck Days here and then only months would be available.
Note also that Excel has detected the start of the period which was January 24th and the
end of the period which was July 30th, covering the data which is included in this Pivot Table.
If I click now OK on this grouping I now only have the dates and months.
I can’t break them down by day anymore.
Of course I haven’t lost the data.
I could go back into that Group field option and switch the days back on again.
But sometimes you either want to change high level groupings or something, as here, you
may say, “Well I never want to look at the individual days.
I’m looking at overall figures.”
So the individual days are just making all look more complicated than it needs to be.
So grouping is another very important aspect of how to present the data.
One or two other features on here, such as Slices, we’re going to look at in the next
section.
Now let’s look at a couple of aspects of formatting.
You know that these are amounts of money that we’ve got in the body of the table here
but you may want to actually indicate that they’re amounts of money.
What you don’t do is select all these fields in the body of the table and format them as
currency because as soon as the data changes it would all be thrown out.
There is what is in effect a styling approach to this.
So all you do is select say one of the values in the middle there and then again on the
Analyze tab one of the options there in the Active Field group, the active field being
the one that’s selected, is Field Settings.
What you’ve got in there is sum of value.
That is you have the sum of the value of in this case the breads that was sold in February
in the Houston, Texas branch.
Now note what we’ve got there is the sum of all of the individual transactions.
I could replace it by the count.
I could say how many bread sales were there?
I could work out what the max sale was, what the min sale was and so on.
I’m going to stick with sum but you can actually change what is shown within the body
of the Pivot Table there that each item in the summarized data.
But what I’m interested in doing here is the Number Format.
And for these I want it to show as currency, two decimal places.
No, I don’t think I’ll use decimal places.
Let’s just do whole dollars.
Click on OK, click on OK and now those amounts are shown in whole dollars instead.
Obviously it’s straightforward enough to change those back or to include the sense
as well if you want to.
And I’m sure by now that you’ve seen the power of Pivot Tables, although of course
there’s still a lot more to find out about them.
But one other thing, you may have been looking at this particular Pivot Table, not everybody
likes to have totals at the top of these sections of the table.
So for instance, if you look at row 5 there, Breads, the totals there, the totals for the
items below.
So going back to Houston, Texas there, that 347 we had selected, the total, 4,822, covers
the three months, Jan, Feb, March below it.
Looking at things that way doesn’t necessarily suit everybody.
Well the Design Tab in the Pivot Table Tools can be used to change the layout, the design
of the overall report.
What we have now is what’s called the compact format.
If I go to the Design tab and go to Report Layout that’s showing compact form.
I could say show in tabular form.
And in tabular form you have the more conventional layout whereby the totals are at the bottom
of each column or in this case part of a column.
Note also here that with this layout if you take January, February, March you see how
breads is repeated down there.
And not everybody likes that arrangement.
So if I go back up to the Report Layouts again and click Do not repeat item labels then the
repeats aren’t shown.
Again some people find it easier to read a table with the repeats not shown as well.
So that’s it on working with Pivot Tables.
In the next section we’re going to look at Filters and Slicers.
Please join me for that.