Microsoft Excel - Subtotals

Subtotals

Excel will automatically calculate subtotal and grand total values in a sorted list by finding the separations of data in a nominated column. An outline is also applied to the list so that you can display and hide the detail rows for each subtotal.

Sort the data by the column that you wish to subtotal.

Select from the Data tab →

Subtotal.

Nominate the column in the At each change in box, choose the function from the Use function box and then check the checkboxes in the Add subtotal to box.

You can use the same function on as many columns as you like but to display subtotals in a list with more than one type of calculation you have to complete the first set of subtotals and then choose Data, Subtotals again. Specify the second formula and turn-off Replace current subtotals. Each different function will appear on a separate row.

If you have to have different functions, for example a SUM for one column but an AVERAGE for another and you want to have all the subtotals in the same row then you have to complete the subtotals for each column using the same function and then change the formulas in the cells.

The value of the first argument of the SUBTOTAL function (see below) determines the summary function used; SUM is 9 and AVERAGE is 1 etc.

Select the column where you have the formulas to edit and choose Edit, Replace, complete the Find what and Replace with boxes and then click the Find Next button, click Replace as you get to each formula in the cell.

Do not click Replace All as you could damage the data.

You can nest subtotals for sub-groups within existing groups. In the example, Country subtotals are produced and then the data is subtotalled by Product within each country. The outer subtotals, the Country subtotals are produced first and then Data, Subtotals is chosen again. This time the At each change in column is specified as Product, the function and columns are the same but the Replace current subtotals checkbox is cleared so that the existing Country subtotals are retained.