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 performing some kind of calculation. It could be multiplication, division, addition, subtraction, or some other 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. The 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 or Design for you), you'll click in the Tools group. You'll click Summarize with PivotTable. I'm not going to use a brand-new sheet.

I'm actually going to create the Pivot Table in this sheet. So I'll choose Existing Worksheet. 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. 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 Sum of Contract Hours to 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 3,780 hours. I would like to know how much money that is per hour. Now, this is something you might think you would calculate by heading over to the original table and divide each of these values, coming up with that calculation in another column.

But this is something 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 the PivotTable Analyze tab.

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. The first step is to give the field a name. I'm going to call this AVG for average dollar per hour (AVG $/HR).

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

So Sales; I can click the Insert Field button to add it. Then I'll divide that by Contract Hours. I can click Insert Field or simply double-click on Contract Hours.

And there it is. 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 PivotTable 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 PivotTable. 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 $/HR.

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

This is a slight adjustment I'm going to make. I'll just press 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. We have a smaller set of information to work with here.

Again, I'll turn this into a table by pressing CTRL+T. I'll press ENTER. I'll head over to Table Design, Summarize with PivotTable, 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. I'm going to head over to PivotTable Analyze. I'll go to Field Items and Sets and click on Calculated Item.

As you can see, I can't do that. The reason is if you're going to create a Calculated Item, you need to be over in the Row Labels column. This cannot be a Value field.

We shouldn't be in Values. I'm going to head over to the Row Labels 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.

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 the zero and type AVERAGE. This is very similar to using the AVERAGE function in your Excel spreadsheet. I will double-click on Quarter 1, enter a comma, double-click on Quarter 2, comma, double-click Quarter 3, comma, Quarter 4, close parentheses.

I'll click Add and will not see the field listed in the PivotTable Field List or in my Fields dialog box. So I'll simply click OK, and it shows up at the bottom.

Now, something you need to be aware of: this affects the grand total because we're counting both the Average 2015 Sales as well as 2015.

Be aware of that. A couple of ways you can resolve it: You can just show the Average 2015 Sales and filter by that.

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

The other thing you can do is simply not select 2015 Sales, then click OK. Now, if someone wanted to see both the quarters and the sales, there's something else you can do, though it may not be as effective.

You can go to the Design tab of the two PivotTable context menus at the top. We'll go to Grand Totals and simply turn them off. That way, the totals won'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 PivotTable Field List and want to make an edit, I'll go to PivotTable Analyze > Field Items and Sets and 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. If you insert it, you'll be inserting it into a new calculation. I don't see any apparent options here.

You just have to know that you have to click the dropdown, and when you see the value that you're looking for (the one that you created), you can select it, then go in and make edits, modify it, or delete it. Basically, those are your options.

The same goes for Calculated Items. So in this section, we covered Calculated Fields and Calculated Items.