How to Add a Custom Field in Pivot Table
Open the workbook in Excel containing the source data and pivot table you'll be working with., Select the worksheet tab that contains the pivot table and make it active by clicking on it. , Determine the custom field that you need, including any...
Step-by-Step Guide
-
Step 1: Open the workbook in Excel containing the source data and pivot table you'll be working with.
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. , If it does not, review the steps and try again. , -
Step 2: Select the worksheet tab that contains the pivot table and make it active by clicking on it.
-
Step 3: Determine the custom field that you need
-
Step 4: including any other fields it may need to reference in order to provide the desired result.
-
Step 5: Force the Pivot Table Tools menu to appear by clicking inside the pivot table.
-
Step 6: Click the Options tab and then choose "Calculated Field" from the "Formulas" menu.
-
Step 7: Enter a descriptive column label for your custom field in the pop-up window.
-
Step 8: Create the formula for your custom field in the "Formula" text entry window.
-
Step 9: Ensure that your calculated field name now appears in the "Values" section of your Pivot Table wizard.
-
Step 10: You may reorder the fields in the "Values" section if you like.
Detailed Guide
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. , If it does not, review the steps and try again. ,
About the Author
Catherine Roberts
Brings years of experience writing about pet care and related subjects.
Rate This Guide
How helpful was this guide? Click to rate: