Create a Calculated Field in Excel Pivot Table

In an Excel pivot table, you can create your own formulas, by using a calculated field.

In this video, see how to create a simple calculated field. The calculation multiplies the sales totals by 3%, to show the bonus payment for each sales rep.

Visit my Contextures site to download the sample file, and see more examples of calculated fields.

Instructor: Debra Dalgleish, Contextures Inc.

VIDEO TRANSCRIPT

When you create a pivot table in Excel, you can see a field list that shows all the fields from the original source data. You can also create your own fields by using a calculated field.

We'll add a calculated field in this pivot table, that shows the bonus that each salesperson will earn, based on their total sales.

I've selected a cell in the pivot table and on the Ribbon, under Pivot Table Tools, I've gone to the Options tab. In here I'll click Calculations, Fields, Items & Sets, and here click Calculated Field.

The first thing we'll do is give this a name, and this is going to be the calculated bonus for each sales rep. I'll call it "RepBonus" and press tab to get down to the next box.

This is where we're going to put in the formula. In the pivot table, there's a field called Total, and we can see it in this list of fields here.

I want to use that. I'll double click on it here and it puts that name into the formula.

I want to take that total, and the bonus is going to be 3% of that total. I'll type a space. You don't need a space, but I like to add one just so it's easier to read the formula. Then a multiplier.

I'll put it in an asterisk, another space, and this time I'm going to type in a value. It's nothing from my field list that I can use. I'll type 3% and click Add, and that's adding it to this list.

It's also going to add it to the pivot table, when I click OK. There's the new field, Sum of RepBonus, and we can see that it's 3% for each of these totals.

We can change the heading, so I'll type Bonus, instead of this long title, and then make the column narrower.

So that's a simple calculated field. It's just taking one field in the pivot table and multiplying it by a percentage.

[jonathanlagman]

This has literally saved me hours and hours of unnecessary data summary manipulation. Thank you very much

[nooruddin]

In a short time explained all the needs thanks for the taking care of time while creating the video....Have a good day...

[wayneedmondson]

Thanks Debra.. always a pleasure to view your videos. Thumbs up!

[pipo]

Hi Debra, I have a basic Pivot table with fields (not items). I want add a column showing the difference between the two previous columns of my pivot table. How do I proceed please?

[megan]

I don't have "calculation" under pivot table? Does it need to be added or my version of Excel is just too old?

[prakashsathyapriya]

Hello sir, please let me know how to calculate % of works completed against total no of works in pivot table and get a report in sub and grand total. Thanks in advance

[JurijFedorov]

But I needed to select a certain name under fields. If I just select "Names" it will not know which names I want to use.

[ronwb]

I'm not clear on this concept. Its on the Excel Expert exam.