Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Pivot Tables enable you to analyze your data efficiently, but some questions can only be answered by performing calculations on the data used to create your Pivot Table. To perform that type of analysis, you can create custom fields that summarize Pivot Table data using a formula. These calculations are called Calculated Fields. Without Calculated Fields, you would have to copy the data from your Pivot Table, paste it onto another worksheet, and create the formulas there. That's a pain. Rather than copy your data outside the Pivot Table, you can create a Calculated Field within the Pivot Table. To do that, you click any cell in the Pivot Table, and then on the PivotTable toolbar, click the Pivot Table button.
Point to Formulas, and then click Calculated Field. You can use the controls in the Insert Calculated Field dialog box to create your Calculated Field. I will name the Calculated Field 'AveragePerSale.' I will use two fields from my data source. The first is Revenue, the total amount of money earned, and Sales, which counts the number of sales used to earn that revenue. So first I will edit the formulas so that I Backspace over the zero and now in the Fields list I will click Revenue. Click Insert Field to add it to the formula.
Now I will type a Forward Slash, which in Excel means division. I can now add the number of Sales. Click Insert Field. And there is my calculation. Revenue divided by number of Sales. And I can click OK. Excel added my field to my Pivot Table immediately. So now I have two fields in the data area. One of them gives me the total amount of revenue earned for a given month and the second gives me the average revenue per sale. It's important to note that the Calculated Field does not appear in the source table. It only exists in Excel's program memory.
The results in this Calculated Field are hard to read. You can get more information on formatting Pivot Table fields in Chapter 4, Lesson 1, 'Changing the Data Field Number Format.'
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64741 Viewers
80 Video lessons · 124337 Viewers
52 Video lessons · 60269 Viewers
59 Video lessons · 46100 Viewers