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.
Get Debra's weekly Excel tips: [ Ссылка ]
More Excel Tips and Tutorials: [ Ссылка ]
Subscribe to Contextures YouTube: [ Ссылка ]
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.
For more Excel tips and tutorials and to download the sample file for this video, please visit my Contextures website at www.contextures.com
