Excel: Calculated Fields and Items in Pivot Tables

Pivot Tables, Calculated Fields and Items. Excel provides a way to perform calculations within a pivot table through calculated fields and items. This is additional data created by executing a calculation against fields in the pivot table.

So you're taking information that's already there in the pivot table and you're performing some kind of calculation. It could be multiplication, division, addition, subtraction, but some kind of calculation. We're going to do this here with this pivot table.

The first step, as always, is we recommend taking your table of data and turning it into a table. Keyboard shortcut you can use to do that is CTRL T. Then you'll press ENTER and you've created a table.

From there, on the table design tab, or it may be table for you or design, you'll click in the tools group. You'll click summarize with pivot table. I'm not going to go to a brand new sheet.

I'm actually going to create the pivot table in this sheet. So I'll choose existing sheet. And in the location box, I'll click on the gray cell because that is going to be the upper left hand corner of my pivot table.

I'll click OK. And there it is. In our instructions, we're asked to choose city.

That's automatically going to go into rows. And then we'll also choose sales and contract hours. Now I can change the name of sum of sales to total sales because that's what it is.

And then sum of contract hours is actually total hours. The goal of this exercise is to figure out how much money we're making per hour. So Atlanta, for instance, made $136,000 in sales.

And that took 3780 hours. I would like to know how much money that is per hour. Now, this is something that you might think you would calculate by heading over to the original table and then dividing each of these values and coming up with that calculation in another column.

But this is something that you can do within the pivot table without even relying on the source data. Usually when you create a pivot table, it's recommended that you take raw information and perform any calculations within the pivot table rather than the original source data. So what I'm going to do here is go to pivot table analyze.

I'll head over to field items and sets. That's the area that we're going to go to. I'll click the dropdown and I want to insert a calculated field.

This opens up a dialog box, the insert calculated field dialog box. And the first step is to give the field a name. I'm going to call this AVG for average dollar sign slash HR, short for average dollar per hour.

Now, the formula is not going to be equal to zero. So I'll backspace on the zero. The formula is going to be equal to one field divided by another.

So sales, I can click the insert field button to add it. Then I'm going to divide that by the total contract hours. I could click insert field or I can simply just double click on contract hours.

And there it is. Now, right now, the field hasn't been created yet. I just need to do one more thing, and that is click add.

Take a look down at the bottom of the list of fields here in the fields box, as well as in the pivot table field list. The last field we have is contract hours. As soon as I click add, I've added the additional field.

I don't have to do anything other than click OK to see it in my pivot table. And there it is. Now I can see how much money I'm making per hour for all the locations when we take a look at their total sales and total hours.

I might go and just change the name of sum of average dollar per hour. Maybe I'll just call it average dollar per hour. So AVG dollar sign slash HR.

I'll press ENTER and you might get this dialog box. The pivot table field name already exists, so you can't use a header name that's exactly the same as your pivot table field name. So you can make a slight adjustment.

And this is a slight adjustment I'm going to make. I'm just going to hit the space bar. That will make the field name different enough to allow me to press ENTER and have that as a name.

Now let's take a look at calculated item. We'll move down the sheet. And we have a smaller set of information to work with here.

Again, I'll turn this into a table by pressing control T. I'll press ENTER. I'll head over to table design, summarize pivot table, existing worksheet. I'll click location and select the gray cell.

I'll click OK. This is a pretty easy pivot table to create because there are only two fields. In fact, I can create this pivot table simply by checking the boxes for period and sales.

There it is. It's already created. Now what I would like to do is figure out average sales per year by coming up with an average of all the quarters.

So this is also going to be a calculation using fields that already exist in the pivot table. So I'm going to head over to pivot table analyze. I'll go over to field items and sets and I'm going to click on calculated item.

As you can see, I can't do that. And the reason is if you're going to create a calculated item, you need to be over in the row labels column. This is not going to be a value field.

We shouldn't be in values. I'm going to head over to the row label section. Then when I go back to field items and sets, I will see calculated item.

So I just wanted to make sure to highlight that. I'm going to click calculated item. My goal is to figure out the average amount for the entire year by coming up with an average of all the quarters.

So I'm going to call this AVG 2015 sales. That's the first year I'm going to start with. The formula is not zero.

So I'll backspace on the zero and I can simply type average. So this is very similar to using the average function in your Excel spreadsheet. I will double click on quarter one, enter a comma, double click on quarter two, comma, double click quarter three, comma, quarter four, close parentheses.

I'll click add and I will not see the field listed over in the pivot table field list or in my fields dialog box here. So what I'll do is simply click okay and it shows up at the bottom. Now something you need to be aware of, this throws off the grand total because we're counting both the average of 2015 as well as 2015.

So be aware of that. A couple of ways you can resolve it. You can just show the average 2015 sales so you can filter for that.

Here's a quick example of that. I'll just do that. There is no conflict and the total is correct.

The other thing you can do is you can simply not select 2015 sales and then you'll click okay. Now if someone wanted to see both the quarters and the sales, there's something else that you can do. Maybe not as effective.

You can go to the design tab of the two pivot table context menus you have at the top. We'll go over to grand totals and simply turn it off. Now we won't see the grand total so that way you can't be incorrect.

Now one last thing before we move on. I'm going to show you how to edit your fields. If I go back to the pivot table field list and I want to make an edit, I'll go over to pivot table analyze field items and sets and I'll click calculated field.

This is where it becomes a little confusing. This is what I want to edit but this is the actual field so I can't right click on it and if I insert it, I'll be inserting it into a new calculation. So I don't see any options here that are really apparent.

You'll just have to know that you have to click the drop down and when you see the value that you're looking for, the one that you created, you can select it. Then you can go in and make edits to it and modify it or delete it. So basically those are your options.

The same goes with calculated items. So this section, in this section, we covered calculated fields and calculated items.