How to Create Pivot Tables in Microsoft Excel 2013 – Part 1

Pivot Tables are one of Microsoft Excel’s greatest strengths and most useful tools, allowing for the management of transaction data. Pivot Tables allow different data properties like Date, Value and Location to be graphed onto an X-Y axis in various ways, allowing access to a multitude of Pivot Charts plotting different row properties along the X and Y axes.  Various syntactical rules exist which allow data to be correctly imported into a Pivot Chart. The Quick Analysis tool is a new feature in Excel 2013 that contains various Pivot Table options, and the Four Quadrants area allows for further customization.

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 start to look at pivot tables. Now it’s a little bit tricky really when we start on pivot tables here because I’m sure that some of you will have used pivot tables before, at least in a basic form. On the other hand understanding some of the basics of pivot tables if you haven’t used them before is absolutely essential even if what we’re going to do is go into some of the more advanced aspects of their use. So what I’m going to do is to start with a relatively straightforward case study with some fairly straightforward data and develop a pivot table and a pivot chart based on this straightforward data before we move into some of the more advanced aspects and in particular some of the aspects that have been introduced in this and other recent versions of Excel.

There are a few features of Microsoft Excel that really are considered to be its greatest strengths and amongst these are pivot tables, pivot charts, and the associated tools and techniques. They’ve been in Excel for several versions now. By no means are they new and they’ve continually been improved particularly in recent versions, but the fundamental idea of pivot tables has been around for some time and has been one of its great strengths for some time.

The first thing to explain to you is not what a pivot table is. I’ll come back to that in a little while. I’m going to start by explaining situations in which you would use pivot tables because that will help to explain not only why you use them but what they are.

We use pivot tables for what we call transactional data. So that’s records of things that have happened normally in some kind of sequence. Now that doesn’t necessarily mean that there is some kind of dependency between these items but it does mean that they are similar events or similar measurements that have a certain set of properties in common.

Now the case study we’re going to start with is a very straightforward one and it’s to do with sales in convenient stores. And in a number of convenient stores in the U.S.A. we’ve recorded individual sales over a long period of time. I’ve actually got a lot of data in the workbook that we’re using for this example, many thousands of transactions. And each transaction is a row on this sheet. So taking that row, for example, we’ve got a date for the transaction. We’ve got a branch. It’s the Denver, Colorado branch. We’ve got a value, $1. And the department in which the sale was made. It’s breads. Now if I go through all of this data and as I say there are many thousands of lines of data, you’ll see that I’m dealing with a few branches over a period of time, not a very long period of time. But there are hundreds of sales each day and they’re all categorized in exactly the same way.

So first of all what we’re dealing with is rows in a worksheet or some worksheets which each represent some kind of transaction. It’s not necessarily a sale but just something that happens; some event, something that was measured.

Now one of the problems you always get with very large amounts of data is finding some meaningful ways of analyzing that data or presenting that data. So for example, given this straightforward convenient store sales data how might we want to analyze it? Supposing we wanted to show, for instance, how the sales in the stores that we’ve got varied over a period of time. Maybe we would plot the total sales for each store for each month in the time period where we’ve made the measurements or maybe for each week. Or would we split those sales by department and see if maybe one department in one store has a much better growth of sales over a period of time than another? There are various options for plotting what we’ve measured against other things that we’ve measured. So are we looking to see how the stores compare with each other? Are we looking to see how the departments compare with each other? Are we looking to see how things vary over time? Now in order to do that we want to be able to look at various ways of plotting different properties of this data against other properties of the data and we’ll want to be able to switch what we’re plotting round. We’ll want to be able to, if you think in terms of X and Y axes in a chart, we’ll want to be able to sometimes put something on the X axis, maybe sometimes put it on the Y axis, to plot something different against something else.

Now the name pivot table arises because you have the ability to pivot properties of the data, individual attributes of the measured data, and to pivot them from one axis in a plot to another axis in a plot. And that’s originally how the name pivot table came into force for this kind of analysis and presentation of data.

Now before we create this pivot table I’ve got a few important points I need to cover. Pivot tables have been around for some time and you may well have used them in an earlier version of Excel. If you have pivot tables created in Excel 2003 they’re really not compatible with the newer version and if you try to use those in the newer version the chances are you won’t get too far. If you have some data that you’ve created pivot tables from in Excel 2003 your best bet is to go back to the original data, much like the data that you’ve seen here, transactional data by row, and really start building a pivot table from scratch in Excel 2013 and usually in the long run you’ll find that’s quicker than trying to do something with pivot tables you’ve created in Excel 2003.

With more recent versions of Excel there is a high level of compatibility. So if you’ve got pivot tables, pivot charts created in Excel 2007, Excel 2010 then you should find that everything is compatible. Having said that there are a lot of new features, several new features that were introduced in Excel 2010 and several introduced in Excel 2013 so even if you’ve used those more recent versions I think it’s still worth going through the sections of this course because I will be covering many of those new features.

Another thing I should point out now is that in general terms you can actually create pivot tables and pivot charts using external data. It doesn’t actually need to be data that’s in the workbook that you’re creating the table and chart in. so you could link to an external data source. But in this specific first case study we’re just looking at the data on this store data sheet in the current workbook.

Now unfortunately I also have to go through some of the rules about how this data is presented. Each transaction needs to be a row, not a column. So if your data is actually in column form, so for instance column A is the first transaction, column B the second transaction, and so on, you need to transpose all of that and get the data so that a transaction is a row. You also have situations where it may not be as simple as a row. So for example let’s suppose the first row her was year 2010 and then we had some figures in column D for Q1 and then column E’s Q2, and F is Q3, and so on. So we’ve got a sort of rectangle array. That’s also no good. You need to have transaction per row. If you get data in the way I’ve just described you need to split that up and get it into row by row transaction sequence. You should also have no blank rows, no blank columns. Each column should have a heading with a title and the title should be unique. You don’t want two of these columns to have the same title at the top.

Now as with many of the data sets that you’re going to use pivot tables on our data set has got a column here with numeric data. If in a numeric data column there is no value do not put a space. Put in a zero because again if you put a space in that will cause trouble as well.

And one other point. If you’re working in compatibility mode, so bear in mind that in compatibility mode you’re able to deal with Excel workbooks from older versions of Excel and able to save workbooks for older versions as well, then many of the newer features of pivot tables in the last three versions, 2007, 2010, 2013 are going to be disabled which you probably don’t want. So avoid working in compatibility mode if you possibly can.

So the data that I’ve got here satisfies all of those rules and I can go ahead and create a pivot table.
In Excel 2013 quite a few new options have been introduced for this. So I’m going to start with one of the new options. And all you need to do in this case is to select all of the data for the table. There is a keyboard shortcut to do that. Let me just click in there somewhere. The keyboard shortcut I want is Control and asterisk, select all of the data. And then you get this little icon here that appears out to the right. Now the icon there is the Quick Analysis icon. If I just hover over it you can see the screen tip Quick Analysis. Use the quick analysis tool to quickly and easily analyze your data with some of Excel’s most useful tools such as charts, color coding, and formulas. So click on the Quick Analysis icon and what it comes up with is this little sort of menu bar here. Do you want formatting? Do you want chart? Do you want totals, tables, or Sparklines? Well I’m going to go for tables on this occasion so click on tables. And I’m given the choice of just creating a table, creating one kind of pivot table. Note that there’s a preview of that pivot table up above, second kind of pivot table and then I have a More option as well. I’ll come back to More later. Let’s look at that first pivot table.

If you look at that you can see what it’s actually done. It’s taken one of the properties of each of the transactions which is the branch name and what it’s done is to categorize all of the sales according to branch. So we’ve got Boston and then a total value of the sales, $60,157.14; Chicago, $19,868.21; Denver, Miami, so on. Now that is my pivot table or should I say it is one version of a pivot table. So let’s click on that option and see what happens. And what happens is that Excel 2013 creates a new sheet and the new sheet has this pivot table on it. In column A it’s got the branch. In column B it’s got the sum of the values. And there is a panel on the right where I can now set about controlling what’s actually shown in that pivot table.

Now we’re going to look at this in much more detail in the next section, but let me just show you now a little bit about what pivot table means.

Currently in the list of pivot table fields on the right, I’ve only got two fields checked. I’ve got branch checked and I’ve got value checked and they’re the two that are shown here. And in fact with the branch I’ve got the names of the individual branches. In fact there’s a New York branch there which seems to have a very small number of sales. I’m not quite sure why that is. But the value, I’ve got the sum of the values shown in the table. Now the symbol down here, sum of value, tells you that it is the sum of value that’s shown.

Now let’s suppose I wanted to introduce another field into this table. Let’s suppose that as well as branch and value I want department. Well I check this to include department and what I get now is branch and department both shown in the same pivot table. So for Boston I’ve got a breakdown by department. For Chicago I’ve got a breakdown by department and so on.

Now let’s look at this little area in the bottom right here. We’ve got four quadrants. We’ve got a filters quadrant, a columns quadrant, a rows quadrant, and a values quadrant. At the moment in the rows quadrant we’ve got branch and department. Let me move department up to columns and see what happens. What I’ve now got is a breakdown of sales, still for each branch because the branches are in the rows, but I’ve got a breakdown by department where the departments are represented by columns. So not only can I see the total sales, for instance here, for the bottom Boston branch. Still the same total that I had before but I’ve got a breakdown by department. And in fact not only that, I can even go so far as to filter what I need. So let’s suppose I disable them all and I just say I want a breakdown for gross sales, hot teas, and juices, click on OK, and I just get those three columns.

Now in addition to that if I wanted to I could pivot the whole thing. So I could take department, put it back down there, take branch, put it up there, and you can see what happens. I’ve got departments going down there, the three that I filtered on, and then I’ve got the branches going across there. So you can see even from that the amazing power and flexibility of pivot tables in Excel 2013.

So that’s the basic principle of pivot tables. In the next section we’re going to look at this in much more detail and we’re also going to look at some more of the new features. 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!