Adding Calculated Field Based on Column Value in Pivot Table

To add a calculated field based on column value in the Pivot Table, go through the steps below:

1. Click anywhere within the Pivot Table.
2. Navigate to the PivotTable Analyze tab.
3. Click on the dropdown of Fields, Items & Sets.
4. Hit the Calculated Field option.
5. Name the field and input a formula with field references.
6. Then, click on Add.
7. Hit OK.

By following these steps, you will able to add the calculated fields based on column value in the Excel Pivot Table.

Step 1: Insert and Create a Pivot Table

In this dataset, we have a sales report of different products with their categories, quantity, and unit prices. In this article, we will learn to add a Calculated Field based on column value in a Pivot Table.
Before doing that, let’s insert and arrange the Pivot Table. So, follow the steps below:

1. Select the Range.
2. Go to Insert tab > PivotTable dropdown> From Table/Range option.
   
3. Choose the location to place the Pivot Table.
4. Click OK.
   
5. Drag the field from PivotTable Fields to the areas.
6. Arrange the Pivot Table with PivotTable Fields.

Here, I have added the category and product name in the Rows area, and then quantity and unit price in the Values area. Additionally, I added the salesman in the Filters area.

Step 2: Add the Calculated Field Based on Column Value

Let’s add a calculated field based on column value in the Excel Pivot Table. Go through the procedure below:

1. Select any cell within the Pivot Table.
2. Go to the PivotTable Analyze tab.
3. Navigate to the Fields, Items & Sets dropdown > Calculated Field option.
   
   It will open the Insert Calculated Field dialog box.
4. Enter a name in the Name box.
5. Select a field from the Fields lists and click on Insert Field.
6. Insert a formula in the Formula box.
7. Click on Add.
8. Then, hit OK.
   
9. Repeat the steps to add more calculated fields.

After hitting OK, you will see an extra column inserting the calculated field in your Pivot Table.

Step 3: Modify the Calculated Field in the Pivot Table

What if you have to make a change in the inserted Calculated Field? So, to modify the calculated field in the Pivot Table, follow the instructions:

1. Click on a cell within the Pivot Table.
2. Go to PivotTable Analyze tab > Fields, Items, & Sets dropdown> Calculated Field option.
3. In the Insert Calculated Field dialog box, click the dropdown of the Name box.
4. Select the name of the inserted calculated field.
   
5. Insert the modified formula in the Formula box.
6. Then, click on Modify.
7. Hit OK.
   

It will modify the inserted calculated field in the Excel Pivot Table. See the screenshot. So, you can see the updated calculated field.

Step 4: See the List of All Calculated Formulas

In this Pivot Table, I have added one more calculated field by following the above steps. Here, I have added the sum of VAT and the sum of Sales in the Excel Pivot Table. Now, follow the steps:

1. Select anywhere in the Pivot Table.
2. Go to PivotTable Analyze tab > Fields, Items & Sets dropdown > List Formulas option.
   

After clicking on List Formulas, you will see a new Excel Sheet. There will be lists of the inserted calculated fields and the formulas.

Conclusion

In conclusion, Excel Pivot Table calculated fields based on column values provide a powerful way to derive new insights and metrics from existing data. By leveraging mathematical operations and expressions, users can create customized calculations that align with their specific analytical needs. In this article, I have provided step-by-step instructions to add a calculated field based on their column value. Then, you will learn to modify the calculated field and also see the list of formulas in the Excel Pivot Table. Explore this article and add the calculated field in the Excel Pivot Table.

Frequently Asked Questions

Can you reference a cell in a Pivot Table calculated field?

No, you cannot directly reference a cell in a Pivot Table calculated field. Calculated fields in a Pivot Table are created using formulas based on the fields within the Pivot Table itself. They do not allow direct references to external cells.

If you need to incorporate data from external cells, consider performing the calculation outside the Pivot Table and then using the result as a source for your Pivot Table. Calculated fields are designed to work with the data within the Pivot Table, not with external references.

How do I use the SUM function to a calculated field in a Pivot Table?

In a Pivot Table, you can’t directly use the SUM function on a calculated field. However, you can achieve the desired result by creating a helper column outside the Pivot Table and then summing that column. Here’s how:

1. In the Pivot Table, go to the PivotTable Analyze tab.
2. Click on Fields, Items & Sets dropdown > Calculated Field option.
3. Enter a name for your calculated field and input the formula based on existing fields in the Pivot Table.
4. Outside the Pivot Table, create a new column adjacent to your original data.
5. Use the same formula you used in the calculated field for each row.

Now, you can use the SUM function to add up the values in your helper column.

Can you use a grand total in a calculated field?

No, you cannot directly use the grand total in a Calculated Field in a Pivot Table in Excel. Calculated Fields operate at the individual row level and cannot reference or include the grand total. They are based on the values in the source data and other calculated fields, but not on the aggregated totals.