How to Use Excel 2010 Pivot Tables and Charts – Part 2

Microsoft Excel 2010 contains numerous commands and options which are useful for customizing and analyzing Pivot Charts and Pivot Tables. The contextual Design, Layout, Format and Analyze tabs allow users to select chart types and styles as well as add data labels, while the Field List allows users to categorize fields and filtered sub-groups according to specific field variables.

Excel 2010 features Slice functionality, allowing users to access multiple customizable subsets of data, as well as a Sigma drop zone, which allows users to employ analytical equations. The Column Labels drop zone is useful for creating graphical chart structures.

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

Do you need to learn Microsoft Excel? Get 35 hours of Microsoft Excel training – click here

Video transcripts:

Hello and welcome back. In this section, we’re going to continue looking at out straightforward example of sales in a convenient store or a number of convenient stores and what we’re going to do first is just to look at formatting the Pivot Chart that we created before. First thing I’m going to do though is to change the data selection. I’ve actually now got a number of places I can that. I can do it here; I can click on department on the chart and filter from there. So if I remove Select All and just select breads, flat bread, oatmeal, and snacks, click on OK, and as you saw before, everything is updated. The selection shown in the Pivot Table and the Chart are all selected. The Sum of Value I have here is still a percentage of total sales, but of course, this is the percentage of total sales for the selected departments. So, just over 50% of the total sales in these four departments is down to breads.

Now, let’s once again take a look at formatting the Pivot Chart itself. We’ve seen ways of selecting data for it and if you want to actually format it, we may want to use it in a report; you may want to move it within your workbook. It’s very often the case that it’s a good idea to get the Field List out of the way while you work on it. I mentioned before that usually if you click within Table or Chart, the Field List will normally reappear. But actually if you’ve deliberately closed it, you need to open it quite deliberately as well. There are two main ways of doing that. If you currently have the Chart selected, then on the Analyze tab there is a button that says Field List, click that and the Field List is shown. And close it again. If you’re within the Pivot Table, then you have a Pivot Table Tools Group with Options and Design in it. If you click in Options, over on the right there’s a Field List button there that shows the Field List for you. So, I’m going to close this again and now let’s start working on this chart.

With the chart selected we get the group of tabs up here – Design, Layout, Format, Analyzer. Let’s go to the Design tab. I’m going to change the Chart Type. I’m going to change it to a Pie Chart. I’m going to change it to a 3-D Pie Chart; click on OK. It all works exactly the same as it did before and I can even go into my Filter here and say decide, well I don’t really want snacks included, click on Ok and everything is updated.

Now there are certain types of chart you can’t use as Pivot Charts, including Scatter Charts and Bubble Charts. But for the purposes of analysis they’re not the sort of charts we’d be using anyway, so that’s not really a restriction.

So, let’s change the Title here. We’re going to change it to “Sales by Product,” okay. And we’re going to remove the Legend.

And finally, we’ll add some Data Labels. So, let’s go for More Data Label Options. We’re going to put the Category name and the Value and let’s not have leader lines. Click on Close. There we are. So flatbread and oatmeal are fine, let’s move breads just over to here and there we are, “Sales by Product.” That’s pretty good.

Now, of course, we can still use usual facilities to increase the size of the chart, for instance. Excel may move some of the Labels to less convenient places, but that’s fine. And we can, back in Design, we can choose from available Chart Styles. So we could go for something like this. And the one thing you may have noticed, which is different, is this, Sum of Value up here. Now this is one of the buttons that the Pivot Chart and Table mechanism in Excel 2010 puts on the charts. If you go to the Analyze tab there is a button here, Field Buttons and if you click the top of that to switch off the Field Buttons, then any of those buttons will disappear. You can, of course, put them back on again if you need to.

So, now we are going to return to the original Field List and we’re now going to go to the next stage with this Pivot Table because we’re going to introduce one of the other variables. We’re going to introduce Branch. So, in the Field List click on Branch. Excel 2010 works out that Branch should be one of the Axis fields. It’s not going to try to do any kind of calculation on it as it did with values. And what you can see has happened here is that for our selected group of departments, so starting with breads, flatbread, oatmeal, snacks; it has then Categorized or Sub-categorized by Branch of Store those percentages. So, for instance, for breads, of the 56.63% of our total that is contributable to breads, this is now broken down by our five stores.

Now at this point you can probably start to see the power of Pivot Tables and let’s now do a few things to show how easy it is to change this particular slice of the data that we’re looking at. Let’s go down, first of all, to the sigma drop zone down here. We selected earlier on here as our Value Field settings, we changed to Sum shown as a percentage of the grand total. Let’s just return it to Sum as it was before, click on OK, and we’ve now got a total value of sales for each of those. The other thing we can do down here, if you look at the Axis Fields Department and Branch, I can actually click on Branch and drag it above department and watch now what happens. We now have the Branches as our first level and then within that the Departments, the types of food stuffs within the Branches, and obviously everything else is updated in line with this.

So, of course, I can filter on Branch now. So I could Undo Select All and say just look at Boston and Chicago. Again, everything is updated accordingly. Now there’s one other very important thing now and that is that as we look at the chart here, we can see that it affectively we have eight values. That’s four different departments, two different branches, and the structure relating these. Although it’s shown in the way that the axis is marked, doesn’t really reflect the nested nature of this information. It’s as though these are eight separate departments.

Now, of course, there may be situations in which that is exactly the way that we want the data to look. But if we actually wanted a structure whereby it’s got a more two dimensional feel, the clue to what has happened here by Default with Excel 2010 is that both Branch and Department are shown as Row Labels. And in fact you can see they are Row Labels. They are structured, there are as we move these around, departments within branches, but they’re both Row Labels. If we wanted to get a two dimensional structure all we need to do is to take department and drop it in the Column Labels drop zone. What happens then is that we get a completely different look to our data because now the columns are the departments, the food types, and the rows are our two branches that are shown. Both of them are Filtered, so on the Column Labels, that is the department types, we could perhaps put in hot teas and on the Row Labels, that is the branches, we could include another branch, so we could put Denver in there. And, of course, we have this type of Clustered Column Chart representing all of that data and looking pretty similar to the Clustered Column Charts that we looked at before.

The next thing we’re going to do is a very important feature of Pivot Tables and Charts, but it needs to be treated with a little bit of care. We have then our Branches as rows and we have our Departments as columns. What I’m going to do is to remove Departments altogether.

So, to get rid of Department, I can just double click on it to bring up the Menu and I can just say Remove Field and that takes it out of the Legend Fields drop zone. So all I have now are my three branches with the total sales for each. What I’m now going to do though is I’m going to add Date. Now, this is a bit of a dangerous thing to do with a very, very large amount of data because the dates are by day and there are over 30,000 transactions, but Excel 2010 copes with it okay. And if I now drag Branch over to the Legend Fields, that’s the column ones and I’ve now got something a little bit closer to a readable chart. Now the problem is with daily data that’s extremely difficult to follow. So what I really want to do is to report by say month, and that’s what we’re going to do next.

Now, one of the differences when you’re dealing with dates, for example, is that although you have a Filter here, so if I click on the Filter, the Filter covers every day over a long period of time and it still only does whole days. So, I could do as I did with Branch and Department at different times, and select a subset, but what it doesn’t do is to total up by date to larger time denominations like month. The way that I do that is I go to the Options tab within the Pivot Table Tools and there’s a function there, Group Field. And if I click on Group Field, Excel 2010 recognizes it as a Date Field. It gives me my Minimum starting date as a Default ending at as another Default. I can change those if I wanted to, but it also lets me group. And I’m going to group this data by months. My choices are everything from seconds to years, but months will I think will be good. Click on OK and what you now see is much better. I’ve got a by month list of sales for my three selected branches with the total value of sales and I also have the Clustered Column Chart to go with it. So, that’s how we do grouping. Now, dates are very often the types of field we’ll use for groupings and this is a pretty clever feature of Excel 2010’s Pivot Tables and Charts, which can really help in reporting a lot of data over a long period of time.

So, we’ve covered many more of the basic features of Pivot Tables and Charts and we’re going to return to them again in the next section where we’re going to step up again and look at some of the more advanced features, including the use of Filters and Slices. So I’ll see you then.

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!