On some occasions, you need to enter more information than a normal pivot table can show. In these rare cases, it might be helpful to add a custom and calculated field to the pivot table. You can configure these fields to show averages, percentages, or the maximum and minimum values of a field. Read on for these instructions to find out how to add a custom field in a PivotTable so you can enter information easily.
Steps
Step 1. Open the worksheet that contains the data and the pivot table you are working with
Step 2. Select the worksheet tab that contains the pivot table and activate it by clicking on it
Step 3. Determine the custom field you need, including references to any other fields you need to produce the desired result
Step 4. Open the Pivot Table Tools by clicking inside the pivot table
Step 5. Click on the Options tab and choose “Calculated Field” in the “Formulas” menu
Step 6. Enter a descriptive label for the custom field column in the pop-up window
Step 7. Create the formula for the custom field in the “Formula” window
- PivotTable calculated fields do not support ranges in formulas, rather a column name should be used in the formula. Click in the "Formula" field and choose the field with the reference value to be calculated from the "Fields" section below.
- Click on "Insert Field" to insert the correct column name in the formula. Complete the formula by adding the calculation.
-
For example, let's assume you want to deduct 6% tax from the sales displayed in the pivot table by region and product. The "Region" column is found in "Columns", the "Sum of Sales" is found in the "Values" section and the product name is found in "Rows".
- Name it the "Taxes" field and create the formula "= Sales * 0.06" - without the quotes. Note the space between the field name and the mathematical operator. Click on the "Add" button and then on "OK" to close the window.
Step 8. Make sure the calculated field name now appears in the “Values” section of the PivotTable wizard
Otherwise, re-read the steps and try again
Step 9. If you want, you can reorder the fields in the “Values” section
Advice
- Changing the formula of one of the calculated fields is much easier than creating - and later modifying - a formula from source data. This can be useful when the values to be calculated change frequently.
- Remember that pivot table calculated fields are calculated from total sums, not individual rows. If you need to calculate for individual rows you will need to enter a column and formula in the source data.
