**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 Field*s 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 .

If you want to delete a calculated field, click the button instead, and then click to conclude the action. The *Calculated Field* will immediately be deleted from the *pivot table*.

**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

**. You can create as many**

*OK**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*.