Show All

Hide All

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.) from 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.) databases doesn't allow you to create formulas.

1. Decide whether you want 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 a 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.) 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.

2. Do one of the following. For best results in a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.), work 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 data values that your formula calculates.

   Add a calculated field

   1. Click the report.
   2. On the PivotTable toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click PivotTable or PivotChart, point to Formulas, and then click Calculated Field.
   3. In the Name box, type a name for the field.
   4. 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%.

   5. Click Add, and then click OK.

   Add a calculated item to a field

   1. If items in the field are grouped, right-click each group, point to Group and Outline on the shortcut menu (shortcut menu: A menu that shows a list of commands relevant to a particular item. To display a shortcut menu, right-click an item or press SHIFT+F10.), and then click Ungroup.
   2. Click the field where you want to add the calculated item.
   3. On the PivotTable toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click PivotTable or PivotChart, point to Formulas, and then click Calculated Item.
   4. In the Name box, type a name for the calculated item.
   5. 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).

   6. Click Add, and then click OK.
   7. If you ungrouped items in step 1, regroup them if you want.
3. For calculated items, you can enter different formulas cell by cell.

   How?

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

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

   2. 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.
4. If you have multiple calculated items or formulas, adjust the order of calculation.

   How?

   1. Click the report.
   2. On the PivotTable toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click PivotTable or PivotChart, point to Formulas, and then click Solve Order.
   3. Click a formula, and then click Move Up or Move Down.
   4. Continue until the formulas are in the order that you want them to be calculated.

 Note   When you add a formula to a PivotChart report or its associated PivotTable report, some chart formatting may be lost.