About calculations and formulas in PivotTable and PivotChart reports

Source data

The values in the data area summarize the underlying source data in the report.

PivotTable report made from the above source data

The Month column field provides items March and April. The Region row field provides 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.

PivotChart report made from the example PivotTable report

In a PivotChart report, 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 data field called 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 value axis, the total sales for April in the North region.

Values and calculations in a PivotChart report reflect those in the associated PivotTable report, and vice versa.

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.

Formulas    If summary functions and custom calculations don't provide the results 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 available in a report depend on whether the source data came from an OLAP database or another type of database.

OLAP source data    For reports that are created from OLAP cubes, the summarized values are precalculated on the OLAP server before Microsoft Excel displays the results. Therefore, you cannot change how these values are calculated from within the report. You cannot change the summary function used to calculate data fields or subtotals, or add calculated fields or calculated items. If the OLAP server provides calculated fields, known as calculated members, you'll see these fields in the PivotTable field list. You'll also see any calculated fields and calculated items that are created by macros that were written in Visual Basic for Applications 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.

Other types of source data    In reports based on other types of external data or on worksheet data, Microsoft Excel uses the Sum summary function to calculate data 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.

Hidden items in totals    For OLAP source data, you can include or exclude the values for hidden items when calculating subtotals and grand totals. For other types of source data, values for hidden items are excluded by default, but you can optionally include the hidden items from page fields.

You can create formulas only in reports that are not based on OLAP source data.

Formulas are available in PivotChart reports and use the same syntax as those in PivotTable reports. For best results when working in a PivotChart report, create and edit formulas in the associated PivotTable report, where you can see the individual values that make up your data, and then view the results in the PivotChart report.

Formula elements    In formulas 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.

Names in reports    Microsoft Excel provides names to identify the elements of a report in your formulas. The names are composed of the field and item names. In the following example, the data in range C3:C9 is named Dairy.

In a PivotChart report, the field names are displayed in the field buttons, 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.

Examples    A calculated field named Forecast could forecast future orders with a formula such as the following:

=Sales * 1.2

A calculated item in the Type field that estimates sales for a new product based on Dairy sales could use a formula such as the following:

=Dairy * 115%

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 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, however, operate on the individual records; the calculated item formula =Dairy *115% multiplies each individual sale of Dairy times 115%, after which the multiplied amounts are summarized together in the data 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 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 you give is before the first item or after the last item in the field, the formula results in a #REF! error.

In calculated item formulas, if you refer to items by their position or relative position, any options you have set under Top 10 AutoShow and AutoSort options in the PivotTable Sort and Top 10 or PivotTable Field Advanced Options dialog boxes are reset to Off or Manual, and the options become unavailable.

The methods and rules used for creating formulas in PivotChart reports are the same as the rules for PivotTable reports. When you create a calculated field or calculated item in a PivotChart report, the calculations are reflected in the associated PivotTable report, and vice versa. For best results, create formulas for a PivotChart report in the associated PivotTable report, where you can see the individual values that make up your data, and then view the results graphically in the PivotChart report.

For example, the following PivotChart report 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 with the following formula:

=Sales * 110%

The result would be reflected in the chart like this:

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 the following formula:

=North – (North * 8%)

The result would look like this:

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