Excel Pivot Table
 |
 |
 |
 |
 |
 |
 |
Excel Pivot Table
Additional Resources
Calculating in Excel Pivot Table | Microsoft Excel XP |
 |
 |
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... Create a Pivot Table Format a Pivot Table with AutoFormats Format a Pivot Table's Cells Change Layout of Pivot Table |
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. | ||
|
|||
|
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 (+ / * -) |
|||
 |
|||
| 5. | When your formula is complete, click Add. | ||
| 6. | Click Close. | ||
After you have created the new calculated field, it appears in the pivot table.
|
|
If you plan on using field names in 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. | ||
|
|||
| 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 names in 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.
| See also... Format a PivotTable | Change Layout of a PivotTable |
| Creating a PivotTable |
| Back to Top |
| Return to Excel XP from Calculating in Excel Pivot Table |
Excel XP Topics
- Tips- Excel Screen Layout
- Navigational Techniques
- 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
- Spreadsheet Data
- AutoFill
- AutoComplete
- Comment
- Find
- Replace
- Spellcheck
- Formulas
- Functions
- Password
- Sorting
- AutoFilter
- Advanced Filter
- Macros
- Charts
- Charting
- Charting Elements
- Gantt Chart
- PivotTable
- PivotTable Calculations
- PivotTable Layout
- PivotTable Format
- PDF to Excel
- PDF-to-Excel Converter
- Excel to PDF Converter