Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 25

Excel Subtotals

The Subtotal feature in Excel is an additional way to summarize data. The Subtotal—which does not necessarily apply to numerical sums only—can summarize and display your data in a logical way.

Excel can automatically calculate subtotal and grand total values in a table. When you insert automatic subtotals, Excel outlines the table so that you can display and hide the rows’ details for each subtotal. This is the same concept as was explored in the previous chapter about outlines.

Subtotals are calculated with a summary function. (Examples of summary functions include Sum, Count, and Average.) Subtotal calculates subtotal values with one of 11 different functions. It is possible to utilize more than one type of summary function for each column.

To insert subtotals, you have to organize the table so that the rows you want to subtotal are grouped together by a certain parameter or type of information. You can calculate subtotals for any column that contains numerical values. However, only the Count function can be applied to nonnumeric data. If you attempt to calculate any other function for nonnumeric data, the result will be zeros for the sum and errors for all other functions.

Grand total values are derived from the data, not from the values in the subtotal rows. If you use the Average Summary function, the grand total displays an average of all the rows’ details in the list, not an average of the values in the subtotal rows (it is not the average of the ...