Excel field: YOUR OBJECTIVE
You can use the values in a pivot table as the basis for making and displaying calculations of your own. That is, Excel makes it possible for you to create new fields which you will use to display the results of your own calculations in additional rows or columns. For example, you might want to introduce an item (row or column) that shows how a 10% increase in sales per customer would reflect on overall sales. You will be using Excel’s Calculated Fields function to create such new fields and items.
This learning module “Excel field”shows you how to create and use Calculated Fields & Items.
Excel field: HOW DOES IT WORK?
Within Excel’s powerful pivot-table analysis tools, you can introduce Calculated Fields or Items into an existing pivot-table. This enhances considerably your ability to analyze or extract more information from your pivot-table data. Should the default arithmetic functions of the pivot table’s value fields be insufficient for you to visualize information that you consider important, then the use of Calculated Fields becomes quite valuable. More detailed explanations regarding Calculated Fields and Items follow here:
Inserting Calculated Fields
If the data that are normally summarized in your pivot table are insufficiently expressive for your purposes, you can insert one or more Calculated Fields into your pivot table. If you want to visualize what effect a 10% increase in the current customer orders would have on the overall sales results, for example, there is no default function that can visualize that for you. To obtain a view of this kind of information, you would have to use a Calculated Field. Please note that Calcualted Fields always reference data that exists in other pivot-table fields.
To insert a Calculated Field to a pivot table, first select an item in the table’s values area. Then go to the Options tab under PivotTable Tools. There, in the Calculations group, click the button. In the selection list that appears, choose the Calculated Field… command. The Insert Calculated Field dialog box will appear.
In this dialog box’s first entry field, you should type in a name, meaningful to you, for the new Calculated Field that you are about to create (e.g., Sales Increase 10%). Then, in the second (i.e., Formula:) entry box, you can now specify the formula required for your custom calculation. Do do so, first select the item that you want to include in the calculation formula from the Fields: list below. Then click . The selected item will be copied into the Formula: field. Now you can complete the formula, using the arithmetic operators with which you are already familiar (+,-,*, etc.).
When your entries are complete, click . Excel will immediately expand your pivot table in the values area to include your new field. In the current example, a new field named Sum of Sales Increase 10% appears in the pivot table. It’s calculated values are also factored into the subtotals and grand totals, if they are showing. Finally, if you look in the Pivot Table Field List, you will see your newly created field there, as well.
You can, of course, edit or delete the Calculated Field any time you like. To edit it, again open the Insert Calculated Field dialog box. There, in the Name: field, you can select the calculated field that you want to edit or delete. If you’ve defined several such fields, use the small black arrow button in the name field to see all calculated-field names. After selecting the specific calculated-field name, its associated formula appears in the Formula: area and can now be edited. When you are satisfied with the entries, click .
Excel field: Inserting Calculated Items
Calculated Items are additional entries in the pivot table that can be inserted as row or column fields. For example: you would need to use this function if you wanted to insert two additional columns with subtotals for North+Midwest and East+Midwest to a pivot table that already has sales area column labels for the North, Midwest and East sales areas.
Begin by selecting one of the column labels. Then, on the Options tab, in the Calculations group, click the button. This time select the Calculated Item command. Excel opens a dialog box called Insert Calculated Item in “Sales Area”. Now type in a name, meaningful to you, for the new item, much as you did for the Calculated Field function. Then select the field you want to use for your calculation from the Fields: listbox. When you’ve selected the field of interest (in this example, Sales Area), a list with the relevant items appears at the right, in the Items: listbox. Now select the item item you want to use, and then click . Your selection is transferred to the Formula: field above. You can now type in operators and more Items:, if needed, until you have completed the desired formula. Finish by clicking OK. You can create as many Calculated Items as you like.
In our current example, the formula looks like this:
And your Calculated Item will now be inserted into the pivot table as a new Sales Area column:
The new item, the East+Midwest column, has been inserted after the other column labels. This column now calculates the order value for the sum of those two sales areas. (If you had initially selected an item from the row labels, you would use the same method to create a Calculated Item at the end of the rows.)
To edit or delete a Calculated Item, proceed as you did for editing or deleting a Calculated Field.