Inserting Subtotals in an Excel 2007 Worksheet

You can use Excel 2007’s Subtotals feature to subtotal data in a sorted list. To subtotal a list, you first sort the list on the field for which you want the subtotals, and then you designate the field that contains the values you want summed — these don’t have to be the same fields in the list.

Excel does not allow you to subtotal a list formatted as a table. You must first convert your table into a normal range of cells. Click a cell in the table and then click the Table Tools Design tab. Click the Convert to Range button in the Tools group, and then click Yes. Excel removes the filter buttons from the columns at the top of the data list while still retaining the original table formatting.

Follow these steps to add subtotals to a list in a worksheet:

Sort the list on the field for which you want subtotals inserted.
Click the Subtotal button in the Outline group on the Data tab.

The Subtotal dialog box appears.

Use the Subtotal dialog box to specify the options for the subtotals.
Select the field for which the subtotals are to be calculated in the At Each Change In drop-down list.
Specify the type of totals you want to insert in the Use Function drop-down list.

When you use the Subtotals feature, you aren’t restricted to having the values in the designated field added together with the SUM function. You can instead have Excel return the number of entries with the COUNT function, the average of the entries with the AVERAGE function, the highest entry with the MAX function, the lowest entry with the MIN function, or even the product of the entries with the PRODUCT function.
Select the check boxes for the field(s) you want to total in the Add Subtotal To list box.
Click OK.

Excel adds the subtotals to the worksheet.

The bottom of a list showing the subtotals and a grand total.

When you use the Subtotals command, Excel outlines the data at the same time that it adds the rows with the departmental salary totals and the grand total. This means that you can collapse the data list down to just its departmental subtotal rows or even just the grand total row simply by collapsing the outline down to the second or first level.

In a large list, you may want Excel to insert page breaks every time data changes in the field on which the list is being subtotaled (that is, the field designated in the At Each Change In drop-down list box). To do this, you simply select the Page Break between Groups check box in the Subtotal dialog box before you click OK to subtotal the list.

Comments (6)

Posted by Janet FEBRUARY 01, 2009 | 02:34PM
How do you add data to this information and maintain the subtotals? I found the only way is to either add the record IN THE RIGHT PLACE, and click on the subtotal button off and on again to refresh. OR turn the subtotaling off, add the record, resort, and turn the subtotaling back on. Is there a better way?

(Report comment)
Posted by Lisa MAY 14, 2009 | 04:07PM
When I collapse the subtotals, all the associated information about the items I am summing is hidden. (Ex: A row with the customer name, salesman's name, etc.) How can I show the associated info on the same line as the Total automatically?

(Report comment)
Posted by ionamine adipex pay cod fed ex AUGUST 02, 2009 | 01:17AM
azpyH3 Great. Now i can say thank you!

(Report comment)
Posted by herbal phentermine vs phentermine AUGUST 02, 2009 | 05:16AM
bjOMyo If you have to do it, you might as well do it right.

(Report comment)
Posted by buy tramadol overnight cod AUGUST 02, 2009 | 03:04PM
9kPrpS Great. Now i can say thank you!

(Report comment)
Posted by Sunil Dutt Sharma OCTOBER 30, 2009 | 05:30PM
Do we have some easy steps and logic explainaton for subtotals

(Report comment)