Category: Excel Training

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 to be 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:

1. Sort the list on the field for which you want subtotals inserted.
2. Click the Subtotal button in the Outline group on the Data tab.The Subtotal dialog box appears.

   

   Use the Subtotal dialogue box to specify the options for the subtotals.
3. Select the field for which the subtotals are to be calculated in the At Each Change In drop-down list.
4. 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.
5. Select the check boxes for the field(s) you want to total in the Add Subtotal To list box.
6. 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 select the Page Break between Groups check box in the Subtotal dialogue box before you click OK to subtotal the list.