 Excel Pivot Table

Excel Pivot Table

# Calculating in Excel Pivot Table | Microsoft Excel XP Find out how an Excel Pivot Table really works. Learn step-by-step how to insert a calculated field or item into a PivotTable. You'll also learn how to refresh the PivotTable is it doesn't do it by itself. What's on this page... Inserting a Calculated Field into a PivotTable Inserting a Calculated Item into a Field Display Calculations Refresh a PivotTable See also...

#### Inserting A Calculated Field Into A Pivot Table

Excel lets you create new calculated fields for an Excel pivot table.

This is 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.

For example, if you have a field named TotalSales, you may want to create a calculated field to project sales for another period.

The calculation would use the value of TotalSales.

Calculated fields must reside in the DATA area of the Excel pivot table (you cannot use them in the PAGE, ROW, or COLUMN areas). When to use a Calculated Field?Use a calculated field when you want to use the data from another field in your formula.

## To create a calculated field in an Excel pivot table, follow these steps:

1. Click anywhere within the DATA area of the pivot table to display the PivotTable toolbar. PivotTable toolbar

2. Click PivotTable ► Formulas ► Calculated Field from the PivotTable toolbar.

The Insert Calculated Field dialog box is displayed. 3. In the Name box, type a name for the field.

4. In the Formula box, enter the formula for the field, leaving the equal symbol (=) in place. To use the data from another field in the formula, click the field in the Fields box, and then click the Insert Field button. For example, to calculate a 15% commissionon each value in the Sales field,you could enter = 'Sales'*15%

The following example shows a new calculated field named "SUBTOTAL". This new field uses the two fields "@ each" and "ITEMS SOLD" in its formula.

Notice that you can use the normal mathematical operators (+ / * -) 6. Click Close.

After you have created the new calculated field, it appears in the pivot table. If you plan on using field namesin your calculated field formulas,you should name the fields without spaces.

#### Inserting A Calculated Item To A Field

Excel enables you to create new calculated items for an Excel pivot table.

This is 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, if you have a field named Months, you can create a calculated item (called Q1, for example) that displays the sum of January, February, and March.

Calculated items must reside in the PAGE, ROW, or COLUMN area of a pivot table (you cannot use them in the DATA area).

## To create a calculated item, follow these steps:

1. Click anywhere within the DATA area of the pivot table to display the PivotTable toolbar. PivotTable toolbar

2. Click PivotTable ► Formulas ► Calculated Item from the PivotTable toolbar.

The Insert Calculated Item dialog box is displayed. 3. In the Name box, type a name for the calculated item.

4. In the Formula box, enter the formula for the item leaving the equal symbol (=) in place. 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.

5. Click Add, and then click OK.

After you have created the calculated item, it appears in the Excel pivot table. If you plan on using the item namesin your calculated item formulas,you should name the items without spaces.

#### Display Calculations

If you can't remember what the formula looked like or if you want to check your formulas that you've used, Excel can always display a list of the formulas you've used in your Excel Pivot Table.

## To display a list of formulas, follow these steps:

 1. Click anywhere in the PivotTable report to display the PivotTable toolbar. PivotTable toolbar 2. Click PivotTable ► Formulas ► List Formulas from the PivotTable toolbar. Excel opens a new worksheet in the same workbook and displays a list of all the formulas used in Calculated Fields and Calculated Items.

#### Refresh A Pivot Table

If you change the source data that is used by a pivot table, the pivot table doesn't get updated automatically. Rather, you must refresh it manually.

## To refresh a pivot table, use any of the following steps:

 ► Choose Data ► Refresh Data from the menu bar. ► Right-click anywhere in the PivotTable and choose Refresh Data from the shortcut menu. ► Click the Refresh Data button on the PivotTable toolbar. Refresh Data button

If the source database is large, there may be some delay while this recalculation takes place, but for small databases the update is virtually instantaneous.
###### Excel XP Topics
- Tips
- Excel Screen Layout
- Working with Workbooks
- Templates
- Working with Worksheets
- Moving Around
- Move Worksheets
- Copy Worksheets
- Insert & Delete Cells
- Insert & Delete Rows
- Insert & Delete Columns
- Resize Row
- Resize Column
- Editing Data
- Content Color
- Cell Color
- Number Formats
- Fonts
- Alignment
- Text Direction
- Indent Contents
- Merge Cells
- Copy
- Move
- Undo & Redo
- Using Zoom
- Freeze & Unfreeze Titles
- Split Worksheet
- AutoFill
- AutoComplete
- Comment
- Find
- Replace
- Spellcheck
- Formulas
- Functions
- Print
- Sorting
- AutoFilter