7.4 Using the subtotal

Google Sheets does not have this feature.

The subtotal feature can be used on a list to calculate subtotals of values for particular categories; for example, for each salesperson, or each product, or each month.

Spreadsheets exercise 5

Working with lists

Skills practised
  • Sorting data
  • Using subtotal functions
  • Using the use function
  1. Open or display the workbook file Telephone sales available in exercise 4.
  2. Click anywhere within the Salesperson column.
  3. Click on the SORT ASCENDING button on the DATA ribbon.
  4. Click on the SUBTOTAL button on the DATA ribbon.
    TS0730a.jpg
  5. For the option AT EACH CHANGE IN choose SALESPERSON.
  6. For the USE FUNCTION option choose SUM.
  7. For the ADD SUBTOTAL TO option choose SALES and clear the tick from MONTH.
  8. Check that SUMMARY BELOW DATA is selected.
  9. Click on OK.
    P0726.jpg

The Outline feature

When the SUBTOTAL feature is used the OUTLINE feature is also invoked.

  1. Click on the 2 button at the top of the OUTLINE area.
    P0727
  2. Click on the plus button next to Owen to display the details just for that salesperson.
  3. Click on the minus button next to the Owen Total to collapse it again.
  4. Repeat to open and collapse each of the other salespeople.
  5. Click on the 3 button to display all the details.

Applying further subtotals

P0728
  1. Click somewhere within the list.
  2. Click on the SUBTOTAL button on the DATA ribbon.
  3. Choose MONTH for the AT EACH CHANGE IN option.
  4. Click on SUM for the USE FUNCTION option.
  5. Click on both UNITS and SALES for ADD SUBTOTAL TO.
  6. Click on MONTH to clear the tick.
  7. Clear the tick in the REPLACE CURRENT SUBTOTALS box so that the sales total for each salesperson will remain.
  8. Click OK. Now extra totals are included for units sold and sales for each salesperson each month.
  9. Click on the 3 button to display only the totals.
    P0729