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

Excel 2013 contains numerous options and new features for the Pivot Table functionality. Pivot Tables can be created through the Create Pivot Table Dialogue, and the existing tables from which a Pivot Table emerges are created, likewise, through the Create Table Dialogue. Users build up Pivot Tables by selecting data fields and dropping them into the Fields Panel’s Drop Zone. Excel 2013 includes a Group Field option that enables the grouping of dates into weekly or monthly groups. Arranging and formatting Pivot Tables is relatively straightforward in Excel, utilizing simple drag and drop 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:

Hello again and welcome back to our course on Excel 2013 Advanced. In the previous section we created our first pivot table and in this section we’re going to expand on the options when you’re creating pivot tables and we’ll be looking at some of the new features in Excel 2013 that can let you get to really good pivot tables very quickly.

So in the previous section we very quickly created a pivot table. Generally speaking when you’re creating pivot tables it’s usually best to create a pivot table from a table rather than a range of data. And the main reason for this is that if you subsequently update the data and as the data you’re dealing with is probably transactional it’s entirely possible that you will want to add to that data later on. You’re actually better off defining a table containing the current data, building the pivot table on the basis of that, and then when you do subsequently add data the amount of work you need to do to update the pivot table is greatly reduced. So let’s start by creating a table from the data that we’ve got here.

So let me just click within the table of data and then insert table and as you should know by now we come up with the Create Table Dialogue. Excel 2013 suggests the range for the table. There are in fact 34,109 rows of data in this data set and as you can tell there it’s detected that the table has headers. What I’m going to do is I’m not going to include all 34,109 rows of data. What I’m going to do is just include the first 20,000 rows and then what we’re going to do is to extend the table later on just to show how easy it is to update the pivot table when we get new, additional data. So let’s go on that basis now. Click on OK. The table is selected and I’m going to call that table Store Sales. So there we are.

Now I’m inside my table of data and I’m going to go to the Insert Tab and I’m going to insert a pivot table pretty much from scratch. So click on insert pivot table and what I get is a Create Pivot Table Dialogue. Now first of all I’m required to select a table or range. If I’ve got an existing table, which of course I have, Store Sales, that’s the one that it defaults to. I’ve all ready got the cursor inside there. That’s why Store Sales comes up. If I wanted to select a different range of course I could. And as you can see from the Option buttons there, there is an alternative option which is Use an external data source. And an external data source could include a connection. We setup connections earlier in the course and of course you can create a pivot table from one of those connections you’ve setup earlier on. For example, it’s very straightforward to create a pivot table using a connection to an Access data source. For the moment let’s stick with this Store Sales table.

Then I have options of either put it on a new worksheet or use an existing worksheet. Now I’m going to put this on a new worksheet. Sometimes there are advantages in putting pivot tables, charts, etcetera on the same worksheet but once you’ve got the pivot table itself I find that having the raw data right in front of you can be a little bit distracting and you actually get more space to work with if you put it on a new worksheet anyway. So I’m going to stick with the new worksheet option, click on OK, and we have basically the bare bones of a pivot table.

Now there’s a couple of things to notice here straightaway. One of them is you may want to rename the sheet here with the pivot table on it, which I’m going to do right now. And also note that what you can see here marked as pivot table 3 is actually a placeholder for a pivot table that hasn’t been built yet. And it won’t be built until we select one or two of the fields here from the pivot table fields panel on the right. Incidentally although that panel seems to be firmly fixed on the right there you can actually move the panel around. You can grab it by its heading and move it around and sometimes when you’re dealing with a pivot table and maybe it needs to be pretty big, you might find it convenient to just pull that away from its right hand side location there and move it to a more convenient location. To pull it back again push it over to the right and then it snaps back into place.

Now as you’ll have realized from the previous section we actually build the pivot table by selecting fields from our data and moving them into these drop zones at the bottom of the pivot table fields panel on the right. Some things happen by default. If for example you select a date type field it will automatically be added to the rows drop zone. So let me just select date there. It automatically goes into that zone. Similarly if I check a numeric field like value here that’s automatically added to the values drop zone and becomes a sum of values. The assumption being that in doing this pivot table analysis you’re looking at totals from a number of transactions. And as you can see with the selection I’ve made there basically my pivot table has got a first column which shows dates, so individual dates starting 24th April 2012 and going forward from there. And then the second column is the sum of values, i.e., the sum of sales for each of those days summed up over all of the branches and all of the departments. And how I would further build my analysis of the store sales is to introduce branches and departments. So let’s see how that works with a specific example.

Let’s suppose I want to include branch in my pivot table. If I select branch, which is basically a text field, what will happen is branch automatically gets added to the rows drop zone. And note because of the sequence there I’ve got date first and then branch. So on the 24th April 2012 broken down by the four branches for which I have sales on that date, they are the sums of values of the sales; similarly 25th of April, 26th of April, and so on. To change the order of the data fields in the rows column all I need to do is to grab one, branch in this case, drag it up. Watch what happens to the analysis now. Now my analysis begins with branch and then I have all the days for the Boston branch and then right down to the end of the period in question then I start the Chicago branch. So the sequence of data fields in the rows drop zone determines the sequence in which those data fields appear in the pivot table on the left.

Pivot tables very often involve reporting, including dates. And particularly as we’re often dealing with straightforward transactional data, dates are very often part of that data and it’s usually the case, particularly if you’ve got data over a long period of time that you wouldn’t want to present the data by individual days like this. You’d almost certainly want to group the dates by weeks or months or quarters or years or whatever. So let’s now look at grouping this date data.

On the pivot tables Analyze Tab there is a Group button and if I click on the drop down on the Group button one of the options is Group Field. That shows me the start date for this date field, April 24th and the end date, August 19, 2012. And it allows me to specify grouping. Now at the moment by default it’s suggesting grouping by months but in fact I can group by multiple criteria. I’m not restricted to grouping my month. So let’s suppose I wanted to group by months and quarters. I’ve got them both selected, click on OK, watch what happens. Now what I get is a sort of two level grouping. I’ve got the quarters grouping divided into months and then I can use these Expand and Collapse buttons here to hide the details if I want to.

Now so far this pivot table is still pretty straightforward and hopefully you find it very easy to understand what’s going on. Let’s now make a couple of further changes to it. One of the things I’m going to do is to move branch over to columns. And what I’ve got now is a crosstab pivot table where I’ve got dates for the rows, bear in mind I’ve got quarters and dates as two levels of field in the rows, and then the branches are the columns. And of course within the date rows I’ve got the expand facility here where I can expand a particular quarter to show the figures for the individual months.

Now at any point I can introduce additional data fields. So for instance, let’s suppose I now want to put department in. As soon as I check department by default department because it’s a text field will be added as a row in the table. So I’ve got departments here and departments are below date. So if I expand quarters I’ll actually find below the dates that I’ve got a breakdown for each month. Bear in mind I’m grouped by quarters and months. I’ve got a breakdown by departments.

And I’m sure you can work this out by now. If I wanted to promote department to the top of the rows drop zone I’ve now got an arrangement whereby I’ve got individual departments and then within each department I’ve got the relevant quarters in which we’ve recorded sales in that department. And similarly if I wanted to put departments over as a column and branches back as a row and so on. It’s extremely straightforward to change all of those things just by dragging and dropping within the drop zones.

Now for the rest of this section I’d like to look at formatting what’s in a pivot table. Formatting is very important because we very often use pivot tables as presentational tools so the presentation is all important. In the body of this pivot table of course the numbers we’ve got are basically currency amounts. If I click on one of them, note the screen tip that I get tells me, Sum of value. Value 1192.25, row Boston, May. So it’s telling me which row I’m in. Note that I’ve removed the quarters for simplicity, just to illustrate what we’re going to do next. And then the column I’m on is flatbread. Sum of value is the actual field value that I’m looking at here so that should be a currency amount.

If you click on Analyze in the pivot table tools, in the active field group one of the options is field settings and these field settings apply to the field that’s currently selected. So that will be sum of values. Click on field settings and we get a Value Field Settings Dialogue. Now I could at this point customize the name if I wanted to. And I could also change what I’m doing with that sum. At the moment it’s a sum. I could change it to a count or an average or a max or a min or whatever. Now if I click on number format I can actually do some number formatting. And what I really want this to be is a currency field and I’m going to go for two decimal places. So I want these sum of values to be currency fields with two decimal places. Click on OK, click on OK, and now look at what an improvement that makes to my pivot table.

Just a word of warning there. Don’t be tempted to say select some cells here and do format cells in the way that you would conventionally on a worksheet. The way that we’ve just defined that currency format for sum of values using field settings for the active field, make sure that as the values related to this pivot table change, so if we introduce more departments, new branches, change the dates that we have data for, and so on, then that definition will be used throughout. No matter how we change the source data then the sum of values there will always be formatted in that currency way. If you select a specific range of cells and do the formatting then if the size and shape of the pivot tables changes then the formatting you’ve done will no longer correspond to the size and shape of the new pivot table. So make sure you do it as a definition for field settings for the active field in the way that I did just now.

And one other aspect of the presentational side of a pivot table, the form we’re looking at now is called a compact form. If you look at the Boston figures, for example, the total for Boston here, for each of the departments, let’s say coffee, is shown at the top. So 562.15 is the total. There are only sales there in May. Similarly down for Chicago, etcetera. Now it’s not always best to have totals at the top. Some situations the totals at the top works, some it doesn’t. But there are a number of other ways of presenting a table and if you go to the Design Tab and click on Report Layout you can choose from a number of quite useful options. One of them, for example, is Show in tabular form. Now if you select show in tabular form you get those totals at the bottom, so you get Boston and then a Boston total row; Chicago and then a Chicago total row. And then look again at the Boston figures here. We’ve got Boston, April, May, June, July, August, etcetera. If you wanted to have Boston in each of these cells here, which might be particularly useful if you’ve got many months say, you’ve got many categories, many values, and things move out of view when you can’t see headings and so on one of the other options on the Report Layout button here is to for Repeat all item labels. And what happens in a situation like this is in this case the branch name get repeated. That can be very useful, as I say, if things would otherwise move out of view.

Now there are other settings there on the Report Layout button that I think it’s good for you to experiment with but that’s it for now on the presentational side of pivot tables. In the next section we’re going to look at filtering in pivot tables and in particular we’re going to look at slices, so please join me for that.

Simon C

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!