Data Groups & Formulas:
Subtotals

Did you want Working with Numbers: 2007,2010,2013,2016  or español

Creating groups within your data is generally done so you can do subtotals or other calculations about the records in just that group.

Excel provides help by letting you use its Sort features and then use its Subtotal command.

With these commands you can sort a plain list of sales and then automatically create subtotals. The subtotals can even be functions besides Sum, like Average, Max or Min.

You might want to see sales for each salesman, or in a geographic area, or for each product sold, or for a day, week, or month in which the sales took place. 

The example below was sorted by Salesman and Date, and then subtotaled on Amount of Sale.

Original data

Sorted on Salesman, then Date
Subtotaled on Amount of Sale

Sorting and subtotaling often mess up formulas that were already in place. You will need to learn how to repair the damage.

Once you have created subtotals for one grouping, it is not easy to subtotal on a something different. Therefore it is a good idea to put the data that you want to subtotal on a new sheet and leave the original data in place. After all, you might get another spiffy idea later and need the original data.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~

Last updated: 02 May 2012