# Calculate values in a PivotTable report

In Excel 2010, you can take advantage of improved calculation options.Read an article or try Office 2010! |

In PivotTable reports, you can use summary functions (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) in value fields (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.) to combine values from the underlying source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.). If summary functions and custom calculations do not provide the results that you want, you can create your own formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) 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 PivotTable report would then automatically include the commission in the subtotals and grand totals.

- Learn more about calculating values in a PivotTable report
- Create a formula in a PivotTable report
- View all formulas that are used in a PivotTable report
- Edit a PivotTable formula
- Delete a PivotTable formula

## Learn more about calculating values in a PivotTable report

PivotTable reports 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 PivotTable and PivotChart reports.

### Available calculation methods

To calculate values in a PivotTable report, 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 report. For example, the following source data:

- Produces the following PivotTable and PivotChart reports. If you create a PivotChart report from the data in a PivotTable report, the values in that PivotChart report reflect the calculations 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.).

- In the PivotTable report, the
**Month**column field provides the 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 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 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**Values**field named**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 vertical (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. - To calculate the value fields, the following summary functions are available for all types of source data except Online Analytical Processing (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.

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 worksheet 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 worksheet 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 (custom calculation: A method of summarizing values in the data area of a PivotTable report by using the values in other cells in the data area. Use the**Show data as**list on the PivotTable Field dialog for a data field to create custom calculations.) 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 |
---|---|

Difference From | Displays values as the difference from the value of the Base item in the Base field. |

% Of | Displays values as a percentage of 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. |

% Of Row | Displays the value in each row or category as a percentage of the total for the row or category. |

% Of Column | Displays all of the values in each column or series as a percentage of the total for the column or series. |

% Of Total | Displays values as a percentage of the grand total of all of the values or data points in the report. |

Index |
Calculates values as follows: ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total)) |

For more information, see Change the summary function or custom calculation for a field in a PivotTable report.

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

### How the type of source data affects calculations

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 PivotTable 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 Excel displays the results. You cannot change how these precalculated values are calculated in the PivotTable report. 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 (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) 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 PivotTable reports 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.

### Using formulas in PivotTable reports

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 PivotTable reports, 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 (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 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 report, 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.

### Using formulas in PivotChart reports

To use formulas in a PivotChart report, you create the formulas in the associated PivotTable report, where you can see the individual values that make up your data, and then you can 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 in the associated PivotTable report that uses a formula such as **=Sales * 110%**.

The result immediately appears in the PivotChart report, 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.

## Create a formula in a PivotTable report

** Important ** You cannot create formulas in a PivotTable report that is connected to 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.) data source.

- Decide whether you want a calculated field or a calculated item within a field.
- Use a calculated field when you want to use the data from another field in your formula.
- Use a calculated item when you want your formula to use data from one or more specific 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.) within a field.
- Do one of the following.

**Add a calculated field**

- Click the PivotTable report.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**Calculated Field**. - In the
**Name**box, type a name for the field. - In the
**Formula**box, enter the formula for the field.

To use the data from another field in the formula, click the field in the **Fields** box, and then click **Insert Field**. For example, to calculate a 15% commission on each value in the Sales field, you could enter **= Sales * 15%**.

- Click
**Add**.

**Add a calculated item to a field**

- If items in the field are grouped, on the
**Options**tab, in the**Group**group, click**Ungroup**. - Click the field where you want to add the calculated item.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**Calculated Item**. - In the
**Name**box, type a name for the calculated item. - In the
**Formula**box, enter the formula for the item.

To use the data from an item in the formula, click the item in the **Items** list, and then click **Insert Item** (the item must be from the same field as the calculated item).

- Click
**Add**. - If you ungrouped items in step 1, regroup them if you want. For more information, see Group items in a PivotTable field

- For calculated items, you can enter different formulas cell by cell.

For example, if a calculated item named **OrangeCounty** has a formula of **=Oranges * .25** across all months, you can change the formula to **=Oranges *.5** for June, July, and August.

Do the following:

- Click a cell for which you want to change the formula.

To change the formula for several cells, hold down CTRL and click the additional cells.

- In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), type the changes to the formula.

- If you have multiple calculated items or formulas, adjust the order of calculation by doing the following:

- Click the PivotTable report.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**Solve Order**. - Click a formula, and then click
**Move Up**or**Move Down**. - Continue until the formulas are in the order that you want them to be calculated.

## View all formulas that are used in a PivotTable report

To display a list of all the formulas that are used in the current PivotTable report, do the following:

- Click the PivotTable report.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**List Formulas**.

## Edit a PivotTable formula

- Determine whether the formula is in a calculated field or a calculated item. If the formula is in a calculated item, determine whether the formula is the only one for the calculated item by doing the following:
- Click the PivotTable report.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**List Formulas**. - In the list of formulas, find the formula that you want to change listed under Calculated Field or Calculated Item.

When there are multiple formulas for a calculated item, the default formula that was entered when the 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.) was created has the calculated item name in column B. For additional formulas for a calculated item, column B contains both the calculated item name and the names of intersecting items.

For example, you might have a default formula for a calculated item named **MyItem**, and another formula for this item identified as **MyItem January Sales**. In the PivotTable report, you would find this formula in the Sales cell for the MyItem row and January column.

- Do one of the following:

**Edit a calculated field formula**

- Click the PivotTable report.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**Calculated Field**. - In the
**Name**box, select the calculated field for which you want to change the formula. - In the
**Formula**box, edit the formula. - Click
**Modify**.

**Edit a single formula for a calculated item**

- Click the field that contains the calculated item.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**Calculated Item**. - In the
**Name**box, select the calculated item. - In the
**Formula**box, edit the formula. - Click
**Modify**.

**Edit individual formulas for specific cells of a calculated item**

For example, if a calculated item named **OrangeCalc** has a formula of **=Oranges * .25** across all months, you can change the formula to **=Oranges *.5** for June, July, and August.

- Click a cell for which you want to change the formula.

To change the formula for several cells, hold down CTRL and click the additional cells.

- In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), type the changes to the formula.

- If you have multiple calculated items or formulas, adjust the order of calculation by doing the following:
- Click the PivotTable report.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**Solve Order**. - Click a formula, and then click
**Move Up**or**Move Down**. - Continue until the formulas are in the order that you want them to be calculated.

## Delete a PivotTable formula

** Tip ** If you do not want to delete a formula permanently, you can hide the field or item. To hide a field, drag it out of the report.

- Determine whether the formula is in a calculated field or a calculated item.

Calculated fields appear in the PivotTable Field List. Calculated items appear as 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.) within other fields.

- Do one of the following:

**Delete a calculated field**

- Click the PivotTable report.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**Calculated Field**. - In the
**Name**box, select the field that you want to delete. - Click
**Delete**.

**Delete a calculated item**

- Click the field that contains the item that you want to delete.
- On the
**Options**tab, in the**Tools**group, click**Formulas**, and then click**Calculated Item**. - In the
**Name**box, select the item that you want to delete. - Click
**Delete**.