In our last session, you learned how to construct a pivot table, transforming a flat dataset into a structured summary. You saw how this process mirrors the GROUP BY logic from your SQL experience and the drag-and-drop interface of tools like Tableau. We briefly touched on changing the default calculation, but today we'll dive much deeper into this crucial feature.
This lesson focuses on customizing the values within your pivot table. Instead of just accepting the default SUM or COUNT, you will learn how to select the right calculation to answer a wide variety of business questions. This is a fundamental skill that transforms a pivot table from a simple summary tool into a powerful analytical instrument. By the end of this session, you'll be able to manipulate pivot table values to show averages, find maximums, display percentages, and rank items—all essential tasks for a data analyst.
Beyond the Default: Summarize Values By
When you drag a field into the Values area of a pivot table, Excel makes a default choice for you: SUM for numeric data and COUNT for text or mixed data. But often, the most interesting insights come from looking at the data through a different lens. For example, knowing the total revenue (SUM) is useful, but understanding the average sale price (AVERAGE) or the number of transactions (COUNT) can provide deeper context.
You can change this default calculation through the Value Field Settings. There are two primary ways to access this:
- Right-click on any value in your pivot table and choose Summarize Values By. This gives you a quick-access menu for the most common functions.
- Click the dropdown on the field in the Values area of the PivotTable Fields pane and select Value Field Settings.... This opens a dialog box with more comprehensive options.
The following short video demonstrates how quickly you can switch between different summary functions like SUM, AVERAGE, and MAX.
Pivot Table Excel | Step-by-Step Tutorial
Watch this brief clip from Kevin Stratvert's tutorial.
Notice how a simple right-click changes the entire meaning of the value column, from total revenue to the single largest order. Pay attention to the segment from summarizing values.
The Core Summary Functions
To get a solid grasp of the most common summary functions, we'll turn to a detailed guide. The following reading from Contextures, a well-regarded Excel resource site, provides clear explanations for each function. As you read, focus on understanding the specific purpose of each one.
Excel Pivot Table Summary Functions Sum Count Change
This article provides an excellent, practical overview of the 11 summary functions available in a pivot table. It uses clear examples to show what each function does and how it behaves.
Please read the following sections: Start with the Introduction and the instructions on how to change the function. Then, review the explanations for the most common functions: Sum, Count, Average, Max, and Min. Pay special attention to the section on the difference between Count and Count Numbers. This is a critical distinction that often trips people up. Understanding this will help you ensure your counts are always accurate.
As you saw in the reading, the distinction between Count and Count Numbers is vital.
- Count (
COUNTA): Counts everything—numbers, text, errors. It only ignores empty cells. This is useful for counting the number of records or transactions, as long as the column you're counting has no blanks. - Count Numbers (
COUNT): Counts only numeric values. This is useful if you have a column with mixed data types and you only want to count the cells that contain actual numbers.
Adding Context: Show Values As
Changing the summary function is only half the story. The real analytical power comes from the Show Values As tab in the Value Field Settings. This feature lets you move beyond simple aggregations and perform calculations that show values in relation to others, such as percentages, ranks, or running totals. This is how you start to tell a story with your data.
For example, instead of just showing that the "Electronics" category had $500,000 in sales, you can show that it accounts for 25% of the grand total. This immediately communicates its relative importance.
The next video provides a quick demonstration of changing the calculation to Average and then using Show Values As to display percentages.
Master Pivot Tables in 10 Minutes (Using Real Examples)
Watch this segment from Kenji Explains.
Focus on the part from proportions and averages. He first shows how to calculate the percentage of total and then switches gears to find the average price per product, which involves changing the summary function from Sum to Average.
Exploring Advanced Calculation Options
Now that you've seen a preview, let's explore the powerful options under Show Values As in more detail. These calculations are essential for many common analytical tasks.
Customizing a pivot table | Microsoft Press Store
This article from the Microsoft Press Store offers a fantastic deep dive into the 'Show Values As' tab. It methodically walks through several of the most useful options with clear examples.
Please read the following sections to understand these key calculations: Start by reviewing the Table 3-1 which summarizes the options. This gives you a great overview. Read "Showing percentage of total" to understand the three percentage options: % of Grand Total, % of Column Total, and % of Row Total. Think about when you would use each one. Read "Showing rank" to see how you can automatically rank your data. This is perfect for "Top 10" lists. Read "Tracking running total..." to learn about the Running Total In and % Running Total In options, which are great for seeing cumulative progress. Finally, read "Tracking the percentage of a parent item" to learn about a very useful hierarchical calculation, % of Parent Row Total. This helps you understand contributions within a specific category, not just to the overall total.
By mastering these options, you can answer much more sophisticated business questions directly within your pivot table, without writing a single formula:
- % of Grand Total: "What percentage of our total annual revenue comes from the North region?"
- % of Column Total: "Within the North region, what is the sales mix of our different product categories?"
- Rank Largest to Smallest: "Who are our top 5 salespeople by revenue?"
- Running Total In: "What was the cumulative revenue month-over-month for the last fiscal year?"
- % of Parent Row Total: "If 'Beverages' is a parent category and 'Coffee' and 'Tea' are sub-categories, what percentage of Beverage sales comes from Coffee?"
Conclusion
In this lesson, you've moved beyond the default settings of a pivot table to take full control of its calculations. You now have a powerful toolkit for summarizing and analyzing data in multiple ways, a core competency for any data analyst.
Here are the key takeaways:
- Pivot table values can be customized using two main methods: Summarize Values By and Show Values As.
Summarize Values Bychanges the fundamental aggregation function (e.g.,SUM,AVERAGE,COUNT,MAX). It's crucial to pick the one that matches your analytical question.- Be mindful of the difference between
Count(which counts non-blank cells) andCount Numbers(which counts only numbers). Show Values Asprovides context by calculating values relative to others in the pivot table (e.g.,% of Grand Total,Rank,Running Total In). This is where you can derive powerful comparative insights.
You are now equipped to create pivot tables that don't just present data, but also interpret it. In our next lesson, we will complete our tour of pivot table fundamentals by learning how to filter, sort, and group data. This will allow you to precisely refine your reports and zoom in on the specific information needed to answer targeted business questions.