Create a Calculated Field in Excel Pivot Table


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. So 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. So 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, so 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
lists 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. For more
Excel tips and tutorials. And to download the sample
file for this video, please visit my Contextures
at www.contextures.com

Paul Whisler

13 Comments

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

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

  3. 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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *