Categorized | pivot table

Calculated field and Calculated Items in a Pivot Table




Calculated field and Calculated Items of a pivot table get their values from the result of a formula.

Calculated Field

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.

calculated-field-pivot-table-data

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”.

calculated-field-in-a-pivot-table

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.

calculated-field-formula-in-a-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”.

calculated-field-in-a-pivot-table-excel-2007

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).

calculated-item-in-a-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’.

calculated-item-formula-in-a-pivot-table

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.

calculated-item-relative-formula-in-a-pivot-table

An parameter value of [-1] would have made the application pick a an item 1 steps up backwards and a value of [5] 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”.

calculated-item-in-a-pivot-table-excel-2007

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:


calculated-field-and-calculated-item-in-excel


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Patrick wrote:

    Excellent overview of Calculated Item/Calculated Field functionality!

  2. Kurt wrote:

    The example shows the problem with the calculated Item: Even though Albert do not sell anything in the Electronics and Home decor product group, Albert shows in the new Electronics & Home group => pivot table size can be extremely big and take ages to calculate => almost useless feature unless pivot tables are very small.

  3. Mohsin wrote:

    I need your help for b/m code.
    B/m code is fine for 65536 number of rows or less.But when number of rows exceeds 65536, it gives an error”Type Mismatch”.
    Please help in this regard.I am using Excel 2010.

    Sub test()
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=””, TableName:=”PivotTable8″, DefaultVersion:=xlPivotTableVersion14
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.ChartArea.Copy
    Sheets(“Sheet2″).Select
    ActiveSheet.Paste
    End Sub

  4. Lee Mei Ling wrote:

    Dear Sir/Madam,

    I would like to do calculated field. But wonder why my formula button is disable??

    do let me know if you would like to see the print screen?

    Thanks

  5. Jack Nimble wrote:

    I think in this sentence:

    A calculated field acts as an additional value in the drop down.

    You meant to write:

    A calculated item acts as an additional value in the drop down.

  6. Casey wrote:

    Hi all – I have a P&L constructed in a form of pivot table with a # of calculated fields and calculated items. When dragging the fields around in the pivot – usually 2 — it takes about 3-5 min to complete refreshing (because it seems to calculate every item in the pivot, not just the fields/items I am filtered on). Adding a 3rd field can take 20 + mins. A 4th crashes excel. How can I speed this process up? Turning off option DEFER LAYOUT UPDATE in table options doesn’t work here….when I make the update with it off it still takes the same amount of time. The source data has about 10,000 rows only….

    If anyone has any advice here I would really appreciate it!

  1. Keyboard Shortcuts in Excel to Sort, Add, Hide Pivot Table Items and Fields | Excel & VBA - da Tab Is On wrote:

    [...] any cell in the data field and presss SHIFT + CTRL + = (Shift key then Ctrl key then =). The insert calculated field dialog box will open [...]

  2. Yet another strange pivot table question wrote:

    [...] when you build the PivotTable.Some useful resources for further information on this topic include: Calculated field and Calculated Items in a Pivot Table | Excel & VBA – DatabisonExcel Pivot Table Calculated [...]

  3. Excel for Actuaries #17 – PivotTables | Benson's Blog wrote:

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel