This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2007

Calculating Subtotals

Once you have sorted a data range into groups, Excel's Subtotal command can create a subtotal for each group, automatically inserting a new row and displaying the total for each line.

return to topAdding Subtotals

Subtotals are added using the Subtotal dialog box, which is accessed from the Data command tab.

NOTE: The Subtotal command is not available for Excel Tables. To find subtotals for Table data, you can convert it to a normal data range and then perform the Subtotal command. For more information, refer to Converting a Table to back to a data range.

  1. Sort your data range

  2. Select a cell within the data range

  3. From the Data command tab, in the Outline group, click SUBTOTALSubtotal button
    The Subtotal dialog box appears.
    HINT: The following two graphics show a data range that has been sorted according to days of the week, and its corresponding Subtotal dialog box, which has been set to display subtotals for the total hours worked on each day of the week.
    Sample data range
    Subtotal dialog box

  4. From the At each change in pull-down list, select the column containing data groups for which you want subtotals
    NOTE: It is important to have this column sorted into data groups, because the Subtotal command subtotals numerical data at every change in the column.
    EXAMPLE: Select Day

  5. From the Use function pull-down list, select the function that will be used to create subtotals
    EXAMPLE: Select Sum

Sum
adds the values for each data group

Count
determines the number of records in each data group

Average
finds the average value in each data group

Max
finds the highest value in each data group

Min
finds the lowest value in each data group

Product
multiplies compounding values in each data group

  1. From the Add subtotal to scroll box, select the column with the numerical data you want subtotaled

  2. OPTIONAL: At the bottom of the dialog box, select the appropriate options

  3. Click OK
    Subtotal lines are added according to the column you specified in the At each change in pull-down list and the data groups you sorted in that column.

return to topAdjusting Views with Subtotals

When you have applied subtotals to your data range, an outline bar appears to the left of the row numbers.

TheOutline levelsrepresents levels of detail in the view

Level 1: grand total

Level 2: subtotals and grand total

Level 3: all data in range

 

To expand the view one level:

  1. Click the plus signPlus sign

To collapse the view one level:

  1. Click the minus signMinus sign

Example of outline levels

return to topRemoving Subtotals

If you no longer need subtotals, you can easily remove them without deleting rows.

  1. Select a cell within the data range

  2. From the Data command tab, in the Outline group, click SUBOTOTALSubtotal button
    The Subtotal dialog box appears.

  3. Click REMOVE ALL
    The subtotals are removed.

return to topRe-sorting the Data

If you re-sort a data range containing subtotals, Excel will warn you that it will remove the subtotals before re-sorting. After the data range has been re-sorted, you can add subtotals again.

Excellence. Our Measure. Our Motto. Our Goal.