## 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**.

**Fig 1: Excel Web App #1:**Sales data – 100 records. Labels in row 1, and records in rows 2 to 101

## 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 on the ribbon, or
- The shortcut sequence
- 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 on the ribbon, or
- The shortcut sequence
- 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: 8 Sep 2017, 8:07 pm [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}`