Add a Custom Field in Pivot Table

There are a few occasions where you need more information than your pivot table is designed to show, but it doesn't make sense to alter your source data to include this additional information. In these rare instances, it can be helpful to add a custom, calculated field to your pivot table. Custom fields can be set to display averages, percentages of a whole, variances or even a minimum or maximum value for the field. Keep reading for instructions on adding custom fields in pivot tables so you can get the information you need with minimal effort.

Steps

1. Open the workbook in Excel containing the source data and pivot table you'll be working with.
2. Select the worksheet tab that contains the pivot table and make it active by clicking on it.
3. Determine the custom field that you need, including any other fields it may need to reference in order to provide the desired result.
4. Force the Pivot Table Tools menu to appear by clicking inside the pivot table.
5. Click the Options tab and then choose "Calculated Field" from the "Formulas" menu.
6. Enter a descriptive column label for your custom field in the pop-up window.
7. Create the formula for your custom field in the "Formula" text entry window.
   • Pivot Table calculated fields do not support ranges in formulas. Therefore, you must use the column name in your formula instead. Click inside the "Formula" text entry window and then choose the field you will be calculating against from the "Fields" selection below.
   • Click "Insert Field" to insert the correct column name into your formula. Complete the formula by adding the calculation.
   • For instance, assume you want to calculate 6 percent tax on the sales in a pivot table that displays sales by region and product. The "Region" column label is in "Columns," the "Sum of Sales" label is in the "Values" section and the "Product" label is in "Rows."
   • Name your field "Tax" and create the formula "=Sales *0.06" without the quotation marks, noting the space between the field name and the mathematical operator. Click the "Add" button and then click "OK" to close the window.
8. Ensure that your calculated field name now appears in the "Values" section of your Pivot Table wizard.
   • If it does not, review the steps and try again.
9. You may reorder the fields in the "Values" section if you like.

Tips

• Changing the formula in this one calculated field is much easier than creating--and later editing--a formula in the source data. This can be useful when the amount by which you are calculating your field changes frequently.
• Remember that the calculated fields in a pivot table calculate against the combined totals, not against individual rows. You will need to insert a column and formula in the source data if you need a calculation by individual rows.

Related Articles

• Add a Field to a Pivot Table
• Add Rows to a Pivot Table
• Create Pivot Tables in Excel
• Change an Excel Pivot Table Source
• Add a Column in a Pivot Table
• Create a Currency Converter With Microsoft Excel
• Create a Mortgage Calculator With Microsoft Excel

Sources and Citations

• http://www.ozgrid.com/Excel/pivot-calculated-fields.htm
• http://office.microsoft.com/en-us/excel-help/calculate-values-in-a-pivottable-report-HP010096323.aspx#BM1c

See also

• Use Google Spreadsheets
• Read and Interpret a Spread Sheet
• Edit the Contents of a Spreadsheet Cell
• Create an XL Worksheet for Continued Fractions
• Create a Graph Using a Spreadsheet
• Add a Column in a Pivot Table
• Create a Spirallic Numbers Dataset
• Do Cost Volume Profit Analysis
• Bulldog Through an Intellectually Simple Problem
• Use the Newton Raphson Method of Quickly Finding Roots

Read more ...

What links here

• Add Rows to a Pivot Table
• Add a Column in a Pivot Table
• Give Notice at Work