4. Changing Pivot Table Output Values
The default SUM output format can be changed to many different alternatives in a Pivot Table. Learn how to apply these alternatives in this lesson
Summarize values by:
- Sum is the default summarized value, and is the most useful
- Count and Average may also be helpful
- Other alternatives such as Min and Product are rarely of use
Show values as:
- Displaying values as a percentage of the total are the most useful options in this menu
- Often you'll have to change the number format to remove 0% values
Useful keyboard shortcuts
ALT + J, T, D: Summarize values by:
ALT + J, T, A: Show values as:
RMBK, T: Access Format Cells menu
CTRL + →: Move to end of data region
HOME: Move to column A
ALT + W, F, F: Freeze panes
When you add a field to the Values area, in our case Revenue, the pivot table automatically assumes that you want to sum all the revenues so that you can find the total revenue generated by Palacios in California or by Simonson in Illinois.
Excel actually gives you many other options to place in your pivot table which you can access by pressing the right mouse button key, and then M for Summarized Values By.
And if I now select Count, the pivot table now displays the number of customers each sales person has in each State.
It also shows you the number of customers each State has, further emphasizing the importance of California and Texas.
If I press the right mouse button key again and then M, but this time change to Average, we get the average revenue per customer in each State.
And the average revenue per customer by sales person, if I scroll to the right-hand side.
Count and Average are the two options I tend to use the most in this drop down list.
Max and Min may be of use on occasion, but product almost never works, because multiplying revenues together doesn’t really make a lot of sense.
Not only can you summarize values by different output options, you can also show these options in different ways.
Let’s first switch back to Sum of Revenues by pressing S.
Then I'll press the right mouse button key again, but this time I'll press A for Show Values As.
Instead of displaying the actual revenue figures, I could display them as a percentage of each State’s revenue.
So I’ll select % of Column Total.
To make this more readable, let’s remove any 0% value by creating a custom number format.
We’ll press the right mouse button and then T for number format.
And in the format cells dialogue box I’m going to select Custom, and then I’ll type 0% semicolon 0% semicolon.
This will show the Percentage format with no decimal points for positive numbers, and the same for negative numbers, which show nothing for zero.
I’ll then press OK, and as you can see, this gives us a much better viewing of our data.
If we’d like to switch to the % of Row Totals instead of Column Totals, then we go back to our drop down and select Row Total.
This now shows how much each individual State contributes to a sales person’s total revenue.
There are many more options available in the Show Values As drop down.
Parent total options, which you see here, are relevant when we have more than one field in a column or row label.
We’ll see these in action in a later lesson.
The Difference From options allows you to set one State or sales person as a base line and measure the difference of each value from that base line.
The Running Total options allow you to show the cumulative growth in each row or cell, and are mostly relevant for date fields.
And lastly, Rank is used to identify the top and bottom performers in each column or row.
It's worth exploring all of these options to see which best suits your needs.
But I find that the % of options are by far the most useful of these for spotting trends in the data.