Excel > Excel 2003 Advanced > Data Management


The Subtotals feature of Excel groups and summarises a list of data. The Sum, Count, Average and other functions can be used for subtotals. Once subtotals are applied, the data is grouped making it easy to view higher level data.

Create subtotals

  1. For subtotals to work, your data needs to be sorted by the column you wish to apply subtotals to. Sort the required column if necessary
  2. Select a cell in the data range
  3. Click Data > Subtotals
  4. The Subtotal dialogue box appears
  5. Subtotal dialogue box
  6. Click the At each change in list arrow and select the column that you wish to subtotal. In our example we have chosen Salesperson, so that it will group and subtotal each salespersons orders
  7. Click the Use function list arrow and select the function to calculate the subtotals
  8. In the Add subtotal to box, check each box for a column that contains values you wish to subtotal
  9. Click Ok

The data list is subtotalled and the Outline group appears on the left side of the screen.

Outline symbols for grouping

Click the Hide Detail and Show Detail buttons in the Outline group to show or hide individual groups.

Click the numbered buttons above the Outline group to group by level. The example below is grouped by the second level showing each salesperson subtotal and the summary data.

Subtotal grouping

It is possible to add more subtotals. Create subtotals as before and uncheck the Replace current subtotals check box in the Subtotal dialogue box.

Remove subtotals

  1. Click Data > Subtotals
  2. Click the Remove All button from the Subtotal dialogue box

All subtotals are removed.

Follow us on

Facebook  Twitter  You Tube