Multiple calculations in a field of a PivotTable:

In this tip, I explain how various operations can be performed on a “field” in a PivotTable.
Click here to download the file.

What I have already created is a new Pivot table with the “Province” field (1) as a Row label with the “Total” (2) as a “Value”.

What I want to get is the three value fields.
One gives me the sum of the total.
The next one gives me the minimum amount from the total.
The last one gives me the maximum amount from the total.

For this, we drag and drop the “Sum of the amount” in the “values” (1) field thrice.
This gives us three columns, each with the sum of the total (2).

Then, we go to the title of each of these columns, change the calculation and adjust the number format.
We begin with the first column.
Right-click a cell in the first column and select “Value Field Settings” in the drop-down menu.
In the window that appears, give the name that you wish to use in this column. “Total” with a space seems quite appropriate.

Why is this space necessary? Well, you have learned the reason in a previous tip.
But for those who would’ve forgotten, here’s a reminder.
When a field name appears in the field list, we can not use this name as the title for a column.  So we should deceive Excel and type a space after the name.

We will continue.
The calculation is already set as “Sum”, so we need not change anything for this column.
Then click the “Number Format” button.

In the “Format Cells” dialog box, we select the “Currency” category and I set the decimal places to zero.
I do not need decimals for this example. You can do whatever you want, of course.
Click the OK button in the “Cell properties window” to close it.
Then click the OK button in the “Value field settings ” window to close it.

Our first column is now displayed in the way we wanted it to be.
The format of the column heading, “Total”, is “Currency” and the operation that is carried out is the sum of the totals.
Everything is fine.
We will now continue with the second column.

Actually, this is like doing the same thing thrice, just that, the operation that we select for each column is of a different type.

We right-click a cell in the second column and select “Value Field Settings” in the drop-down menu.
In the “Value Field Settings” dialog box, I give it the name “Min”, and this need not be followed by a space because this name does not appear in the field list.
I select the “Min” operation again. This will give me the minimum value to be displayed.
I then click the “Number Format” button again.

In the “Number format” dialog box, everything remains the same as was the case for the previous column.
So the “Currency” category is selected and the number of decimal places is zero.
Click the OK button in the “Cell properties window” to close it.
Then click the OK button in the “Value field settings ” window to close it.

The second column now shows us the minimum values from this data.

Finally, we have the third column.
Here, again, its the same process.
We right-click a cell in the third column and select “Value Field Settings” in the drop-down menu.
In the “Value Field Settings” window, I give it the name “Max”, without following it by a space because, this name does not appear in the field list.
I now select the “Max”operation. The Max operation will give me the maximum value.
I then click the “Number Format” button again.

In the “Number format” dialog box, everything remains the same as was the case for the previous column.
So the “Currency” category and set the number of decimal places to zero.
Click the OK button in the “Cell properties window” to close it.
I then click the OK button in the “Value field settings ” window to close it.

All three columns give us different values.
The first one gives me the total, the second one gives the minimum value and the third, the maximum value.

FYI: Swotster.com thinks of its students, so you also remember swotster.com

Copyright © 2013 – Swotster Ltd.

Featured Courses

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