Pivot Table Calculated Field for Average in Excel

Get FREE Advanced Excel Exercises with Solutions!

Calculated fields and calculated items are two confusing aspects of pivot tables. Due to complexity, many pivot table users avoid these two features. But these features are really useful, and they aren’t complicated if you understand well how they work. So, in this article, we shall show how you can create or add a pivot table calculated field for showing the average of two existing data fields.

We have used a sample file and our examples are based on this sample file. The following figure shows part of a table in that file.

Dataset to create a calculated average field in excel pivot table

The table shown in the above figure consists of five columns and 48 rows. Each row of the table contains the monthly sales amount for a particular sales representative. For example, Bob is a sales representative for the North region, he sold 267 units in the month of February and his total sales were $23,882.


What Is Calculated Field in Excel Pivot Table?

A pivot table is a special type of range. You can’t insert new rows or columns within the pivot table. In summary, we can say that you can’t insert formulas to perform calculations with the data in a pivot table. However, you can create a calculated field in a pivot table. A calculated field consists of a calculation that involves other fields.

With the help of a calculated field (added in a pivot table), you can display new information. A calculated field is an alternative to creating a new column field in your source data. In most cases, you will find that it is easier to create a new column field in your source data with a formula that performs the expected calculation. But when your data is from an external source and you can’t manipulate the data source, you can use the calculated field feature.


Pivot Table Calculated Field for Average in Excel: with Easy Steps

The following figure shows a pivot table created from the table. To create this pivot table, we have placed the Month field in the Rows area, the SalesRep field in the Columns area, and the Sales field in the Values area.

PivotTable created from the dataset

See our above figure and observe the pivot table. The boss of the company may want to know the average unit price sold by Bob in the month of February. To find out the average unit price, divide the Sales field by the Units Sold field. We are going to add a new field to the pivot table that will show the average unit price.

We have used the following procedure to create a calculated field that shows the average unit price data:


Step 1: Select Pivot Table

  • First, select any cell within the pivot table to access the PivotTable Analyze feature.

Step 2: Click on Calculated Field Command

  • Then, choose PivotTable Analyze ➪ Calculations ➪ Fields, Items & Sets ➪ Calculated Field. The Insert Calculated Field dialog box will appear.

Click on Calculated Field Command


Step 3: Enter Formula for Calculated Average Field

There are two fields in the Insert Calculated Field dialog box. One is Name Box and another is Formula Box.

  • Next, enter a descriptive name in the Name Box and input the formula in the Formula Box. The formula can use any worksheet function and use any fields from the data source. In our example, we have entered Average Unit Price in the Name box, and this formula in the Formula box: =Sales/’Units Sold’.

How to create a calculated field in an Excel pivot table


Step 4: Add Calculated Field

  • After that, click Add button in the dialog box to add this new field in the Fields section.

Calculated field for Average


Step 5: Observe Results

  • Finally, click OK to close the Insert Calculated Field dialog box.

Click OK to close the dialog box

Note: You can enter the formula manually by typing it in the formula box or by double-clicking items in the Fields list box. When you double-click an item, the item will be transferred to the formula box. Because the Units Sold field contains a space, Excel adds single quotes around this field name when it is entered in the formula box.
  • We are almost done. After you have created the calculated field (Average Unit Price), Excel automatically adds this field as a new column in the pivot table with its calculated values. The calculated field also appears in the PivotTable Fields task pane. You can treat this new field just like any other field, but you can’t move it to the Rows, Columns, or Filters areas like other fields. This field must remain in the Values area.

PivotTable Task Pane

  • The following figure shows the pivot table after we have added the calculated field Average Unit Price. Some labels have been shortened to extend readability. To shorten a label, select the label, press F2, and change the label. I have applied some styling for better visualization.
Calculated average field added to pivot table

The pivot table with a calculated field.

Tip: The formulas that you develop can also use worksheet functions, but the functions can’t refer to cells or named ranges.

Read More: How to Calculate Weighted Average in Excel Pivot Table


Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to add the calculated field average in an Excel pivot table. Do you have any more queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.

Happy Excelling 🙂


Related Articles


<< Go Back to Calculated Field in Pivot Table | Pivot Table Calculations | Pivot Table in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo