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

Working with Pivot Tables and Pivot Charts in Excel 2010 involves numerous functionalities and commands. Prior to creating Pivot Charts users should create a Data Table in Excel containing the relevant information, and ensure that this data conforms to basic guidelines.

Excel includes an Insert Pivot Table button and Pivot Chart dialog for creating and setting up Pivot Tables and Charts. Related to Pivot Charts and Tables, Excel also features a Pivot Table Field List Box that contains a list of fields as well as various drop zones. Excel also includes a Value Fields Settings dialog, providing access to numerous equations for data analysis.

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 again and welcome back. In this section, we’re going to start to look at Pivot Tables and Pivot Charts, which many people consider to be about the strongest features of Excel. I’m going to start with a fairly straightforward example just to get some of the basic concepts and terminology over and then I’m going to go into a much more complex example and to show you just how powerful Pivot Tables and Pivot Charts are in Excel 2010. So, let’s get started.

Now before we actually get started, a couple of words of warning about earlier versions of Excel. Pivot Tables created in Excel 2003 are really not compatible with Excel 2010 and if you have a version 2003 Pivot Table, your best bet, the one that will probably involve the least problems for you would be to use the data and to re-create a Pivot Table in Excel 2010 from the data. The second point to bear in mind is that although in many ways Excel 2007 and Excel 2010 seem very similar in many things, Pivot Tables are one of the areas where there are quite a few changes. Some of them are more changes of terminology, but there are some substantial changes as well. So if you’re just moving over from 2007 to 2010, you might want to look at what I’m doing here quite carefully as I’ll try to point out some of the changes in terminology. So, let’s get started.

Now Pivot Charts relate to transactional data and in order to prepare data for a Pivot Chart you really need to work on a couple of basic principles in terms of the data. The first principle is that each transaction should correspond to one row on your sheet. Now, these transactions really are very straightforward transactions. They’re details sales in a number of stores. The data of each sale is in the first column, the name of the store branches in the second column, the value of the sale, which is actually in dollars, is in the third column, and then the department, the type of produce is in the D column. So we have flat bread, breads, and so on. Now with each of these we have just that basic information. The examples we’re going to look at later on will have quite more complex transactions. If in fact you have information, transactions the other way around, so if each column were a transaction, that’s not really suitable for building Pivot Charts in this way and you need to in some way transpose that data. And a couple of other very important points about Pivot Charts in order to make them reasonably straightforward, yes, Headers are a good idea, but keep the Headers simple, ideally the Header will be in one cell. And secondly, you shouldn’t have any blank columns or blank rows in your data. So, the way I’ve got this data presented here is pretty much spot on right for a Pivot Chart.

And here’s a useful tip when you’re creating Pivot Charts. It’s always a good idea to actually put all your data into a table or rather to create a table containing all of your data. The reason this is helpful is that if you subsequently add some data, or even take some data away, Excel 2010 will automatically refresh and redraw the Pivot Chart on the basis of the contents of the table. So, if it comes to changes to data later on, you’ll find this actually saves you some time. It’s quite easy to put your data into a table, just click somewhere within the data and then use Control-T. In my case, I know that I’ve actually got an awful lot of data here, so my range is A1 to D34109. As you can see I have a lot of transactions, check the box My Table has Headers, click on OK, convert the selection to a Table, Yes, and there we are. And I’ve now got a Table in place.

Now, in order to create the Pivot Chart, we just click anywhere within our data and then on the Insert Menu there is a Pivot Table button. Now this button has a top and a bottom and we’re going to start by clicking the bottom, Insert Pivot Table, and we’re going to select the Pivot Chart Menu item.

So, we see the Create Pivot Chart Table with Pivot Chart dialog and within that we have, first of all, a definition of the data that’s going to be analyzed. Now, if I had just selected a range, the range would appear here. Because I put this into a table, it’s actually linked this to what it’s called Table 2. You can see up here Table 2, just confirms that that’s the table that’s outlined here. And I can actually link to an external data source, now I’m not going to do that, I’m going to stick with the data I’ve got here and by Default, the chart is created on a new worksheet. You can put it on an existing worksheet, perhaps one you’ve all ready prepared for that purpose, but I’m going to choose New Worksheet. Click on OK and there we are. My new worksheet, which is actually called Sheet 2, which is created, and now let’s look at all these various Fields and Controls that are put on that sheet.

So, now we have our new worksheet. The first three columns or this area in the first three columns is actually reserved for the Pivot Table, columns A to C for the Pivot Table. The Pivot Chart has columns E to M reserved for it and on the right we have the Pivot Table Field List Box. Now this contains a list of the Fields and then there are four drop zones at the bottom. Now, the arrangement here, what you can actually see, is selectable by this little menu and you could, it currently says Filled Section Area, Sections Stacked. You can actually change that, the second option is Filled Section Area, Sections Side-by-Side, so you have a different arrangement, Filled Section Only and so on. Now as we start to develop the complexity of the data we’re dealing with in Pivot Tables and Pivot Charts, you’ll see the use for those alternatives for the Pivot Table Field List Box.

Now apart from these new areas on the worksheet, we have a set of four tabs on the ribbon under the heading Pivot Chart Tools. We have a Design tab, which is basically the same as the Chart Design tab we’ve seen before. We have a Layout tab, and then we have a Format tab. Again, these are pretty much the same as we had before and then an Analyze tab that we’ll look at a little bit later on, which is where we do Analysis on the data in our Pivot Table.

So, as a first step in creating a Pivot Chart, let’s enable one of the fields to add to our report and we’re going to choose Department. Now once we choose department a number of things happen. One of them is that over here in the Table area we get a list of all the different departments that Excel 2010 recognized in our data. And apart from being able to select one of the individual departments, we can also use the Filter Control to choose any combination of them. So, for instance, we could choose breads and flatbread. In the chart area we see department with a symbol here showing that there’s a filter on the departments and we can actually change the departments there as well. And again, so it works both ways between the table and the chart.

One other things to note is that department appears in the Axis Fields down here and it’s all ready identified as one of the Axis fields for my Pivot Chart. Now, one variable is obviously not going to be enough, so let’s do an analysis on Value of Sales by Department. So, the next thing we do is to check the Value Box. Now what we now have is two fields selected and Excel 2010 has identified that the Value Field is going to be our Vertical Numerical Field here and the Department Field is on the chart horizontally. Now note, Value is actually in the sigma drop zone and when we’re dealing with more than two fields, there will be occasions when we will need to move things between zones. But for the purposes of this exercise, Excel has actually put everything in the right place for us, so we don’t need to move anything on this occasion.

Now although this is a very straightforward example, it’s a good place to stop because it enables us to look at some of the main features of Pivot Tables and Pivot Charts. Let’s look at the Pivot Chart, first of all. It’s created as a normal Excel 2010 Chart and we can do things like Change the Title, Format the Axes, apply different styles to the chart, and so on. So, we can pretty much do anything we would do to a normal chart, we can do to this chart, including putting it somewhere else for presentation purposes and so on. But we’ll come back to that later on. Let’s look at some of the powerful features that we can use here with Pivot Tables and Charts. One of them, of course, is the Filtering Facility. So, if I actually didn’t want to include the figures for Coffee and the figures for Gift Certificates, I only want breads, flatbreads and, say, oatmeal. Click on OK, and everything gets updated.

Now on occasion when you’re working with Pivots you may find that the Field List on the right disappears, quite easy to get it back. Normally, if you just click within the Pivot Table or the Pivot Chart itself you’ll see the List reappear. So, just one more thing on Filtering, we always have the Select All feature. So if we do a Select All, note not only that we see all of the Departments, the sales departments shown, but Excel 2010 does its usual good job of fitting everything in by changing the way that the Axes are Labeled, the Gridlines, and so on.

And now let’s look at one of the other very important basic features here and that is if we go back down to the drop zone for sigma here, note that it says Sum of Value. By Default it actually plots the sum of the values of all of the transactions and as we know here, categorize by department. But we don’t actually have Sum of Value, we can, for instance, go into Value Field Settings, brings up the Value Field Settings dialogue and we can change it to, for instance, Counter Value. And what then happens is that Excel 2010 counts the number of transactions instead of summing the total value. So we find how many transactions we have in each department. Now, they’re actually many options here, back into Value Field Settings. We can have the Average, the Maximum, the Minimum. We can have the Standard Deviation, and we also have options to do Show Values As. So, for instance, we could have the sum and then showing the values as a percentage of the grand total, click on OK, and basically for each of the departments we can see the percentage that its sales represent out of the grand totals. So, smoothies nearly 50%, gross sales just over 40%, and all of the others are actually very small.

So, if you’ve not really seen or used Pivot Tables and Pivot Charts before, by now you’ll be looking at that and probably be quite impressed and probably see why people see that it’s such a powerful feature of Excel. But in fact we’ve only really scratched the surface so far and in the next section we’re going to dive into a lot more detail using initially this example and more detail from this example, but then building up the overall complexity. 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!