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 2003/2004

Calculating Subtotals in Databases

Calculating subtotals based on subgroups of information is made easier with the Data Subtotal command. Once the data is sorted, Excel can add subtotals based on data within the database, automatically inserting a new row and adding an appropriate total heading for each line.

return to topAdding Subtotals

  1. Sort the data
    For more information, refer to Using the Sort Command.
    Sorting the database is important for grouping the information you want subtotaled.
    If you do not have column labels on what you are subtotaling, Excel will prompt you to enter column labels or to choose the top row as your column labels. Be aware that if you choose the first row as your column labels, that data will not be included in the subtotals. 

  2. Select a cell within the database

  3. From the Data menu, select Subtotals...
    The Subtotal dialog box appears.
    Subtotal Dialog Box

  4. From the At each change in pull-down list, select the desired option

  5. From the Use function pull-down list, select the desired function

  6. From the Add subtotal to scroll box, select the desired column label

  7. OPTIONAL: At the bottom of the dialog box, select/deselect the appropriate options

  8. Click OK
    Subtotal lines are added to the database along with outlines for adjusting the view of the data.

return to topAdjusting Views with Subtotals

When you have applied subtotals to your database, an outline-type bar appears to the left of the row IDs.

TheLevels of the viewrepresents the levels of the view.
Level 1: grand total
Level 2: subtotals and grand total
Level 3: all data in the database

To expand the view one level:

  1. Click the plus signplus sign

To collapse the view one level:

  1. Click the minus signminus sign

return to topRemoving Subtotals

If you no longer need the subtotals, you can easily remove them with a command rather than deleting each row. To remove the subtotals, use the following instructions:

  1. Select a cell within the database

  2. From the Data menu, select Subtotals...
    The Subtotal dialog box appears.

  3. Click REMOVE ALL
    The subtotals are removed.

return to topRe-sorting the Data

If you re-sort the database, Excel will warn you that it will remove the subtotals and re-sort. After the database has been sorted, you can add subtotals again.

Excellence. Our Measure. Our Motto. Our Goal.