Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
PivotTables enable you to analyze your data efficiently, but some questions can only be answered by performing calculations on the data used to create your PivotTable. To perform that type of analysis you can create custom fields that summarize PivotTable data using a formula. Without calculated fields, you'd have to copy the data from your PivotTable, paste it on another worksheet, and create the formulas there and that's a pain. Now you have to dig a little to find the button you click to create a calculated field, but you can find it by clicking the Options contextual tab on the Ribbon and then in the Calculations group click Fields, Items & Sets, and the item you want on this menu is Calculated Field.
When you click Calculated Field the Insert Calculated Field dialog box appears. Now you can type a name for your calculation and in this case I will call it Average Sale, and now I can create the formula. To create a formula I'll click in the Formula box and backspace to delete the zero and now I can insert fields and any mathematical operators that I want. So I want to find the average value of each sale, so I'm going to divide total revenue per month by the number of sales per month.
So for that I'll click the Revenue field here in the Fields list, and click Insert field and then type a forward slash, which means a divide in Excel, and then I'll click Sales Count, Insert Field, and then click OK. When I do, Excel adds the Average Sale field to the body of the PivotTable. Now even though I don't know the number of sales for a given month, I know that in January I had a total revenue of 67 and the average revenue per sale was 9.57 approximately.
You should note that even though the newly calculated field appears here in the PivotTable Field List it hasn't been added to the original data source. So for example, this PivotTable was built off of data found on Sheet2 in this worksheet, and you'll see that the only data I have are for the Year, Quarter, Month Company, Revenue, and Sales Count. The new field, Average Sales, only exists within the PivotTable's data cache, which is its temporary memory. So you can see that calculated fields extend the type of analysis that you can perform in Excel, making PivotTables even more useful for your operational analysis.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91085 Viewers
80 Video lessons · 138151 Viewers
59 Video lessons · 56963 Viewers
52 Video lessons · 70595 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.