Excel Pivot Tables, Calculated Fields and Items

Pivot Tables are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

Pivot Tables, Calculated Fields, & 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 gonna do this here with this Pivot Table.

Create a 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 Control 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 3,780 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.

Performing Calculations

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, & Sets. That's the area that we're gonna 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, $/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.

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 Fields 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 could 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 Avg$/HR. Maybe I'll just call it Avg$/HR. I'll change it and 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 Fields name.

So you can make a slight adjustment, and this is a slight adjustment I'm gonna make. I'm just going to hit the spacebar. That will make the field name different enough to allow me to press ENTER and have that as the name.

Calculated Item

Now let's take a look at a Calculated Item. We'll move down to 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 a 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.

Average Sales per Year

Now what I would like to do is figure up 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 Pivot Table Analyze. I go over to Fields, Items, & 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, so we shouldn't be in V alues. I'm going to head over to the Row Labels section. Then when I go back to Field, Items, & 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 parenthesis. I'll click add and I will not see the field listed over in the PivotTables Field list or in my Fields dialog box here. So what I'll do is simply click OK, and it shows up at the bottom.

Caution

There's 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 could 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. Another thing you can do is you can simply not select 2015 sales and then you click OK.

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 Pivot, 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.

Edit Fields

Now, one last thing before we move on, I'm gonna show you how to edit your fields. If I go back to the Pivot Table Fields list, and I want to make an edit, I'll go over to Pivot Table Analyze, Field, Items, & 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.

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 dropdown, 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.

Recap

So in this section, we covered Calculated Fields and Calculated Items.