How To Use Excel Formula To Add Calculated Field In PivotTable

In this article, I will tell you how to use excel formulas in a PivotTable to perform calculations and add custom fields to the PivotTable. This is very useful in some use cases.

1. Excel Formulas That Can Be Used In PivotTable.

1. There are several formulas that can be used in Excel PivotTables to perform calculations and add custom fields. Here are a few examples.
2. SUM: Calculates the sum of a set of values. This formula is commonly used to calculate totals in PivotTables.

   Syntax: =SUM(range)
   Example: =SUM(Sales)

3. AVERAGE: Calculates the average of a set of values. This formula is useful for calculating average values in PivotTables.

   Syntax: =AVERAGE(range)
   Example: =AVERAGE(Sales)

4. COUNT: Counts the number of cells that contain numeric values in a range. This formula is helpful for counting values in PivotTables.

   Syntax: =COUNT(range)
   Example: =COUNT(Sales)

5. IF: Returns one value if a condition is true and another value if it is false. This formula is used to create custom calculations in PivotTables.

   Syntax: =IF(condition, value_if_true, value_if_false)
   Example: =IF(Sales > 100, "High", "Low")

6. These formulas can be used in combination with other functions to create powerful calculations and custom fields in PivotTables.

2. How To Use Excel Formula To Add Calculated Field In PivotTable.

2.1 Use Excel Formula To Add Calculated Field In PivotTable Steps.

1. To use Excel formulas in a PivotTable, you need to create a calculated field.
2. Here are the steps to create a calculated field in a PivotTable.
3. Select the PivotTable.
4. Click the “PivotTable Analyze” tab in the Excel ribbon.
5. This tab will be shown only when you select the PivotTable.
6. Click the “Fields, Items & Sets” item in the Calculations group and select “Calculated Field” from the drop-down list.
7. In the “Name” field, enter a name for the calculated field.
8. In the “Formula” field, enter the formula you want to use for the calculated field. You can use any Excel formula here, such as SUM, AVERAGE, IF, etc.
9. Click on “Add” to add the calculated field to the PivotTable.
10. The new calculated field will appear in the “Values” area of the PivotTable pane. You can drag it to other areas of the PivotTable as needed.

2.2 Use Excel Formula In PivotTable Example.

2.2.1 Example DataSet.

1. Here is an example dataset that you can use to create a PivotTable with a calculated field.
   

   	A	B	C	D
   1	Product Category	Region	Sales	Quantity
   2	Widgets	East	$10	5.00
   3	Widgets	West	$15	10.00
   4	Gadgets	East	$20	8.00
   5	Gadgets	West	$12	6.00

2. This dataset includes sales information for two product categories (Widgets and Gadgets) in two regions (East and West).
3. The Sales column represents the total sales for each product category in each region, and the Quantity column represents the total quantity of each product category sold in each region.

2.2.2 Add A Calculated Field To The PivotTable.

1. In this example, we will use the above dataset to create a PivotTable that shows total sales by region and product category.
2. And then add a calculated field to calculate the average selling price for each product category.
3. Before starting, make sure the Sales column cells value data type is Currency and the Quantity column cell value data type is Number.
4. And make sure there is no white space in the cell value’s beginning and end.
5. Now, create a PivotTable from the above data set, you can refer to the article How To Create, Remove Excel PivotTable Examples to learn how to do it.
   
6. Because we want to calculate the average selling price for each product category, so we can create a calculated field with the following formula: =SUM(Sales)/SUM(Quantity). This formula divides the total sales by the total quantity to calculate the average selling price.
7. Select the PivotTable and click the “PivotTable Analyze” tab in the Excel ribbon.
8. Click the “Fields, Items & Sets” item in the Calculations group and select “Calculated Field” from the drop-down list.
9. Input the field name Unit_Price in the Name text box, and input the formula =SUM(Sales)/SUM(Quantity) in the Formula input text box like below.
   
10. Click the Add button, then it will add the newly created calculated field ( Unit_Price ) in the Fields list.
    
11. After you click the OK button, you can see the calculated field is added in the excel PivotTable pane like below.
12. You can drag it to the Values column to show the calculated field values in the PivotTable as you need.
    

References

1. How To Modify, Delete Calculated Field In Excel PivotTable.