Welcome back to our course on Excel 2013 Advanced.
In this section we’re going to take a look at pivot charts.
We’ve already spent a little bit of time on this course looking at pivot tables and
the various ways of creating, modifying, and using them.
Well, pivot charts add another dimension to the same type of analysis and a very visual
one which is particularly useful if you want to make a presentation of the kind of data
that you would include in a pivot table.
Now in order to demonstrate the use of pivot charts, I’m going to start with the last
example we did with those store sales from a number of U.S. small convenient stores taken
over a period of several months.
Now if you already have a pivot table, creating a pivot chart from it is really straightforward.
So I just click anywhere within this pivot table, I can go to the Insert tab and then
I have a Pivot chart button here and I can just click on pivot chart.
So I see the familiar Insert Chart dialog and within that Excel 2013 has recommended
the use of a clustered column chart which is very often what its recommendation is.
So I’m going to click on clustered column, click on OK for that, and a very small chart
is created.
Generally speaking, when you’re working with pivot charts you’re probably going
to want to remove the pivot table fields pane as I’ve removed it here and you’ll almost
certainly want to make the chart itself quite a bit bigger.
As you make the chart bigger, of course more detail appears.
Now one important thing to note about a pivot chart is that to some extent it has its own
controls.
Now that’s only to some extent.
I’ll show you what I mean by that now.
I’m going to take this particular pivot chart, so I’ve selected it, I’m going
to cut it, and I’m going to put it on to a new sheet in the same workbook.
One reason for doing that is so that we’re not distracted by seeing the data as well,
but also to give us the maximum amount of working space.
So let me just paste it into this sheet.
Now the first thing I’d like to point out to you here is that when you create a pivot
chart from a pivot table if the pivot table already has filters applied, then that will
very much affect what you can see in the pivot chart.
If I were to go back to the pivot table that we started with just now, we could check which
filters are currently applied.
But as an alternative to that, if I hover over the Department button here, notice how
it tells us that there is a date filter in place so that the date is between September
1, 2012 and October 31, 2012 and that it’s sorted on branches in reverse alphabetical
order.
Now because I’ve got a date filter in place, I’m only getting three departments worth
of data.
There isn’t actually a department filter in place.
Let me just click on the department filter here.
I’ve actually got all departments but I’m only showing three because there’s only
three with sales in that period.
If I go to the date filter where I have this date filter, let me click on that and then
click on the between which shows the between dates.
Let me cancel that.
Let me now just clear the date filter and we’ll see what happens.
So go over date filter, say clear filter, and now what we get is we get all of the departments
because all of have got sales within the period of time that covers all of our transactions.
Of course because we’ve now got a much higher level of inclusion of data, this column chart
is almost unusable.
The lines are so thin and there are so many of them that it’s almost unusable.
But, of course, we can narrow things down because apart from removing filters we applied
in the pivot table itself, we can of course apply filters in the pivot chart.
So let’s suppose that I just wanted to look at, say, two of the quarters; say quarter
one and quarter two.
In this type of filter to select multiple items I check here and that turns these into
checkboxes.
Let me switch them all off and we’ll just have quarter one and quarter two, click on
OK, and now we’re restricted to quarter one and quarter two and automatically we only
see departments that have sales in that period of time.
If I further wanted to filter on branch, I could choose perhaps the Denver, Colorado
branch and say the Chicago branch, click on OK, and I’ve just got two branches to compare.
So I may have to remove filters that I basically pick up from the pivot table or I can apply
them myself within the pivot chart.
Now before we move on to look at some of the other capabilities of pivot charts, I’d
just like to talk about the use of pivot charts in presentations and live discussions.
I’ve very often been involved in using these in this kind of format where you have a pivot
chart with perhaps a very large amount of data and where you want to present to other
people or discuss with other people the underlying transactional data.
A pivot chart is a really excellent way of being able to focus perhaps on one department,
one period of time, perhaps one branch in this case, and you can move the numbers around.
You can compare them.
You can look at where there is growth, where there could be growth, and so on.
Using a pivot chart like this is something where there’s very little by way of other
distractions and I think particularly in Excel 2013 the capabilities with pivot charts are
really very impressive in this area.
Now you notice in this case that I’ve minimized the Ribbon, I’ve removed the formula bar,
and making almost maximum use of the space that’s available in order to get as much
into the pivot chart as I can and make it as easy as possible for my audience to see
the details.
Okay, I’m going to go back to the conventional view now and I’m going to talk about a couple
of the other things that we can do using the pivot chart tools where we have three contextual
tabs: Analyze, Design, and Format.
Let’s start with the Analyze tab.
On the Analyze tab, we could change the name of the chart.
We can also use this Options button here to look at the pivot table options, obviously
related to the underlying pivot table in this case.
We can also create slicers and timelines in the way that we did earlier on in the course
when we looked at pivot tables.
We can change the data source from here.
We can move the chart.
We can also show or hide the field list.
So although we’re on a different sheet now, we can put the pivot chart fields pane on
there and let me just hide the field list again.
The other thing that can be useful, notice the Field Buttons button over here.
Look at the chart while I click here.
If I click at the top of the Field Buttons button you see that all of those buttons and
controls disappear from the chart.
Let me just click them back on again.
In fact, there are several different types of control on there.
If I click on the lower part of that Fields Buttons button, you can see I have Report
Filter Field button; so I could just switch those off or I also have Legend Field buttons.
Note the legend is on the right in this case.
So if I switch off the Legend Field buttons, that’s gone.
But I can also very selectively switch any of those on and off or the equivalent of clicking
the top if I’m working at the bottom is to hide all.
So in that way I can control what’s shown on the pivot chart and that in turn can be
very useful if you’re working interactively with a pivot chart.
So that’s the first of the pivot chart tools.
That’s the Analyze tab.
Let’s take a look at the Design tab.
The Design tab is very similar to Design tabs that you’ve seen already.
So apart from things like add chart element, quick layout to choose a quick layout depending
on the type of chart that you’ve got chosen.
And then you’ve got options to choose a chart style, to select different data, to
change the chart type, etc.
Again, one to move the chart, so no surprises there really on the Design tab.
With the Format tab, again you can change shape styles.
You can use WordArt styles.
You can change sizes and so on.
So again, no real surprises on the Format tab either.
Also to the right of the pivot chart we have two buttons again.
If I click on the top button, the one with the plus symbol, that gives me access to what
is an alternative way of adding chart elements.
So I can control axes, axis titles, chart title, etc.
All of these have live preview facilities.
So you can go through and again format your pivot chart in pretty much any way you want.
And then the second button there, the one with the picture of the brush on it, if I
click on that I’m afraid I have to adapt this slightly because of the way that I’m
recording this course.
You can see that you get a panel there where you can choose a style and you also have a
Color tab where you can change or set the color scheme.
So again with format and the use of these two buttons, you can really substantially
customize your chart to more or less any style, color scheme, and content that you might reasonably
want.
So in terms of the design and formatting of pivot charts, I largely think of them as following
pretty much the same rules as regular kinds of charts and graphs, very few differences.
But what you get in addition with a pivot chart is you have this link to the initial
source data via the pivot table mechanism which means that you have a much more adaptable
way of deciding which data it included.
With most of the charts that you look at in Excel 2013, you have the change data source
facility which is very often just a range of data, a two dimensional range of data.
With the use of pivot tables, you have a much more flexible range of data sources and ways
of selecting from those data sources.
So I’ve just got one more thing to show you about pivot charts now.
I’m going to go back to store data, the original source data.
Click anywhere in the source data, click on insert, and in the Charts Group under pivot
chart, one of the options is pivot chart and pivot table.
If you click on that option, it gives you both basically and it gives you a mechanism
for dealing with both.
I’m going to stick with our table, store sales, as my source data.
I’m going to put this on a new worksheet again, click on OK, and what I get on a new
worksheet is I get pivot chart fields on the right, all the same mechanisms that I had
before, a choice of fields, and then my drop zones in the corner here.
And then I have pivot table two here as a sort of placeholder for pivot table two and
a place holder for a chart here.
As I select, let’s suppose I chose date and department, as I select both of those,
what I start to get straightaway is the table being built up on the worksheet and the chart
being built up here within a chart area hovering over the pivot table.
So let me now just drop value there and you see that my pivot table is started and my
pivot chart is started.
Both of them created pretty much instantaneously from the original store data.
So that’s it on pivot charts.
I’ll see you in the next section.