Search Contextures Sites

Excel Pivot Table Calculated Field

Amazon.com      Amazon.com	Pivot Table Calculated Field Add a Pivot Table Calculated Field Remove a Pivot Table Calculated Field Programmatically Remove Pivot Table Calculated Field Create List of Pivot Table Formulas Pivot Table Tutorials and Videos
	Pivot Table Calculated Field
	In a pivot table, you can create a new field that performs a calculation on the sum of other pivot fields. For example, in the screen shot below, a calculated field, named Bonus, has been created, and it will calculate 3% of the Total, if the sum of Units is greater than 100. About Calculated Fields For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount. Calculated field formulas cannot refer to the pivot table totals or subtotals Calculated field formulas cannot refer to worksheet cells by address or by name. Sum is the only function available for a calculated field.
	Add a Pivot Table Calculated Field In this example, the pivot table shows the total sales for each sales representative per product, and the Units field summarizes the number of units sold. The sales reps will earn a 3 percent bonus if they have sold more than 100 units of any product. To show the bonuses, you can add a calculated field to the pivot table. In this example, the formula will test the Units field, to see if more than 100 units were sold, and multiply the Total field by 3%. To add a calculated field: Select a cell in the pivot table, and on the Excel Ribbon, under the PivotTable Tools tab, click the Options tab. In the Tools group, click Formulas, and then click Calculated Field. Type a name for the calculated field, for example, Bonus. In the Formula box, type =IF(Units>100,Total*3%,0). Click Add to save the calculated field, and click Close. The Bonus field appears in the Values area of the pivot table, and in the field list in the PivotTable Field List.
	Remove a Pivot Table Calculated Field In this example, the pivot table has a calculated field named Bonus. It appears in the Values area as Sum of Bonus. You could temporarily hide the Bonus calculated field, or permanently delete it from the pivot table.
	Temporarily Remove a Calculated Field To temporarily remove a calculated field from a pivot table, follow these steps: In the pivot table, right-click a cell in the calculated field. In this example, we’ll right-click the Bonus field. In the popup menu, click the Remove command that shows the name of the calculated field.   The calculated field is removed from the pivot table layout, but remains in the PivotTable Field List. Later, you can add a check mark to the calculated field in the PivotTable Field List, to return it to the pivot table layout. Permanently Remove a Calculated Field To permanently remove a calculated field, follow these steps to delete it: Select any cell in the pivot table. On the Ribbon, under the PivotTable Tools tab,  click the Options tab. In the Tools group, click Formulas, and then click Calculated Field. From the Name drop down list, select the name of the calculated field you want to delete.   Click Delete, and then click OK to close the dialog box.
	Programmatically Remove Pivot Table Calculated Field In Excel VBA, if you try to change the Orientation for a calculated field, Excel displays the error message "Run-time error '1004': Unable to set the Orientation property of the PivotField class" You can manually uncheck the calculated field boxes, and remove them from the pivot table, then check the box again, to put it back into the layout. However, if you record code while removing the calculated field, that recorded code shows the same error message when you try to run it. So, I wrote the following code that deletes each calculated field, then immediately adds it back to the pivot table field list, but not into the pivot table layout. If you've been having the same trouble with calculated fields, I hope this helps! Sub RemoveCalculatedFields() Dim pt As PivotTable Dim pf As PivotField Dim pfNew As PivotField Dim strSource As String Dim strFormula As String Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.CalculatedFields strSource = pf.SourceName strFormula = pf.Formula pf.Delete Set pfNew = pt.CalculatedFields.Add(strSource, strFormula) Next pf End Sub
	Create List of Pivot Table Formulas   List the Pivot Table Formulas in Excel 2007 Select any cell in the pivot table. On the Ribbon, under the PivotTable Tools tab, click the Options tab. In the Tools group, click Formulas Click  List Formulas. A new sheet is inserted in the workbook, with a list of the calculated fields and a list of the calculated items. List the Pivot Table Formulas in Excel 2003 Select any cell in the pivot table. On the Pivot toolbar, click PivotTable. Click Formulas, then click  List Formulas. A new sheet is inserted in the workbook, with a list of the calculated fields and calculated items (see the Excel 2007 example above).

Pivot Table Tutorials Excel Pivot Table -- Introduction  Excel Pivot Table -- Clear Old Items Excel Pivot Table -- Create a Pivot Table in Excel 2007  Excel Pivot Table -- Custom Calculations  Excel Pivot Table -- Data Field Layout Excel Pivot Table -- Dynamic Data Source Excel Pivot Table -- FAQs Excel Pivot Table -- Field Settings Excel Pivot Table -- Filter Source Data   Excel Pivot Table -- Filters, Top 10  Excel Pivot Table -- GetPivotData Excel Pivot Table -- Grand Totals Excel Pivot Table -- Grouping Data Excel Pivot Table -- Layout, Excel 2007 Excel Pivot Table -- Multiple Consolidation Ranges Excel Pivot Table -- Pivot Cache    Excel Pivot Table -- PivotTable Style Excel Pivot Table -- Printing    Excel Pivot Table -- Protection   Excel Pivot Table -- Report Filters Excel Pivot Table -- Running Totals   Excel Pivot Table -- Show and Hide Items  Excel Pivot Table -- Sorting Excel Pivot Table -- Subtotals  Excel Pivot Table -- Summary Functions Excel Pivot Table -- Unique Items

Pivot Table Books Pivot Tables, Beginning (Excel 2007)  Pivot Tables, Recipe Book (Excel 2003)  Pivot Tables, Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Tables - Add-in -- Pivot Power  Pivot Tables - Add-in - Pivot Play PLUS  Pivot Table Videos Pivot Tables - Clear Old Items Pivot Tables - Copy a Custom PivotTable Style Pivot Tables - Create in Excel 2007 Pivot Tables - Create from Multiple Sheets Pivot Tables - Data Field Layout Pivot Tables - Date Filters, Add Pivot Tables - GetPivotData Pivot Tables - Group Data Pivot Tables - Layout, Excel 2007 Pivot Tables - Report Filters, Add Pivot Tables - Running Totals Pivot Tables - Select Sections Pivot Tables - Subtotals, Create Multiple Pivot Tables - Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2010 All rights reserved.	Debra Dalgleish

Last updated: September 8, 2010