How To Use the Calculated Field in an Excel Pivot Table in 3 Steps

1. Click the PivotTable. …
2. On the Analyze tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
3. In the Name box, type a name for the field.
4. In the Formula box, enter the formula for the field. …
5. Click Add.

We all have a ton of data to deal with at work, and it can sometimes be overwhelming to make sense of it all. Excel pivot tables are a great tool for analyzing this data and bringing out meaningful insights. But, did you know that you can do even more with pivot tables if you use them in combination with calculated fields? Calculated fields allow you to perform complex calculations directly within the pivot table, and give you the flexibility to quickly and easily perform calculations not available in the standard pivot table features. In this blog post, we will look at the basics of creating calculated fields in Excel pivot tables, and discuss some of the practical applications of this powerful feature.

Excel Pivot Table Calculated Items and Calculated Fields

What is the calculated field in a pivot table?

A pivot table’s calculated fields are functions that use data from other fields to generate results. A calculated field’s results typically appear in the last column of a pivot table and can use multiple fields in its formulas.

Consider, for instance, a pivot table that shows the overall sales for each representative in a business. You could use a calculated field that multiplies the total sales for each employee by 0 if the company gives each employee a bonus equal to 3% of their sales. 03. When the calculated field is complete, a column shows each employee’s bonus.

Why use pivot tables in Excel?

For the analysis of a lot of data, pivot tables are helpful. They create a table with filtering options using data from a spreadsheet. You are able to generate reports and reach conclusions about your data by specifying the kind and amount of information you want to see. The ability of a pivot table to display data from various angles is referred to by the term. Due to their built-in calculations, pivot tables also let you perform more thorough analyses. For instance, you can average the values of two fields or add up various items in a field.

How to use the calculated field in an Excel pivot table

The calculated field in an Excel pivot table can be used as follows:

1. Open the “Insert Calculated Field” dialog box

You can choose any cell in the pivot table by opening your spreadsheet. In the top ribbon, select “Analyze” underneath “PivotTable Tools. Choose “Fields, Items, & Sets” from the “Calculations” group by clicking there. ” Select “Calculated Field. “.

2. Name the calculated field

Click the box next to “Name” in the dialog box for “Insert Calculated Field.” You can change the program’s name to one that more accurately describes the calculation you want to run by deleting the default name. Once you have entered a name, click anywhere outside of this field.

3. Create a formula

Click the box next to “Formula” and erase the “0. “To the right of the integrated equal sign, type the formula you want to use. It should be noted that while formulas may contain the common operational signs and the built-in functions of a program, they are incompatible with formulas containing cell references and array formulas. Select the name of the field from the “Fields” box and click “Insert Field” to include it in your formula. “.

After you type your formula, click “Add” and then “OK. The calculated field is automatically added as the last column in the pivot table. Drag the calculated field label from the list of fields to a location other than “Values” if you want to modify the pivot table so that information is shown from a different angle. “.

Examples of using the calculated field in an Excel pivot table

Here are some instances of how to use an Excel pivot table’s calculated field:

Example 1

The pivot table below shows how a retailer sells greeting cards and controls its inventory. Employees of the business have currently configured the pivot table to show revenue and the total amount they spent on cards for Christmas, Easter, birthdays, and Valentine’s Day. They want to create the following table by using a calculated field to identify the kind of greeting card that makes the most money:

The employees of the retailer create a calculated field called “Profit” with the following values: Type of greeting card**Cost**RevenueChristmas$6,000$7,200Easter$4,000$5,000Birthday$4,500$5,175Valentines Day$5,000$6,500 They choose “Revenue” from the list of fields for the formula and click “Insert Field.” They enter a minus sign, choose the field “Cost” from the list, and then click “Insert Field” once more. In this formula, the cost is subtracted from the revenue to determine the profit for each type of greeting card. Valentine’s Day cards generated the highest profit, while Christmas cards generated the lowest, as shown in the table below:

Type of greeting card**Cost**Revenue**Profit**Christmas$6,000$7,200$1,200Easter$4,000$5,000$1,000Birthday$4,500$5,175$675Valentines Day$5,000$6,500$1,500### Example 2

The pivot table below is used by a manager to monitor the performance of her sales team. A representative is eligible for an end-of-quarter bonus equal to 3% of their total sales if they generate more than $2,000 in sales. End-of-quarter bonuses are not available to representatives with sales of less than $2,000 The manager decides that the following table, which includes a calculated field, will help them determine who is eligible for bonuses and how much each bonus is worth:

The supervisor names the calculated field “Bonus” and chooses to use the following IF statement as the formula:*Representative name**Sales*Pedro Vasquez $2,200 Samuel Roberts $2,500 Louis Allen $3,900 Danielle Henderson $1,900

IF(Sales>$2,000, Sales * .03, 0)

The first argument determines whether the sales representative made more than $2,000 in sales. If this is the case, the program multiplies the sales of the representative by 3% to determine the second argument. The program outputs the third argument “0” if the sales were less than $2,000 to indicate that the representative is not eligible for a bonus. As shown in the table below, Pedro, Samuel, and Louis qualified for a bonus equal to 3% of their sales because their sales exceeded $2,000

Please note that none of the businesses or goods mentioned in this article are associated with Indeed. Representative name**Sales**BonusPedro Vasquez $2,200 $66 Samuel Roberts $2,500 $75 Louis Allen $3,900 $117 Danielle Henderson $1,900 $0

FAQ

Can you perform calculations in a pivot table?

1. Select one of the Order Status items in the pivot table.
2. On the Ribbon’s Options tab, click Calculations.
3. Click Fields, Items & Sets, and then click Calculated Item.
4. Click the drop-down arrow next to the Name box in the Insert Calculated Item dialog box.

How do I add a calculated item to a pivot table?

Calculated items are listed alongside other items in the pivot table’s Row or Column area. Calculated items are NOT shown in the PivotTable Field List.

Is calculated field part of pivot table?

By default, the pivot table will display the sum or count values. Step 3: After adding the field to the pivot table, select any cell and select Analyze > Fields, Items & Sets > Calculated Field by clicking anywhere on the pivot table.

How do I view a calculated field in a pivot table?

By default, the pivot table will display the sum or count values. Step 3: After adding the field to the pivot table, select any cell and select Analyze > Fields, Items & Sets > Calculated Field by clicking anywhere on the pivot table.