How to use the Subtotal tool
What's on this page
The Subtotal feature lets you insert totals and grand totals automatically, this feature was added to Excel 2010 and is still around in later Excel versions. The image above demonstrates the Subtotal tool applied to cell range B2:E30.
The + and minus signs to the left of the row numbers let you expand and collapse rows respectively. This functionality lets you easily examine the values behind a specific subtotal that may interest you more than the others.
Prepare data
Before you can use the Subtotal tool you need to prepare your data:
 Remove blank rows.
 Sort the data based on the column or columns you want to add subtotals to.
How to sort data
 Press with right mouse button on on any cell in your data set. A popup menu appears, see image above.
 Press with mouse on "Sort".
 Press with left mouse button on "Custom Sort...", a dialog box appears.
 Sort by "Salesperson"
 Add a Level
 Then by "Region"
 Press with left mouse button on OK button.
The image above shows the data set sorted based on column B and then on column C.
Note, you can't insert subtotals to an Excel Table. This will grey out the "Subtotal" button.
I recommend a Pivot Table if you want the functionality that the Excel Table has and the Subtotal feature.
How to start the Subtotal tool?
Go to tab "Data" on the ribbon, press with left mouse button on the "Subtotal" button. A dialog box appears, see image above.
Disable the "Data Bars" checkbox and enable the "Sales" check box. Press with left mouse button on the OK button to apply settings and create subtotals.
The image above demonstrates subtotals in cell D11 and D20 created by the Subtotal tool. The formula bar shows that the Subtotal tool inserted SUBTOTAL functions in both cell D11 and D20.
The SUBTOTAL function has many arguments which I won't be demonstrating in this article, check out the SUBTOTAL function article if you want to learn more about them.
Press with left mouse button on symbol to collapse rows and press with left mouse button on symbol to expand rows. You can also expand or collapse all rows on a hierarchy level by pressing 1, 2 or 3:
These buttons are located to the left of the row numbers, see the image above or the animated image below.
Subtotals on multiple levels
You can add more subtotals to your data list. We inserted subtotals before when a change occurred in column Salesperson, let's add a new subtotal for each change in column Region.
 Select cell range A1:C20
 Press with left mouse button on "Subtotal" button on tab "Data"
 Press with left mouse button on the dropdown menu below the "At each change in:"
 Select Region
 Clear checkbox "Replace current subtotals"
 Press with left mouse button on OK button.
Dialog box settings
At each change in
The first dropdown list lets you choose which column to use when determining where to insert subtotals to. When a value changes a new subtotal is inserted, this is why it is important to sort the values first.
Use function
The dialog box allows you to pick one function out of eleven functions, they are:

Sum  Calculates a total.
Formula in cell D11:=SUBTOTAL(9,D3:D10)Number 9 represents the SUM function, it adds numbers and returns a total.
97496 + 33568 + 90964 + 24088 + 98629 + 72498 + 89764 + 14867 = 521874 
Count  Counts the number of cells that are not empty.
Formula in cell D11:=SUBTOTAL(3,D3:D10)The first argument is 3 which represents the COUNTA function, it counts all cells that are not empty.
There are eight cells in cell range D3:D10 and they all nonempty. The formula returns 8 in cell D11. 
Average  Returns the average (arithmetic mean) of the arguments.
Formula in cell D11:=SUBTOTAL(1,D3:D10)The first argument is 1, it represents the AVERAGE function. It adds all numbers and then divides by the total count of values.
97496 + 33568 + 90964 + 24088 + 98629 + 72498 + 89764 + 14867 = 521874
521874/8 = 65234.25 
Max  Returns the largest number
Formula in cell D11:=SUBTOTAL(4, D3:D10)Cell range D3:D10 contains the following values: 97496, 33568, 90964, 24088, 98629, 72498, 89764 and 14867.
98629 is the largest one. 
Min  Returns the smallest number
Formula in cell D11:=SUBTOTAL(5, D3:D10)Cell range D3:D10 contains the following values: 97496, 33568, 90964, 24088, 98629, 72498, 89764 and 14867.
14867 is the smallest number. 
Product  Multiplies numbers in cell range
Formula in cell D11:=SUBTOTAL(6, D3:D10)Cell range D3:D10 contains the following values: 97496, 33568, 90964, 24088, 98629, 72498, 89764 and 14867.
97496 * 33568 * 90964 * 24088 * 98629 * 72498 * 89764 * 14867 = 6.8428772708558E+37 
Count numbers  Count the number of cells that contain numbers
Formula in cell D11:=SUBTOTAL(2, D3:D10)There are eight cells in cell range D3:D10 and they all are numbers. The formula returns 8 in cell D11.

StdDev Calculates the standard deviation based on a sample of the entire population.
Formula in cell D11:
=SUBTOTAL(7, D3:D10)The first argument is 7 which represents the STDEV.S function, it was added to Excel 2010. The STDEV.S function returns the standard deviation based on a sample of the entire population.
The standard deviation shows how much the values differ from the mean value of the group, in other words, how they are distributed around the average value, or how spread out the numbers are.
x ̅ is the average.
n is how many values.97496+33568+90964+24088+98629+72498+89764+14867 = 521874
521874/8 = 65234.25 is the average number.(9749665234.25)^2 + (3356865234.25)^2 + (9096465234.25)^2 + (2408865234.25)^2 + (9862965234.25)^2 + (7249865234.25)^2 + (8976465234.25)^2 + (1486765234.25)^2
becomes
32261.75^2 + (31666.25)^2 + 25729.75^2 + (41146.25)^2 + 33394.75^2 + 7263.75^2 + 24529.75^2 + (50367.25)^2
becomes
1040820513.0625 + 1002751389.0625 + 662020035.0625 + 1693013889.0625 + 1115209327.5625 + 52762064.0625 + 601708635.0625 +2536859872.5625
equals 8705145725.5.
8705145725.5/(81) = 1243592246.5
1243592246.5^(1/2) = 35264.6033084168

StdDevp  calculates the standard deviation based on the entire population
Formula in cell D11:=SUBTOTAL(8, D3:D10)The STDEV.P function returns the standard deviation based on the entire population.
x ̅ is the average.
n is how many values.97496+33568+90964+24088+98629+72498+89764+14867 = 521874
521874/8 = 65234.25 is the average number.(9749665234.25)^2 + (3356865234.25)^2 + (9096465234.25)^2 + (2408865234.25)^2 + (9862965234.25)^2 + (7249865234.25)^2 + (8976465234.25)^2 + (1486765234.25)^2
becomes
32261.75^2 + (31666.25)^2 + 25729.75^2 + (41146.25)^2 + 33394.75^2 + 7263.75^2 + 24529.75^2 + (50367.25)^2
becomes
1040820513.0625 + 1002751389.0625 + 662020035.0625 + 1693013889.0625 + 1115209327.5625 + 52762064.0625 + 601708635.0625 +2536859872.5625
equals 8705145725.5.
8705145725.5/8 = 1088143215.6875
1088143215.6875^(1/2) = 32987.0158651476

Var  estimates variance based on a sample
Formula in cell D11:=SUBTOTAL(10, D3:D10)The VAR.S function returns the variance based on the entire population. Variance is the average of the squared differences from the mean.
x ̅ is the average.
n is how many values.97496+33568+90964+24088+98629+72498+89764+14867 = 521874
521874/8 = 65234.25 is the average number.(9749665234.25)^2 + (3356865234.25)^2 + (9096465234.25)^2 + (2408865234.25)^2 + (9862965234.25)^2 + (7249865234.25)^2 + (8976465234.25)^2 + (1486765234.25)^2
becomes
32261.75^2 + (31666.25)^2 + 25729.75^2 + (41146.25)^2 + 33394.75^2 + 7263.75^2 + 24529.75^2 + (50367.25)^2
becomes
1040820513.0625 + 1002751389.0625 + 662020035.0625 + 1693013889.0625 + 1115209327.5625 + 52762064.0625 + 601708635.0625 +2536859872.5625
equals 8705145725.5.
8705145725.5/(81) = 1243592246.5

Varp  estimates variance based on population
Formula in cell D11:=SUBTOTAL(10, D3:D10)The VAR.P function returns the variance based on the entire population. Variance is the average of the squared differences from the mean.
x ̅ is the average.
n is how many values.97496+33568+90964+24088+98629+72498+89764+14867 = 521874
521874/8 = 65234.25 is the average number.(9749665234.25)^2 + (3356865234.25)^2 + (9096465234.25)^2 + (2408865234.25)^2 + (9862965234.25)^2 + (7249865234.25)^2 + (8976465234.25)^2 + (1486765234.25)^2
becomes
32261.75^2 + (31666.25)^2 + 25729.75^2 + (41146.25)^2 + 33394.75^2 + 7263.75^2 + 24529.75^2 + (50367.25)^2
becomes
1040820513.0625 + 1002751389.0625 + 662020035.0625 + 1693013889.0625 + 1115209327.5625 + 52762064.0625 + 601708635.0625 +2536859872.5625
equals 8705145725.5.
8705145725.5/8 = 1088143215.6875
Add subtotal to
This setting allows you to choose which columns to add subtotals to.
Replace current subtotals
This checkbox lets you substitute current subtotals with new ones, if enabled.
Pagebreak between groups
This checkbox, if enabled, lets you put each group in a new page.
Summary below data
The last checkbox adds a summary below all groups, for example, if a total is calculated for each group then a grand total is calculated below all groups.
Add data bars
Now it is time to spice up your data table with some chart bars. Hold on, I am not going to insert a chart. Conditional Formatting is what we are going to use actually.
 Create a new column and type =C2 in cell D2.
 Copy cell D2 to the cells below.
 Remove cells that link to subtotals and grand total.
 Select cell range D2:D24.
 Go to tab "Home" on the ribbon.
 Press with left mouse button on "Conditional formatting" button.
 Press with left mouse button on "Data Bars".
 Press with left mouse button on "More Rules..."
 Enable the checkbox "Show Bar Only".
 I chose a "Solid Border" from the "Border" dropdown menu.
 Press with left mouse button on OK button.
The Data Bars let you quickly compare numbers, finding outliers, or interesting statistics.
Remove all subtotals
You can easily remove all subtotals, select the cell range you want to remove subtotals from. Go to tab "Data", press with left mouse button on "Subtotal" button. Press with left mouse button on "Remove All" button.
Build a Pivot Table/Pivot chart
A pivot table allows you to summarize a data table in many different ways, using different criteria. It is easy and you will be analyzing your data in no time. Pivot tables are subtotals on steroids.
I highly recommend reading this blog post: Analyze trends using pivot tables, there is also a quick introduction to pivot tables.
You can also compare data, year to year, month to month and whatever time frame you like.
Features category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Question: How do I create a dropdown list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates different ways to reference an Excel defined Table in a dropdown list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent dropdown lists in multiple cells. The dropdown lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (dropdown list). The above […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
In this article, I am going to show you how to quickly change Pivot Table data source using a dropdown […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
This article describes how to create a dropdown list populated with sorted values from A to Z. The sorted list […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
A dropdown list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
Functions in this article
More than 1300 Excel formulas
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form