Source data

The values in the data area (data area: The part of a PivotTable report that contains summary data. Values in each cell of the data area represent a summary of data from the source records or rows.) summarize the underlying source data in the report.

PivotTable report made from the above source data

The Month column field (column field: A field that's assigned a column orientation in a PivotTable report. Items associated with a column field are displayed as column labels.) provides items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) March and April. The Region row field (row field: A field that's assigned a row orientation in a PivotTable report. Items associated with a row field are displayed as row labels.) 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 (PivotChart category field: A field that is assigned a category orientation in a PivotChart report. In a chart, categories usually appear on the x-axis, or horizontal axis, of the chart.) that shows North, South, East, and West as categories. The Month field could be a series field (PivotChart series field: A field that is assigned a series orientation in a PivotChart report. In a chart, series are represented in the legend.) 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 (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.) 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 (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains categories.), the total sales for April in the North region.

Values and calculations in a PivotChart report reflect those in the associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), 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 (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) and calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.). 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 (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) database or another type of database.

OLAP source data    For reports that are created from OLAP cubes (cube: An OLAP data structure. A cube contains dimensions, like Country/Region/City, and data fields, like Sales Amount. Dimensions organize types of data into hierarchies with levels of detail, and data fields measure quantities.), 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 (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) or calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.). 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 (macro: An action or a set of actions you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) that were written in Visual Basic for Applications (VBA: A macro-language version of Microsoft Visual Basic that is used to program Windows applications and is included with several Microsoft 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 (page field: A field that's assigned to a page orientation in a PivotTable or PivotChart report. You can either display a summary of all items in a page field, or display one item at a time, which filters out the data for all other items.).

You can create formulas only in reports that are not based on OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) 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 (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), 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 (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) and calculated items (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.), you can use operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) and expressions as you do in other worksheet formulas. You can use constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) and refer to data from the report, but you cannot use cell references or defined names (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.). You cannot use worksheet functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) that require cell references or defined names as arguments, and you cannot use array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) 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 (field: In a PivotTable or PivotChart report, a category of data that's derived from a field in the source data. PivotTable reports have row, column, page, and data fields. PivotChart reports have series, category, page, and data fields.) and item (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) 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 (data area: The part of a PivotTable report that contains summary data. Values in each cell of the data area represent a summary of data from the source records or rows.).

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 (calculated field: A field in a PivotTable report or PivotChart report that uses a formula you create. Calculated fields can perform calculations by using the contents of other fields in the PivotTable report or PivotChart report.) or calculated item (calculated item: An item within a PivotTable field or PivotChart field that uses a formula you create. Calculated items can perform calculations by using the contents of other items within the same field of the PivotTable report or PivotChart report.) in a PivotChart report, the calculations are reflected in the associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.), 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 (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value that originates from a worksheet cell. Related data markers in a chart constitute a data series.) 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.