Data Groups & Formulas:
Subtotal

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007/2010  or españolIcon: Change web



You could create subtotals yourself by inserting some blank lines and creating new formulas to add the appropriate cells. But Excel has a special Subtotal command that does this for you. You can choose what value to subtotal and what value to group in.


Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & Formulas
    SubtotalsTo subtopics
    FootprintSort
    FootprintSubtotal
    FootprintFormat Groups
    Images & DrawingsTo subtopics
    Changes & FormulasTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Project 5: DesignTo subtopics


Search 
Glossary
  
Appendix


Icon Step-by-Step

Step-by-Step: Subtotal

 Icon Step-by-Step

What you will learn:

to use Subtotal command
to expand/collapse subtotal display 

Start with: Class disk 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. Message: Found a row of data above your selectionFrom 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. Dialog: Subtotal on Travel AgentClick 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.

Tip  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!

Subtotals added
 


Subtotal: Expand/Collapse

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

A Button: Collapse 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 Button: Expand Expand button shows that the area is collapsed, showing only the subtotals. Clicking the button will expand the display to show the rows.

The Buttons: Level 1, 2, 3 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.

Only Grand Total showing

  1. Click on each of the Buttons: Level 1, 2, 3 buttons to expand and collapse the levels.  
     

    The Button: Level 1 Level 1 button shows only the Grand Total.

    The Button: Level 2 Level 2 button shows the subtotals and Grand Total.

    The Button: Level 3 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 Button: Expand and Button: Collapse buttons under Level 2 to expand and collapse individual sections of the sheet.
     
  3. Level 1 showingClick the Level 2 button again, to show just the subtotals.
     
  4. Class disk Save as  trips23.xls  to your Class disk in the excel project4 folder.
    Full floppy disk 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 Print just this sheet with only subtotals showing (Level 2).

    Print out: Subtotals and Grand Total