Create Multiple Subtotals in Excel Pivot Tables

With Pivot Table Subtotals, we become accustomed to getting the sum, as this is the default function used. Did you know that you can create multiple subtotals and use different functions? Excel allows you to apply multiple subtotal functions—such as Average, Max, Min, and even statistical functions like StdDev and Var—to give you deeper insights into your data. This flexibility makes Pivot Tables one of the most powerful tools for data analysis in Excel.

n this article, we’ll dive deep into how to use multiple subtotals in Pivot Tables, explore the available functions, show step-by-step instructions, and share practical scenarios where this feature shines.

Key Takeaways:

• Subtotals in Pivot Tables aren’t limited to just Sum.
• You can add multiple subtotal functions at once.
• Options include Sum, Count, Average, Max, Min, Product, and more.
• Subtotals can be easily added or removed with Field Settings.
• Multiple subtotals provide broader insights into your dataset.

Exercise Workbook:

---

Understanding Pivot Table Subtotals

Why Subtotals Matter in Pivot Tables

Subtotals are essentially the middle ground between raw data and final grand totals. Instead of only giving you one perspective—like the sum of sales or the count of transactions—they allow you to evaluate multiple dimensions at once.

For example, let’s say you’re analyzing sales by product. With subtotals, you could quickly see:

• Sum: Total revenue per product.
• Count: Number of transactions or orders per product.
• Average: Average sales per transaction.
• Max/Min: Highest and lowest sales amounts.

Instead of creating separate tables for each, Pivot Tables let you layer these subtotals together, giving a holistic view of your dataset.

The Full List of Subtotal Functions

Here are all the functions you can apply in Pivot Tables for subtotals:

• Sum – Adds up all values.
• Count – Counts all items, including text and numbers.
• Average – Returns the mean of the values.
• Max – Shows the highest value.
• Min – Shows the lowest value.
• Product – Multiplies all values together.
• Count Numbers – Counts only numeric values.
• StdDev – Estimates the standard deviation based on a sample.
• StdDevp – Calculates the standard deviation for the entire population.
• Var – Estimates variance based on a sample.
• Varp – Calculates variance for the entire population.

This range of functions means Pivot Tables aren’t just for quick summaries—they can be used for basic statistical analysis as well.

 

Create Multiple Subtotals in Excel Pivot Tables

Adding Subtotals

Let us now have some fun with multiple subtotals!

STEP 1: This is our Pivot Table. Click on the arrow beside Products

Select Field Settings

STEP 2: Select Custom for the Subtotals option. And you can select Sum, Count, Average, Max, Min to see what happens!

Click OK

Now you have the different subtotals for each Product! You can see that for Bottles and Ice Cubes, the sum, count, average, max and min are all being shown!

By following the same steps, you can add or remove other functions, including product, count numbers, StdDev, StdDevp, Var and/or Varp!

Removing Subtotal

To remove these subtotals, follow the steps below:

STEP 1: Right-click on the row label you want to remove.

STEP 2: Click on subtotal to remove the check mark.

The subtotals will be removed!

The subtotals of the field selected will be removed! You can also add multiple grand totals to your Pivot Table, Click here to learn how!

 

Practical Scenarios for Multiple Subtotals

• Sales Analysis – Sum for total revenue, Count for number of orders, Average for order value.
• Employee Performance – Max for best performance, Min for lowest, Average for consistency.
• Inventory Management – Count Numbers for stock items, Product for calculating bundle values.
• Customer Transactions – Sum for total spending, StdDev for variability in order sizes.
• Survey Data – Count for responses, Average for satisfaction score, Var for diversity in opinions.

Each scenario shows how powerful it can be to apply multiple subtotals instead of sticking with just one.

 

FAQs

What are Pivot Table Subtotals and why are they useful?

Pivot Table Subtotals are intermediate calculations that summarize data at a grouped level before reaching the grand total. By default, Excel applies the Sum function, but you can switch to other calculations like Average, Max, or Count. They’re useful because they give you insights into your data from multiple angles without creating separate Pivot Tables. For example, you could see both the total revenue and the average transaction value per product. This flexibility makes subtotals one of the most powerful features in Pivot Table analysis.

Can I apply more than one subtotal function at the same time?

Yes, Excel allows you to apply multiple subtotal functions simultaneously. This means you could view the Sum, Count, Average, Max, and Min for each category all in one Pivot Table. Instead of juggling different tables, you get a holistic view of your dataset in a single layout. This feature is especially handy for comparing performance across products, regions, or employees. In short, you’re not stuck with just one perspective—you can analyze your data from multiple angles at once.

What functions are available for Pivot Table Subtotals?

Excel provides a comprehensive list of subtotal functions, including Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, and Varp. These cover everything from simple totals to statistical calculations like standard deviation and variance. For example, StdDev helps measure variability in sales data, while Var and Varp provide variance insights. With these options, you can go beyond simple summaries and conduct light statistical analysis right inside a Pivot Table. This makes Excel not just a reporting tool, but also a data analysis powerhouse.

How do I add or remove multiple subtotals in a Pivot Table?

To add subtotals, open the Field Settings for the field you want and select Custom, then check the functions you need. Once you click OK, Excel will display all chosen subtotals for each category in your Pivot Table. To remove them, simply right-click the row label and uncheck Subtotal. This gives you full control over which calculations appear and keeps your Pivot Table from getting too cluttered. Adding or removing subtotals is quick, so you can adjust your analysis on the fly.

In what scenarios should I use multiple subtotals in a Pivot Table?

Multiple subtotals are useful whenever you want to analyze data from more than one angle. For instance, in sales analysis, you could use Sum for total revenue, Count for the number of orders, and Average for order value. In employee performance, Max, Min, and Average can show best, worst, and consistent results. For inventory management, functions like Count Numbers and Product help track stock and calculate bundle values. These practical scenarios show that multiple subtotals reveal richer insights than relying on a single calculation.