|
|
Search
Contextures Sites
|
||
Excel Pivot Table Calculated Item
|
|
|
|||
|
||||
Contextures Inc., Copyright ©2011
All rights reserved.
Search Contextures Sites
Excel Pivot Table Calculated Item
Create a Calculated Item
Modify a Calculated Item
Use Index Numbers in a Calculated ItemDownload the Calculated Item sample file and the Calculated Item Index sample file.
For information on Calculated Fields, please visit:
Excel Pivot Table Calculated Field
Create a Calculated Item
In addition to the existing items in a pivot table field, you can create one or more calculated items.
Warning: If you create a calculated item in a field, you will be unable to move the field to the Report Filters area and you will be unable to add multiple copies of a field to the Values area.
In this example, the pivot table contains an Order Status field, and the orders currently have the following types of status:
- Shipped
- Pending
- Backorder
- Canceled
In the pivot table, you could create a calculated item, named Sold, to sum all the units that have been sold, for orders with a status of Shipped, Pending, or Backorder.
Follow these steps to create a calculated item:
- In the pivot table, select a cell that contains an Order Status item. For example, select cell A5, that contains the Backorder item.
- On the Ribbon’s Options tab, click Calculations
- Click Fields, Items & Sets, and then click Calculated Item.
- Note: If the Excel window is wide enough, you'll see Fields, Items & Sets as a separate command.
- In Excel 2007, on the Ribbon’s Options tab, in the Tools group, click Formulas, and then click Calculated Item.
- Type a name for the Calculated Item, for example, Sold, and then press the Tab key to move to the Formula box.
- In the Fields list, select Order Status, and in the Items list, double-click Shipped, and then type a plus sign (+).
- Double-click Pending, type a plus sign, and then double-click Backorder. The complete formula is
=Shipped + Pending + Backorder
Note: You can include the space characters or omit them.
- Click OK, to save the calculated item, and to close the dialog box.
The new calculated item, Sold, is added to the Row area in the pivot table.
However, the Grand Totals have increased, because the Sold item duplicates the values from other items.
Hide the Unnecessary Pivot Items
In the pivot table, you can hide the Shipped, Pending, and Backorder items, because they are included in the Sold calculated item.
With those pivot items hidden, the pivot table will show the correct Grand Totals.
Modify a Pivot Table Calculated Item
After you create a calculated item in a pivot table, you might need to change its formula.
In the previous section, you created a calculated item named Sold, in the Order Status field. The Sold item sums the orders with a status of Shipped, Pending, or Backorder.
You can change the calculated item's formula, so it doesn’t include the Backorder items.
Follow these steps to modify the calculated item:
- In the pivot table, select one of the Order Status items. For example, select cell A6, which is the Canceled item.
- On the Ribbon’s Options tab, click Calculations
- Click Fields, Items & Sets, and then click Calculated Item.
- Note: If the Excel window is wide enough, you'll see Fields, Items & Sets as a separate command.
- In Excel 2007, on the Ribbon’s Options tab, in the Tools group, click Formulas, and then click Calculated Item.
- In the Calculate Item dialog box, click the drop down arrow for the Name box.
- Select Sold, which is the name of the calculated item you want to change.
- In the Formula box, change the formula, to remove the +Backorder.
- The revised formula is =Shipped+Pending.
- Click Modify, to save the change, and then click OK to close the dialog box.
Download the Sample File
To see the pivot table data and the calculated item, you can download the Calculated Item sample file. The file is in Excel 2010/2007 format, and is zipped.
Use Index Numbers in a Calculated Item
Instead of item names, you can use index numbers in a calculated item's formula. This can be a helpful solution if the pivot table source data changes each month, to use the previous month's data. Instead of refering to specific dates in the calculated field, use the index numbers.
Download the Calculated Item Index sample file.
For example, to sum the data for the first date in the OrderDate field and the fifteenth date, create a calculated item in the OrderDate field, with the following formula:
= OrderDate[1]+OrderDate[15]
You can also refer to pivot items by their index number, relative to the calculated item. For example, you could create a calculated item named DateCalc, with the following formula:
= OrderDate[+3]-OrderDate[+2]
If the DateCalc calculated item is moved to the top of the list of OrderDates, it calculates the difference between the value for the OrderDate that is three rows below and the OrderDate that is two rows below.
Tip: To move the item, right-click the DateCalc item, click Move, and then click Move "DateCalc" to Beginning.
Warning: If you move the calculated item into one of the referenced positions, you create a circular reference.
Warning: If you use a negative number in the relative position, the number is automatically changed to a positive number, and the formula will not produce the expected results.
Download the Calculated Item Index sample file.
Contextures Inc., Copyright ©2011
All rights reserved.
Last updated: March 17, 2011
