Insert “Calculated Field”

When creating a “Pivot Table”, we can add a “Calculated Field” to expand our analysis capabilities. A “Calculated Field” is a new field and is comprised of other fields, based on a “Formula”. A “Calculated field” must be placed in the value field.
First we select a cell in the value field. We choose the “Options” tab in the “Ribbon”. Click the “Calculations” button, choose “Fields items and sets” and click “Calculated Field”.

This opens the “Insert Calculated Field” dialog. In the upper section we give an appropriate name. I give ‘TotAK’, which stands for “Total purchase”. In the “Formula box”, we enter our “Formula”. We can type this or we can create it by double-clicking on the fields.
Example: I double-click the ‘priceAK’ field, type the multiplication sign and double-click the number field.

This would give me the total for the purchase of my property.
I click the “Add” button, and click OK.

And Voila!, the calculated field is added to the “Pivot Table”. You will now find this field in the “Values” area under the “Pivot Table Field List”.

 

 

Adding a calculated item

In Excel, we can also add a calculated item. A calculated item uses the data from a different field.
For example, the field “month” contains the different months of the year, January to December. I wish to add a calculated item named Q1 which gives the totals for the months January, February and March or the first quarter.
The first thing I do is select a cell in the column “Month”. We choose the “Options” tab in the ribbon. Next we click the button “Calculations”, Choose “Fields items and sets” and click “Calculated Item.”

This opens the dialog “Insert Calculated Item.” In the upper section I give an appropriate name. In the left part I choose the field for which the calculated item is created, example “Month”. In the right part I double-click the month of ‘January’, I type a plus sign, I double-click ‘February’, I type a plus sign and double-click ‘March’. Those are the months of the first quarter.
I click the “Add” button, and click OK.

I repeat this for Q2, Q3 and Q4.

 

View “Formulas”

To view the “Formulas” that were created by “Calculated fields” or “Calculated items,” you click the “Calculations” in the “Options” tab of the “Pivot Table Tools”.  Next, you choose “Fields, items and sets” and you click “Formulas” from the drop-down menu. This will open a new “Worksheet” in which our “Formulas” that are used in our “Pivot Table” are shown

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats