How to Create Pivot Charts in Excel 2013

The Pivot Chart in Excel 2013 is very similar to the Pivot Table, presenting comparable information in a more visually oriented manner; thus, Pivot Charts are useful for live presentations and meetings. Pivot Charts should be created from existing Pivot Tables through the Pivot Chart button, and necessarily Filters and other restrictions on the Pivot Table apply to the Pivot Chart as well. Working with Pivot Charts in Excel involves various controls, such as the contextual Analyze, Design and Format Tabs in the Ribbon. Pivot Charts function like normal charts and graphs but are linked to Pivot Table data and functionality.

Watch the free video here, transcripts for the entire video follow:

Do you need to learn Microsoft Excel 2013? Get 19 hours of Microsoft Excel 2013 training – click here.

Video transcripts:

Welcome back to our course on Excel 2013 Advanced. In this section we’re going to take a look at pivot charts. We’ve all ready 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 all ready 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 Dialogue 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 all ready 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. And 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. And 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. And 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. And 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 all ready. 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, etcetera; again one to move the chart. So no surprises there really on the Design Tab.

And 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, etcetera. 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. And 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. And 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. And 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.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!