Excel > Excel 2007 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 the Subtotal button from the Outline group of the Data tab on the Ribbon
  4. The Subtotal dialogue box appears
  5. The 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

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.

Level 2 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 the Subtotal button from the Outline group of the Data tab on the Ribbon
  2. Click the Remove All button from the Subtotal dialogue box

All subtotals are removed.

Show Me

Follow us on

Facebook  Twitter  You Tube