How To Add A Calculated Field To A Pivot Table

Excel supplies an opportunity for computing values inside a pivot table. You tin can add calculated fields and items to a table. Adding a calculated field enables you to insert a new row or cavalcade into a pin table and then fill up the new row or column with a formula.

For example, you come across that it reports on sales both by product and calendar month. What if you want to add the commissions expense that you incurred on these sales?

Suppose for the sake of illustration that your network of independent sales representatives earns a 25 percent commission on coffee sales. This committee expense doesn't announced in the data list, so you lot can't retrieve the information from that source. Withal, considering y'all know how to calculate the commissions expense, yous can easily add the commissions expense to the pin tabular array past using a calculated field.

To add a calculated field to a pivot tabular array, take the post-obit steps:

Identify the pin table past clicking whatsoever cell in that pivot table.
Tell Excel that you desire to add a calculated field.

Click the Clarify ribbon's Fields, Items & Sets command, and so choose Calculated Field from the Formulas menu. Excel displays the Insert Calculated Field dialog box.

In Excel 2007and Excel 2010, you cull the PivotTable Tools Choice tab'south Formulas command and so choose Calculated Field from the Formulas menu.
In the Proper name text box, name the new row or column that you lot want to show the calculated field.

For example, if you want to add a row that shows commissions expense, you might proper noun the new field Commissions.
Write the formula in the Formula text box.

Calculated field formulas work the same way as formulas for regular cells:
1. Begin the formula past typing the equal (=) sign.
2. Enter the operator and operands that make up the formula.
  
  If you desire to summate commissions and commissions equal 25 percent of sales, enter =.25*.
3. The Fields box lists all the possible fields that can be included in your formula. To include a choice from the Fields listing, click the Sales $ item in the Fields list box and and so click the Insert Field button.
See how the Insert Calculated Field dialog box looks afterward you create a calculated field to show a 25 percent commissions expense.
Click OK.

Excel adds the calculated field to your pivot table.

Later on you insert a calculated field, Excel adds the calculated field to the PivotTable field list. Yous can then pretty much work with the calculated item in the same way that you work with traditional items.

How To Add A Calculated Field To A Pivot Table

About This Commodity

This commodity tin can be institute in the category: