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 calculated fields that summarize pivot table data using a formula. 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. It's extra work that you absolutely do not have to go through. Now how to create a calculated field in an Excel 2007 pivot table isn't the most intuitively obvious thing.
So I'll just show you where it is on the user interface so you can do it later. To create a calculated field, first you select any cell in the pivot table and then on the Pivot Table Tools Options tab of the ribbon, you go to the Tools group and click Formulas and that's where you find the Calculated Field menu item. So click that and you get the Insert Calculated Field dialog box. And what you do here is create a name for your field.
The goal for this field is to calculate a company's revenue as a proportion of the cost of goods sold. So it will be dividing revenue by COGS. So for that one, I'll just call it Times COGS, press Tab and the formula will be equal. And then again, I want to divide revenue by cost of goods sold. So I'll click Revenue, insert that field into the formula, forward slash to indicate division and then I insert the Cost of Goods Sold field.
So there we have our calculated field and everything looks correct. So I'll click OK. The Times COGS field appeared in the Pivot Table Field List and it also appears down here in the pivot table values area. So at present, our pivot table has two data fields. We have the sum of revenue and we then we also have the sum of times cost of goods sold. So I'll remove the Revenue field from the data area for the moment and you can see here, in January 2008, we had a ratio of 17:1 and the data field is currently configured the way it was before to display numbers as a currency or accounting field.
So I'm going to change the data type just to make it easier to read. To do that, you right-click a cell that contains the data that you want to change and click Number Format and from the list of available values, in this case I'll click Number and I'll put two decimal places on it. Click OK and there we have it. We have a ratio of 16.75, which again is different from 17. The rounding got taken care of when we added the decimal points. Calculated fields extends the types of analysis you can perform in Excel, which makes pivot tables even more useful for your financial analyses.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 75213 Viewers
80 Video lessons · 130049 Viewers
52 Video lessons · 64197 Viewers
59 Video lessons · 49997 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.
Your file was successfully uploaded.