How to add a custom field in a pivot table

Table of contents:

How to add a custom field in a pivot table
How to add a custom field in a pivot table
Anonim

There are a few times when you need more information than your pivot table is designed to display, but it doesn't make sense to alter your source of information to include this additional information. In these rare cases, adding a customizable calculated field to your pivot table can help. Custom fields can be adjusted to display averages, percentages, variables, or even minimum and maximum values for the field. Here's how to add customizable fields in pivot tables so you can get the information you need with minimal effort.

Steps

Add Custom Field in Pivot Table Step 1
Add Custom Field in Pivot Table Step 1

Step 1. Open the workbook in Excel that contains the source of information and the pivot table with which you will be working

Add Custom Field in Pivot Table Step 2
Add Custom Field in Pivot Table Step 2

Step 2. Select the tab with the worksheet that contains the pivot table and activate it by clicking on it

Add Custom Field in Pivot Table Step 3
Add Custom Field in Pivot Table Step 3

Step 3. Determine the custom field you need, including any other fields you need to reference in order to provide the result you want

Add Custom Field in Pivot Table Step 4
Add Custom Field in Pivot Table Step 4

Step 4. Bring up the PivotTable Tools by clicking inside the pivot table

Add Custom Field in Pivot Table Step 5
Add Custom Field in Pivot Table Step 5

Step 5. Click on the Options tab and choose "Calculated field" from the "Formulas" menu.

Add Custom Field in Pivot Table Step 6
Add Custom Field in Pivot Table Step 6

Step 6. Enter a descriptive column label for your custom field in the floating window that appears

Add Custom Field in Pivot Table Step 7
Add Custom Field in Pivot Table Step 7

Step 7. Create the formula for your custom field in the “Formula

”

  • The fields calculated in the Pivot Tables do not support ranges in the formulas. So instead you have to use the column name in your formula. Click inside the "formula" input window and then choose the field against which you will be calculating from the "Fields" section below.
  • Click "Insert Field" to insert the correct column name into your formula. Complete the formula by adding the calculation.

  • For example, suppose you want to calculate a 6 percent tax on a pivot table that shows 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 # is in" Rows."

  • Name your field as "Taxes" and create the formula "= Sales * 0.06" without the quotes, note the space between the field name and the mathematical operation. Click the "Add" button and click "OK" to close the window.
Add Custom Field in Pivot Table Step 8
Add Custom Field in Pivot Table Step 8

Step 8. Make sure the name of the calculated field appears in the “Values” section of your PivotTable browser

If it doesn't appear, review the steps and try again

Add Custom Field in Pivot Table Step 9
Add Custom Field in Pivot Table Step 9

Step 9. You can rearrange the fields in the "Values" section if you want

Advice

  • Changing the formula in this calculated field is much easier than creating - and then editing - a formula in the feed. This can be useful when the quantities you are calculating your field by change frequently.
  • Remember that calculated fields in a pivot table calculate on combined totals, not on individual rows. You will need to insert a column and a formula in the source of information if you need to calculate individual rows.

Popular by topic