Subtotals
One of the simplest ways to summarise worksheet data is to add subtotals.
Example: An analyst is interested in the sales data shown in figure 1, worksheet(1) named Sales Data 1
.
This data set is constructed as follows:- row 1 is the header or labels row, and describes the fields in each column. This text data has some labels containing space characters. For example, worksheet column A is labeled Invoice No
. The numbers 150100, 150101, 150102, ...
are in the Invoice No
fields. There are 11 fields in total. Each row in the body of the data is one unique record. Some blank or empty empty cells are OK, but there should be no empty rows or columns in the data set. When you click in a cell within the data, the data set becomes the Current Region.
Subtotals – manual
Reference: figure 1 Worksheet: Sales Data 1
. Suppose that the Analyst is interested in the sales volume achieved by individual salespersons and intends to add subtotals to the data.
Subtotals can be added manually by:
- Sorting the data set on the Salesperson column – see figure 2
- To sort the data in ascending order, select a cell in the Salesperson column (field)
- Then select the sequence Data > Sort & Filter > AZ↓ on the ribbon, or
- The shortcut sequence Alt A SA
- The data for Jellison ends at row 18
- Insert a new row at row 19, then use the SUM function, or the SUBTOTAL function to summarise the data
- Repeat this process for the other salerpersons
- Click on a cell in the data set
- Then select the sequence Data > Outline > Subtotal on the ribbon, or
- The shortcut sequence Alt A B
- This displays the Subtotal dialog box- see figure 3
- Figure 3 – the Subtotal dialog box options – Ⓐ At each change in: select Salesperson from the drop down list, Ⓑ Use function: Subtotal, and Ⓒ Add subtotal to: the three quantity columns
- Click OK – to display the list in figure 4
- The expand / contract feature is used by clicking on a level in the \(\boxed{1}\), \(\boxed{2}\), \(\boxed{3}\) sequence or a group a
,
item
- \(\boxed{1}\) – Grand Total
- \(\boxed{2}\) – Salesperson Total
- \(\boxed{3}\) – full detail, or click a level 2
to expand individual Salesperson detail
- Click
or
to expand or contract selected group
- To hide or view the subtotal panel press Ctrl+8
- To add an Average subtotal to the existing Sum subtotal, Use function: Average, and
- Ⓓ untick Replace current subtotals (figure 5) to prevent the Sum being overwritten
- The result is shown in figure 6 – with 4 levels
- Using the Sort dialog box, Sort by: Salesperson, Then by: Transport
- Set the subtotal for Salesperson – figure 3, then
- Set the subtotal for Transport – untick Replace current subtotals
- The result is shown in figure 8 – with 4 levels
- Select the data to be copied – the Source
- Press F5 to display the Go To Special dialog box
- Select Visible cells only
- Click OK
- Press Ctrl+C to copy the source to the clipboard
- Select the cell for the target, then press Ctrl+V to Paste.
- This example was developed in Excel 2013 Pro 64 bit.
- Last modified: , [Australian Eastern Standard Time (AEST)]

An easier method is to use the Excel subtotal command. Use of the command is shown in the next three subsections.
Subtotal command
Subtotals – single
Using the same data sort from figure 2, the data is repeated in worksheet(2) named Sales data - subtotal 1
.
Subtotals with the subtotal command:


The subtotal command adds groups to the data set, with expand and collapse items in the subtotal panel to the left of the row header item. See figure 4.
Subtotals – multi
Worksheet(3) named Sales data - subtotal 2
.


Subtotals – nested
Worksheet(4): Sales Data - subtotal 3
The AZ↓ sort command can only sort on one field. To setup a nested subtotal, two or more fields need to be sorted. This requires the multi level features of the Sort dialog box – figure 7.
To set up a Salesperson subtotal, with a Transport nested subtotal


Format subtotals
Any cell formats applied to subtotals, are removed when the subtotals are cleared.
To clear subtotals, click the Remove All button in the Subtotal dialog box.
Copy visible subtotal cells
To copy the visible cells (visible subtotal data)
Note: steps 2 to 4 can be replaced by the Alt+; keyboard shortcut (see EXCEL: useful keyboard shortcuts – pdf)
Subtotal function
The subtotal procedure used the Excel subtotal function
Syntax: SUBTOTAL(function_num,ref1,[ref2],…)
Function_num (includes hidden values) | Function_num (excludes hidden values) | Function |
---|---|---|
1 | 101 | AVERAGE |
2 | 102 | COUNT |
3 | 103 | COUNTA |
4 | 104 | MAX |
5 | 105 | MIN |
6 | 106 | PRODUCT |
7 | 107 | STDEV |
8 | 108 | STDEVP |
9 | 109 | SUM |
10 | 110 | VAR |
11 | 111 | VARP |
Using SUBTOTAL for summary statistics
You can use the SUBTOTAL function to indirectly link cell labels to the summary statistic procedure – figure 9.

The example in figure 9 is available on the Summary Stats
worksheet – Excel Web App #1
The ListSubTotal array has the values ={"average",101,1;"count",102,2;"counta",103,3;"max",104,4;"min",105,5;"product",106,6;"stdev",107,7;"stdevp",108,8;"sum",109,9;"var",110,10;"varp",111,11}