4. Changing Pivot Table Output Values

Overview

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

Summary

  1. Summarize Values By (00:04)

    When we add a numeric field, like Revenue, to a Pivot Table, it assumes we want to create a sum of that revenue. However, there are several other options available. You can access these options by pressing Right Mouse Button Key, M. 

    If we select Count, the Pivot Table shows us the number of customers each salesperson has in each state. Selecting Average shows us the average revenue per customer in each state. Sum, count and average are the most common options you’re likely to us. Max and min can also be useful on occasion, while product is rarely used.

  2. Show Values As (01:27)

    A Pivot Table also provides various different options for displaying its output. You can access the available options by pressing Right Mouse Button Key, A. For example, instead of displaying actual revenue figures, we can display them as a percentage of each state’s revenue. If we do this, we then need to use Format Cells to create a custom number format that omits any values of 0%. 

    There are numerous other options available, including:

    • Parent total options are relevant when a row label or column label contains multiple fields

    • Difference from options let you set a state or sales person as a baseline and compare other states or salespeople to that baseline

    • Running total options let you see the cumulative growth of a field, and are useful when working with dates

    • Rank lets you identify the top and bottom performers in each column or row

Transcript

In the previous lesson, we learned how to format a pivot table and improve its appearance.

In this lesson, we'll learn how to change the output values displayed in our pivot table.

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 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 pressing M for summarize values by.

If we select count, the pivot table now displays the number of customers each sales person has in each state.

It also shows us the number of customers each state has, further emphasizing the importance of California and Texas.

If we press the right mouse button key again, and then press M, but this time change to average, we get the average revenue per customer in each state. If we scroll to the right-hand side, we can also see the average revenue per customer, by salesperson.

Count and average are the two options I tend to use most often in this dropdown 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 switch back to sum of revenues by pressing S.

We'll then press the right mouse button key again, but this time press A, for show values as. Instead of displaying the actual revenue figures, we could display them as a percentage of each state's revenue. So we'll select percent of column total.

To make this more readable, let's remove any 0% values by creating a custom number format.

We'll press the right mouse button and then T, for number format.

In the format cells dialog box, we'll select custom, and then type 0%, semi-colon, 0%, semi-colon. This will show the percentage format with no decimal points for both positive and negative numbers, but show nothing for zero.

We'll then press okay, and see that this gives us a much better view of our data.

If we'd like to switch to the percentage of row totals instead of column totals, then we would go back to our dropdown and select row total.

This now shows how much each individual state contributes to a salesperson's total revenue.

There are many more options available in the show values as dropdown.

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 allow you to set one state or salesperson as a baseline, and measure the difference of each value from the baseline.

The running total options allow you to show the cumulative growth in each row or cell, and are mostly relevant for date fields.

Finally, 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 ones best suit your needs. But I find that the percentage of options are by far the most useful for spotting trends in the data.

Excel Excel for Business Analytics Learning Plan
Data Analysis
Pivot Tables

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial