Home > Sample chapters > Microsoft Office > Excel

Using Formulas and Functions

Summing with Subtotals and Grand Totals

You frequently need to organize the data in an Excel worksheet by one or more criteria. For example, you might have a worksheet in which you list yearly sales for each product you offer, with the products broken down by category. If your data is organized this way, you can have Excel calculate a subtotal for each category of products. When you create a subtotal, you identify the cells with the values to be calculated and the cells that identify the change from one category to the next; Excel updates the subtotal and grand total for you if the value of any cell changes.

Create a Subtotal

  1. Click any cell in the range you want to subtotal.

  2. Click the Data tab.

  3. In the Outline group, click Subtotal.

  4. Click the At Each Change In down arrow.

  5. Click the value on which you want to base the subtotals.

  6. Click the Use Function down arrow.

  7. Click the subtotal function you want to use.

  8. Select which columns should have subtotals calculated.

  9. Click OK.

Remove a Subtotal

  1. Click any cell in the subtotaled range.

  2. Click the Data tab.

  3. Click Subtotal.

  4. Click Remove All.

    httpatomoreillycomsourcemspimages632203.png