Calculate values in a PivotTable

In PivotTables, you can use summary functions in value fields to combine values from the underlying source data. If summary functions and custom calculations do not provide the results that you want, you can create your own formulas in calculated fields and calculated items. For example, you could add a calculated item with the formula for the sales commission, which could be different for each region. The PivotTable would then automatically include the commission in the subtotals and grand totals.

PivotTables provide ways to calculate data. Learn about the calculation methods that are available, how calculations are affected by the type of source data, and how to use formulas in PivotTables and PivotCharts.

To calculate values in a PivotTable, you can use any or all of the following types of calculation methods:

• Summary functions in value fields    The data in the values area summarize the underlying source data in the PivotTable. For example, the following source data:

• Produces the following PivotTables and PivotCharts. If you create a PivotChart from the data in a PivotTable, the values in that PivotChart reflect the calculations in the associated PivotTable report.

• In the PivotTable, the Month column field provides the items March and April. The Region row field provides the items North, South, East, and West. The value at the intersection of the April column and the North row is the total sales revenue from the records in the source data that have Month values of April and Region values of North.

• In a PivotChart, the Region field might be a category field that shows North, South, East, and West as categories. The Month field could be a series field that shows the items March, April, and May as series represented in the legend. A Values field named Sum of Sales could contain data markers that represent the total revenue in each region for each month. For example, one data marker would represent, by its position on the vertical (value) axis, the total sales for April in the North region.

• To calculate the value fields, the following summary functions are available for all types of source data except Online Analytical Processing (OLAP) source data.

  Function	Summarizes
  Sum	The sum of the values. This is the default function for numeric data.
  Count	The number of data values. The Count summary function works the same as the COUNTA function. Count is the default function for data other than numbers.
  Average	The average of the values.
  Max	The largest value.
  Min	The smallest value.
  Product	The product of the values.
  Count Nums	The number of data values that are numbers. The Count Nums summary function works the same as the COUNT function.
  StDev	An estimate of the standard deviation of a population, where the sample is a subset of the entire population.
  StDevp	The standard deviation of a population, where the population is all of the data to be summarized.
  Var	An estimate of the variance of a population, where the sample is a subset of the entire population.
  Varp	The variance of a population, where the population is all of the data to be summarized.

• Custom calculations    A custom calculation shows values based on other items or cells in the data area. For example, you could display values in the Sum of Sales data field as a percentage of March sales, or as a running total of the items in the Month field.

  The following functions are available for custom calculations in value fields.

  Function	Result
  No Calculation	Displays the value that is entered in the field.
  % of Grand Total	Displays values as a percentage of the grand total of all of the values or data points in the report.
  % of Column Total	Displays all of the values in each column or series as a percentage of the total for the column or series.
  % of Row Total	Displays the value in each row or category as a percentage of the total for the row or category.
  % Of	Displays values as a percentage of the value of the Base item in the Base field.
  % of Parent Row Total	Calculates values as follows: (value for the item) / (value for the parent item on rows)
  % of Parent Column Total	Calculates values as follows: (value for the item) / (value for the parent item on columns)
  % of Parent Total	Calculates values as follows: (value for the item) / (value for the parent item of the selected Base field)
  Difference From	Displays values as the difference from the value of the Base item in the Base field.
  % Difference From	Displays values as the percentage difference from the value of the Base item in the Base field.
  Running Total in	Displays the value for successive items in the Base field as a running total.
  % Running Total in	Calculates the value for successive items in the Base field that are displayed as a running total as a percentage.
  Rank Smallest to Largest	Displays the rank of selected values in a specific field, listing the smallest item in the field as 1, and each larger value will have a higher rank value.
  Rank Largest to Smallest	Displays the rank of selected values in a specific field, listing the largest item in the field as 1, and each smaller value will have a higher rank value.
  Index	Calculates values as follows: ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

• Formulas    If summary functions and custom calculations do not provide the results that you want, you can create your own formulas in calculated fields and calculated items. For example, you could add a calculated item with the formula for the sales commission, which could be different for each region. The report would then automatically include the commission in the subtotals and grand totals.

Calculations and options that are available in a report depend on whether the source data came from an OLAP database or a non-OLAP data source.

• Calculations based on OLAP source data    For PivotTables that are created from OLAP cubes, the summarized values are precalculated on the OLAP server before Excel displays the results. You cannot change how these precalculated values are calculated in the PivotTable. For example, you cannot change the summary function that is used to calculate data fields or subtotals, or add calculated fields or calculated items.

  Also, if the OLAP server provides calculated fields, known as calculated members, you will see these fields in the PivotTable Field List. You will also see any calculated fields and calculated items that are created by macros that were written in Visual Basic for Applications (VBA) and stored in your workbook, but you won't be able to change these fields or items. If you need additional types of calculations, contact your OLAP database administrator.

  For OLAP source data, you can include or exclude the values for hidden items when calculating subtotals and grand totals.

• Calculations based on non-OLAP source data    In PivotTables that are based on other types of external data or on worksheet data, Excel uses the Sum summary function to calculate value fields that contain numeric data, and the Count summary function to calculate data fields that contain text. You can choose a different summary function, such as, Average, Max, or Min, to further analyze and customize your data. You can also create your own formulas that use elements of the report or other worksheet data by creating a calculated field or a calculated item within a field.

You can create formulas only in reports that are based on a non-OLAP source data. You cannot use formulas in reports that are based on an OLAP database. When you use formulas in PivotTables, you should know about the following formula syntax rules and formula behavior:

• PivotTable formula elements    In formulas that you create for calculated fields and calculated items, you can use operators and expressions as you do in other worksheet formulas. You can use constants and refer to data from the report, but you cannot use cell references or defined names. You cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions.

• Field and item names    Excel uses field and item names to identify those elements of a report in your formulas. In the following example, the data in range C3:C9 is using the field name Dairy. A calculated item in the Type field that estimates sales for a new product based on Dairy sales could use a formula such as =Dairy * 115%.

  

  Note: In a PivotChart, the field names are displayed in the PivotTable field list, and item names can be seen in each field drop-down list. Don't confuse these names with those you see in chart tips, which reflect series and data point names instead.

• 
  
  

  
  

• Formulas operate on sum totals, not individual records    Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula. For example, the calculated field formula =Sales * 1.2 multiplies the sum of the sales for each type and region by 1.2; it does not multiply each individual sale by 1.2 and then sum the multiplied amounts.

  Formulas for calculated items operate on the individual records. For example, the calculated item formula =Dairy *115% multiplies each individual sale of Dairy times 115%, after which the multiplied amounts are summarized together in the Values area.

• Spaces, numbers, and symbols in names    In a name that includes more than one field, the fields can be in any order. In the example above, cells C6:D6 can be 'April North' or 'North April'. Use single quotation marks around names that are more than one word or that include numbers or symbols.

• Totals    Formulas cannot refer to totals (such as, March Total, April Total, and Grand Total in the example).

• Field names in item references    You can include the field name in a reference to an item. The item name must be in square brackets — for example, Region[North]. Use this format to avoid #NAME? errors when two items in two different fields in a report have the same name. For example, if a report has an item named Meat in the Type field and another item named Meat in the Category field, you can prevent #NAME? errors by referring to the items as Type[Meat] and Category[Meat].

• Referring to items by position    You can refer to an item by its position in the report as currently sorted and displayed. Type[1] is Dairy, and Type[2] is Seafood. The item referred to in this way can change whenever the positions of items change or different items are displayed or hidden. Hidden items are not counted in this index.

  You can use relative positions to refer to items. The positions are determined relative to the calculated item that contains the formula. If South is the current region, Region[-1] is North; if North is the current region, Region[+1] is South. For example, a calculated item could use the formula =Region[-1] * 3%. If the position that you give is before the first item or after the last item in the field, the formula results in a #REF! error.

To use formulas in a PivotChart, you create the formulas in the associated PivotTable, where you can see the individual values that make up your data, and then you can view the results graphically in the PivotChart.

For example, the following PivotChart shows sales for each salesperson per region:

To see what sales would look like if they were increased by 10 percent, you could create a calculated field in the associated PivotTable that uses a formula such as =Sales * 110%.

The result immediately appears in the PivotChart, as shown in the following chart:

To see a separate data marker for sales in the North region minus a transportation cost of 8 percent, you could create a calculated item in the Region field with a formula such as =North – (North * 8%).

The resulting chart would look like this:

However, a calculated item that is created in the Salesperson field would appear as a series represented in the legend and appear in the chart as a data point in each category.