Macro Sheets Only
Adds a field to a PivotTable.
Syntax
PIVOT.ADD.DATA(name, pivot_field_name, new_name, position, function, calculation, base_field, base_item, format_text)
Name is the name of the PivotTable to which the user wants to add as a data field. If name is omitted, Microsoft Excel will use the PivotTable containing the active cell.
Pivot_field_name is the name of a field which the user would like to add to his PivotTable as data or as text.
New_name is the name you would like to give to the new field once it is added to your PivotTable. If this argument is omitted, Microsoft Excel will pick a default name for you. This function returns new_name or the name Microsoft Excel chooses for the field.
Position is the position within all the Data fields you would like to place the new data field. If position is omitted, the field will be added as the last data field.
Function is a number from 2 to 2048 specifying how the new field is to be calculated. To compute the value to place in this column choose one value from the following table. If function is omitted, SUM will be used. If the field is a numeric field or text field, COUNTA will be used.
Value Function
2 SUM
4 COUNTA
8 COUNT
16 AVERAGE
32 MAX
64 MIN
128 PRODUCT
256 STDEV
512 STDEVP
1024 VAR
2048 VARP
Calculation is a number between 1 and 9 representing which custom calculation you would like to apply to this data field. This corresponds to the Show Data As drop-down box on the PivotTable Field dialog box. If this argument is omitted, no special calculation will be applied to the data field.
Value Calculation
1 Normal
2 Difference From
3 % Of Item
4 % Difference From
5 Running Total In
6 % of Row
7 % of Column
8 % of Total
9 Index
Base_Field is the field on which you want to base the Calculation.
Base_Item is the item within base_field on which you want to base the Calculation.
Format_text is the type of number format you want to apply to the PivotTable data. Corresponds to the number button in the PivotTable Field dialog box, which appears when you choose the PivotTable Field command from the Data menu when the selection is in a data field.
Remarks
Related Functions
PIVOT.ADD.FIELDS Adds fields to a PivotTable
PIVOT.FIELD Pivots fields within a PivotTable
PIVOT.FIELD.GROUP Creates groups within a PivotTable
PIVOT.FIELD.PROPERTIES Changes the properties of a field inside a PivotTable
PIVOT.FIELD.UNGROUP Ungroups all selected groups within a PivotTable
PIVOT.ITEM Moves an item within a PivotTable
PIVOT.ITEM.PROPERTIES Changes the properties of an item within a header field
PIVOT.REFRESH Refreshes a PivotTable
PIVOT.SHOW.PAGES Creates new sheets in the workbook containing the active cell
PIVOT.TABLE.WIZARD Creates an empty PivotTable
List of Command-Equivalent Functions