Pivot Table Basics - Calculated Fields

Users often need to perform data based calculations that are not in the original data set. This can be done in pivot table through the "Calculated Field". A calculated field is a virtual data field created by executing a calculation against existing fields in the pivot table, just like a new virtual column added to the data set.

Next, we will add a calculated field to the following pivot table:

Create a Calculated Field

Steps to create a calculated field:

(i) Select "Analyze" under the Pivot Table Tools
(ii) Select "Fields, Items & Sets"
(iii) From the drop-down menu select "Calculated Field".

(iv) The "Insert Calculated Field" dialog box will pop up

There are two input boxes inside the dialog screen. At the top is the "Name" field, which user can name the calculated field with a name of their choice, better appropriately to meet its function.

The one below is the "Formula" box, user can build their own formula by selecting the combination of data fields from the underneath "Fields" list box.

In this example, we create a calculated field to calculate the "Profit" for the property transactions:

We first select and double click the "Selling price" field, then enter the mathematical operator "- ", follow by "Purchase price"; then repeat the process again for each of the cost items to finalize the following formula into the formula box:

"Selling Price"-"Purchase Price"-"Stamp Duty"-"Conveyance"-"Improvement Cost"

Finally, click "O.K"

Then we can see the field under the name of "Sum of Profit" been added to Pivot Table:

Once the new calculated field was created, we can also find it in the "Fields" list box:

Pivot Table - Data Model

Data Model is an in-memory analytics tool introduced by Microsoft since Excel 2013, which can be used to work with disparate data.

Up to this point, we have been working with source data from one table. In reality, users will most likely encounter data from disparate data source. Start from the basic again, in the following example we are working with data source from two tables within the same worksheet:

The table on the left is a Sales summary, the table on the right is a Salesperson table, we want to group them together to find out the performance of the salespersons.

First, we need to tell Excel their relationship, and then pull them into the internal Data Model.

Step 1: Click any cell of the Sales Summary table.

Step 2: Go to ribbon, select "Insert"

Step 3: Select "Pivot Table"

Step 4: "Create Pivot Table" dialog box will pop up

Step 5: Select and check the range, select "New Worksheet"

Step 6: Be sure to check next to the "Add this data to the Data Model" => OK

Step 7: Click any cell inside the Salesperson table.

Step 8: Repeat the procedure for the Salesperson     table.

Step 9: On the "Create Pivot Table" dialog box, also check the box next to the "Add this data to the Data Model"

Underneath the "Pivot Table Fields", we can either select "Active" or "All" , we select "All".

"Range" and "Range 1" is the two tables of our source data.

We can click the arrowhead at the left of the table name and select fields from the respective table to incorporate into our new Pivot Table.

We select "Salesperson" to Rows and "Amounts" to Values.

Excel recognises that we are using two tables from our Data Model, and prompts us to create a relationship between them.

To set up the relationship, we click "CREATE"

In the "Create Relationship" dialog box, we link up the "Customers" from Sales summary table to the "Cust." in the Salesperson table.

Following is the result: