Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101

Home > Lessons > Jan's CompLit 101 > Working with Numbers > Formulas > Subtotals > Subtotal

Jan's Working with Numbers

    Formulas: Subtotals: Subtotal

You could create subtotals for each of your groups 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.

List of functions for SubtotalTo use the Subtotal feature, your data must be sorted into groups. You can then choose one of several functions to use for the 'subtotals', not just the usual SUM function.

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
to edit borders with Format Cells > Borders tab 

Start with: Icon: Class disk trips21-Firstname-Lastname.xlsx - Agent Totals sheet (saved in previous lesson)

Subtotal: Create

  1. Icon: Class diskOpen trips21-Firstname-Lastname.xlsx to the  Agent Totals sheet.
  2. Icon: Class diskSave As trips22-Firstname-Lastname.xlsx in the excel project4 folder of your Class disk.

    Icon: Full disk How to handle a full disk

  3. SChoices for 'At each change in' when header row is not selected.elect A5:F23 on the Agent Totals sheet.
    Dialog: Subtotal> Chavez >Sum (Excel 2010)This is the data for all the travel agents but not the header row and not the Totals row.
  4. On the  Data  tab in the Outline tab group, click on the button Subtotal Button: Subtotals (Excel 2010).
    The Subtotal dialog appears.
  5. Click the arrow to the right of Chavez to open the list of choices.
    These would be easier to understand if they were the column labels instead of the first data item in the column.
  6. Click on Cancel to close the dialog.
  7. Dialog: Subtotal - At each change in: when header row was includedDialog: Subtotal - when header row was includedReselect the data and include the header row, A4:F23.
  8. Click on the Subtotal button again.
    This dialog looks better!
  9. Drop the list for 'At each change in:'.
    This time we see the column labels. Much easier to understand!
  10. Match your dialog to the one illustrated.
    • Travel Agent.
      This is sets the group that you want to subtotal, for each different agent. That's why you had to sort first.
    • Sum
      You can also use a function like Average or Max or Min or Count. It's odd to call these 'subtotals'!
    • Both Number of people and Total sale.
      These will be the columns for which Excel will do a SUM subtotal.
    • Summary below data
      This adds a Grand Total row, which will create a problem since you already have a Totals 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.

  11. Click on OK to close the Subtotal dialog and apply your choices.
    Look at what happens to the total you already had on the sheet. It's a HUGE number. This cell added up the column so it is adding the subtotals and grand total to what you really wanted. Whoops.
  12. Delete the row containing your original Totals, row 29.
  13. Icon: Class diskSave.

Subtotals and Grand Total summary added 

Subtotal: Expand/Collapse

On the left is a new area, showing the arrangement of subtotals, which ones are expanded and which ones are collapsed.

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

The number buttons Buttons: Level 1, 2, 3 at the top of this new area collapse and expand whole levels of the display all at once- Button: Level 1 Level 1 = Grand Total, Button: Level 2 Level 2 = all Subtotals, Button: Level 3 Level 3 = All Data.

  1. Icon: Experiment Experiment: Expand/Collapse
    on each of the Buttons: Level 1, 2, 3 buttons to expand and collapse the levels. Try different orders to your clicks. Look at the row headings. Numbers come and go as you expand and collapse.

    Level 1 = Grand Total only Level 2 showing = subtotals and grand total

    When you are ready to continue...

  2. Level 3: all data, subtotals, grand totalExpand the whole sheet by clicking the Level 3 button, if necessary.
  3. Click on the Button: Expand and Button: Collapse buttons under Level 2 to expand and collapse individual sections of the sheet.
  4. Click the Level 2 button again, to show just the subtotals.
  5. Icon: Class diskSave.

Repair Formatting: Borders Dialog

Adding in new lines and moving columns can mess up your formatting. Some borders are hard to see in Normal view, especially at the left edge of the sheet next to the window edge. The Borders tab of the Format Cells dialog lets you edit the borders for a single cell or for a selection, including the color and style for the border's line.

  1. Print Preview: Agent Totals sheet - just subtotals - Formatting glitchesLook at the Print Preview.
    There are several glitches that are not easy to see in Normal view. (Your preview may look somewhat different.)
    • Header: Excel Project 4, not Project 3 now.

      How it happened: The workbook was previously worked on in Project 3.

    • Borders on Blank Row: The row below the Grand Total should not have any borders.  

      How it happened: You deleted the original Totals row but not the blank row above it. The formatting for that blank row is hard to see in Normal view.

    • Borders on First Column: There should be a left border. There should not be a border between the first and second columns, which some cells have.

      How it happened: You moved the Trip column to the right and it brought its left border with it. That left the Travel Agent column without a left border and with a border between the original cells in those two columns.

  2. Edit the header so that the right section reads Excel Project 4.
  3. Select the row below the Grand Total and Clear Formats.
    (Hint: Use the menu for the Clear button on the Home tab)
    This removes the left and right border from the row but also the top border, which is also the bottom border for the Grand Totals. So the fix for one problem created another one. It's easy to fix!
  4. Dialog: Format Cells > Borders > Outline (Excel 2010)Button: Borders > More Borders... (Excel 2010)Select all of the rows from the header (row 4) through the Grand Total (row 27).
  5. On the Home tab click the arrow for the Border button to open its menu.
    You don't want to use one of the border choices on the menu. Those will apply the default line color Black. The border from the table style is blue. You need to open the full dialog.
  6. Select More Borders...
    The Format Cells dialog opens to the Border tab.
  7. Icon: Experiment Experiment: Borders
    Try clicking various of the buttons surrounding the borders preview. Create various combinations of borders.
    Try clicking directly on a border in the preview section.
    Select a different line style or color. The preview does not automatically update! You must click on a button or on a border in the preview to apply this new choice.

    When you are ready to continue...

  8. Set the line style to a thin solid line and the color to Blue, Accent 1, Lighter 40%.
  9. Click on the button Outline to set a border around the outside of the selection.
  10. Click on OK to close the dialog and apply your changes.
  11. Dialog: Format Cells > Border tab > top and bottom border onlyDialog: Format Cells > Border > preview shows that some cells have a left border, but not allSelect B4:B27 (the Trip column) and from the Border button menu select More Borders... again.
    The preview shows a hazy left line, which means that some cells in the selection have a border here but some do not. The buttons surrounding the preview light up only when ALL cells in the selection have this border.
  12. Click the left border in the preview twice.
    The first click applies a left border to all the selected cells. The second click turns it off.
    The preview should show no left or right border, just top and bottom border.
  13. Compare your print preview to the image below. If necessary, add any missing borders and remove any extra ones.
  14. Icon: Class diskSave.
  15. Print Print just this sheet with only subtotals showing (Level 2).

    Print out: Subtotals and Grand Total