This is something that is tested almost every year in Grade 12 exam papers: the Subtotal command in the Outline group of the Data ribbon. It is a useful way to aggregate (summarise) large amounts of data quickly.
In the example below, we have one year’s financial data. The data represents monthly sales figures; the monthly sales figures have been grouped into financial quarters. In the example, I use the Subtotal feature to create an outline of the data which includes totals for each of the quarters, as well as a total for the year.
- Select the data, headings included (A2:C14)
- Activate the Data menu
- Click the Subtotal button
- Select the column by which you want to aggregate (group) the data — the data must be sorted by this column!
- Choose which function you want to use
- Choose which column of values you want to aggregate (calculate)
- Select the Summary below data option
- Click OK
- Your data now has subtotals for each Quarter
- You can expand and collapse the levels of the data outline