See how to create a simple calculated field in a pivot table in Excel, to show the bonus that each sales representative will receive. The formula will multiply each person’s total sales by 3%, to create a new value in the pivot table.
Create a Calculated Field in a Pivot Table
In this example, the pivot table shows the total sales for each sales representative per product, and the Units field summarizes the number of units sold. The sales reps will earn a 3% bonus on their Total Sales.
To show the bonus amounts, you can created a calculated field in the pivot table. Learn more about pivot table calculated fields on my Contextures site.
Create a Simple Calculated Field
This short video shows the steps to create a calculated field in a pivot table in Excel. To follow along, get the pivot table sample file from my Contextures site. There are written instructions on that page too.
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 “Rep Bonus” 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.
And it’s also going to add it to the pivot table when I click OK. There’s the new field, sum of “Rep Bonus” 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.
________________
Create a Simple Calculated Field
_____________
__________________