Calculated field and Calculated Items of a pivot table get their values from the result of a formula.
A calculated field allows the user to insert a new data field into the pivot table – one which does not exist in the base data but gets its value from a formula. The formula in turn can include existing fields, numbers and other arithmetical operators. Let’s understand this a little better. Whenever you create a pivot table, by default, the list of available fields that one can work with is the same as set of columns specified as the source data. So if the pivot table’s underlying data consists of say “Product”, “Month”, “Region”, “Sales Manager”, “Sales Target” and “Actual Sales”, you will only be able to use those four fields and place them in either page, row, column or data fields.
But what if you wanted to use something like a Sales Volume per Sales Manager or % of Goal achieved (Sales Achieved/Goal)? A calculated field allows you to do just that – create a customized field that acts as a combination of one or more already existing pivot fields. Let’s take the example of a derived data field – say % of sales target met. Although this derived field does not exist directly in the pivot table, it can be created using two other fields – actual sales and the sales target. So if we could somehow get the pivot table to provide us a combined field which is equal to sum of sales divided by sum of sales target, our aim would be achieved. This is where a calculated field comes in handy. The best way to visualize a calculated field is to think of it as an additional column in your underlying source data but with restriction that it can only be used as a data field (and not as a row, column or page field). I’ve always felt that a better name would have been “Calculated Data Field”. Let’s see how we can make use of them.
Creating a Calculated Field in Pivot Table in Excel 2003 and earlier
Turn on the pivot table toolbar (View -> Toolbar -> Pivot Table). Now select any cell located inside the pivot table. In the pivot table toolbar, click on “Pivot Table” -> “Formulas” -> “Calculated Field”.
In the box that appears, you can provide the field with a descriptive name say “Percentage of Goal Achieved”. In the Formula box, you can either type in the field names of double click on the existing field names to insert them. In our case we will write something like =ActualSales/ TargetSales. Click Add and then press OK. The field you now begin to show in the Pivot Table.
You can add more calculated fields the same way. Feel free to use the operators (*, / , +, – etc) in the same way as one would in a normal Excel formula. However one point that needs to be kept in mind (and which hinders to usability of Calculated Field feature in Excel) is that formulas in calculated fields tend to operate on the total sum of the field used in the formula and not on individual items within the field. So when you write something like =Actual Sales/ TargetSales the output is equivalent to =SUM(Actual Sales)/SUM(Target Sales) and not = Actual Sales1/ Target Sales1 + Actual Sales2/ Target Sales2 + … … … + Actual SalesN/ Target SalesN. To repeat, the calculated field will not look at individual items so any formula used in a calculated field would work only on the sum of each constituent field and individual items within that field.
Creating a Calculated Field in a Pivot Table in Excel 2007 and later
Inserting a calculated field in a pivot table in Excel 2007 is pretty much the same as in the earlier versions. Click in any part of the pivot table and then click the “Options” tab in the ribbon. Click the “formulas” button and select “Calculate Field”.
Rest of the steps are the same as mentioned above.
Calculated Item in a Pivot Table
Pivot tables in Excel provide yet another useful feature called the Calculated Item. When you add a field to a row, column or page slot in the pivot table, by default all the unique values contained in the column (represented by that field) appear in the drop down and are made available for selection by the users. A calculated item acts as an additional value in the drop down. Think of it as a virtual new item for that field – one which does not exist in the source data but can be made to appear in the field drop-down. Like the calculated field, the calculated item is also based upon a formula – the actual field does not exist in the pivot table and its values get calculated based on the relationships specified in the formula. The formula can contain references to other items of the same field, numbers and operators. For example, let’s assume, that we placed the “Product” in the row field. It has three existing products – Home, Décor and Industrial, all of which appear in the drop down. Adding a new calculated item to the “Product” field will result in it containing one additional field thus resulting in 4 unique values (three existing and one calculated). A calculated item can be inserted in either the row or the column field.
Creating a Calculated Item in a Pivot Table in Excel 2003 and earlier
Let’s say we want to insert a new item in the Product drop down say, Home & Electronics. This particular field, when selected from the drop down, should show us the combined totals for both Home Décor and Electronics. To crate a new calculated item, let’s first turn on the pivot table toolbar (View -> Toolbar -> Pivot Table).
Select either the particular row field or the column field in which you would like to insert a new item. Now select “Pivot Table” -> “Formulas” ->”Calculated Item”. You can now either write your own formula or use the existing items in that field and insert them by double clicking. In our case, we will insert a new item by writing something like =’Electronics’+’Home Décor’.
Let’s provide it the name that we decided earlier – “Electronics & Home”. Press OK. What this will do is to insert a new calculated field, one which would show the combined sum of Electronics and Home Décor. If you look at the pivot table now, you will see that under the Product field, a new item has been inserted and which sums up the relevant figures both from Electronics and Home Décor.
Another interesting way to create calculated items in a pivot table is the use relative references. In the above example, we clubbed two items into a single new item – “Electronics & Home” which contained the totals for both the fields. In the Calculated Field creation box, we could have written = Product[-3] + Product[-2] instead of =’Electronics’+’Home Décor’ (assuming that these two fields immediately precede the new calculated field that we are creating. The [-3] and [-2] actually tell the application to pick up and sum the preceding two fields.
An parameter value of [-1] would have made the application pick a an item 1 steps up backwards and a value of  would make it pick up an item 5 steps forward. Again like the calculated field, a calculated item can use operator like +,-, / and * and other relevant excel formulas. For example a calculated item formula of =IF(Product[-1]>23000000,1,0) +Product[-2] would add 1 to Product[-2] if the total value for Product [-1] exceeds 23,000,000.
Creating a Calculated Item in a Pivot Table in Excel 2007 and later
Like the calculated field, inserting a calculated item in a pivot table in Excel 2007 is again pretty much the same as in the earlier versions. Click on the a particular field (where you would like to insert a calculated item) in the pivot table and then click the “Options” tab in the ribbon. Click the “formulas” button and select “Calculate Item”.
Rest of the steps are the same as mentioned above.
You can download a sample worksheet with a example of Calculated Field and Calculated Item in a Pivot Table in Excel here or click on the button below: