Calculating subtotals

When making extensive reports, there is often a need to calculate subtotals by reporting categories. Subtotals are, most of the time, sums and they might also be an average, a minimum, a maximum, a product, or a count of articles within a report. Subtotals can be made manually and in this „recipe“ it will be about how to use Excel for automatically adding subtotals.

In order to calculate the subtotals we first need to have a report that consists of one or more categories, as a summing criterion. After choosing the report range, the Subtotal option must be selected in the Data ribbon. The dialog box of the same name will open.

In it, we first choose which category we are using to perform subtotal calculation. A category is a column with a constant value, based on which we want to perform a certain arithmetic operation for all members of that category. Then, we choose a function that will be used for aggregation (sum, average, count, maximum, minimum, or product). In the end we select below which column we want to add to the subtotal. Click OK to add the subtotals and the total.

In the dialogue box of Subtotal we have several other options available. The checkbox Summary below data, if checked, is used to display the intermediate and final sums at the bottom of the groups. Otherwise, they will be displayed on the top of the group. Checkbox Page break between groups, if checked, will transfer each new group to a separate page of the report. Finally, the Replace Current Subtotals checkbox is used to replace the previously created subtotals with the new ones. If this field is not checked, the report will be continually updated with the new ones, which can be useful, for example, if we want to make a summary of one category, and a average by another category of report.