How to Add a Custom Field in Pivot Table ? Do you want to expand your pivot table’s content without changing the data from your source? Instead of adding more columns to your worksheet, you may quickly add custom calculated fields to the pivot table to develop formulas there. Custom fields can do almost any calculation you require, including showing averages, percentages, variances, and the field’s maximum value. In this tutorial, we’ll teach you how to add a custom calculated field to a pivot table in Microsoft Excel and how to alter the field whenever you need to change the formula.
First of two methods: Including Custom Fields
1. Start your workbook.
Double-click the file containing the pivot table and source data that you will be using. Remember that calculated fields cannot use references or named ranges in formulas; as a result, your custom field cannot use functions like INDEX, OFFSET, or VLOOKUP. However, any other functions that don’t need references or named ranges, such IF, SUM, COUNT, are acceptable.
2 Select the pivot table that needs editing. The “PivotTable Fields” panel will expand to the right when you click the pivot table. The PivotTable Analyze menu can be found at the top of Excel.
3 The PivotTable Analyze tab can be selected. This tab can be found at the Excel window’s top.
When you click to activate the pivot table, the PivotTable Analyze menu only then becomes available. To restore the menu if it ever goes away, simply click the pivot table once again. In some versions of Excel, this tab is referred to as Analyze.
4 Select Fields, Items & Sets from the menu. This can be seen in Excel’s “Calculations” section of the toolbar.
5. Click Calculated Field in step. The “Insert Calculated Field” dialog box will open once you choose this menu item.
6 Give the field a name. In the “Name” box, enter a detailed column label for your custom field.
7 For your custom field, create a formula. Enter the formula you wish to use to calculate your custom field in the “Formula” box.
For instance, you could call your custom field “Tax” and use the formula =Items * 8.5 percent to determine the tax rate for the “Items” field, which is 8.5 percent. Another illustration: You might use the formula =Sales/Quantity to divide the values in the “Sales” column by the values in the “Quantity” column. Select the field and click Insert Field to include data from a different pivot table field in your formula.
8 Press Add. Your custom field is now included in the list of Fields.
9 Press OK. By doing so, the “Insert Calculated Field” box is closed, and the pivot table is displayed once more.
10 In the “Values” section, locate your new field. You can now see your newly added field in the “Values” column of the “PivotTable Fields” panel on the right side of your sheet.
11 Drag the new field to the appropriate location. To apply the formula to your data, drag your custom calculated field from the Values area to Filters, Rows, or Columns.
2nd approach: Editing custom fields
1 Choose the pivot table that includes the field you wish to change.
It is considerably simpler to edit the formula in this single calculated field than it is to create and then edit a formula in the source data. When the amount by which you are calculating your field varies frequently, this can be helpful. The PivotTable Analyze or Analyze option at the top of Excel will be activated when you click the pivot table.
2 Select the Analyze or PivotTable Analyze tab. These can be found at the Excel window’s top.
3 Choose Fields, Items & Sets from the menu. This can be seen in Excel’s “Calculations” section of the toolbar.
4. Click Calculated Field. The “Insert Calculated Field” dialog box will open once you choose this menu item.
5 Choose the field that needs editing. The “Fields” section contains all fields, including your custom fields. When you click a custom field, its name and formula will appear in the “Name” and “Formula” boxes, respectively.
6 Change the formula or field name. Your custom field can now be modified as necessary. Simply click Delete if you wish to remove the field.
7 Press OK. Your field has been modified. To view your updated calculations, right-click your pivot table and choose Refresh if it doesn’t update automatically.