Data Groups & Formulas:
Subtotal

Step-by-Step: Subtotal

---

What you will learn:

to use Subtotal command to expand/collapse subtotal display

---

Start with: trips22.xls - Agent Totals sheet (saved in previous lesson)

Subtotal: Create

1. Select A5:F23 on the Agent Totals sheet. This is the data for all the travel agents.
    
2. From the menu select  Data  |  Subtotals… 

   A message appears asking if the cells above the selection are labels. Since they are, you should have included them. Isn't Excel being helpful here?
    

3. Click on Yes. The Subtotal dialog appears.
    
4. Match your dialog to the one illustrated. You want a subtotal at each change of Travel Agent. (This is why you had to sort first.)
    

   You want to Sum. You want subtotals for both Number of people and Total sale. You definitely want a Summary below data, since that adds a Grand Total row.

   The Replace current subtotals box does not really matter this time, since there not any subtotals yet.  If you Subtotal again later, you need the replace the old subtotals with the current ones.

  If you had not deleted the Totals row in the last lesson, the totals there would include the subtotals also. Your totals would be twice as large as they should be!

 

---

Subtotal: Expand/Collapse

On the left is a new area, showing the arrangement of subtotals.

A Collapse button shows that the area is currently expanded to show the data and the subtotals. Clicking this button will collapse the display, hiding the data in that part of the table and showing only the subtotal.

An Expand button shows that the area is collapsed, showing only the subtotals. Clicking the button will expand the display to show the rows.

The number buttons at the top of this new area will collapse and expand whole levels of the display all at once- Grand Total, Subtotals, All Data.

1. Click on each of the buttons to expand and collapse the levels.  
    

   The Level 1 button shows only the Grand Total.

   The Level 2 button shows the subtotals and Grand Total.

   The Level 3 button expands the display to show all the data again.
    

2. Expand the whole sheet by clicking the Level 3 button, if necessary. Click on the and buttons under Level 2 to expand and collapse individual sections of the sheet.
    
3. Click the Level 2 button again, to show just the subtotals.
    
4. Save as  trips23.xls  to your Class disk in the excel project4 folder.
   How to handle a full disk
    
5. Look at the Print Preview. You copied the header when you copied the sheet so you do not have to create one this time.  Hurrah!
    
6. Print just this sheet with only subtotals showing (Level 2).