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.

  3. Select A5:F23 on the Agent Totals sheet.
    This is the data for all the travel agents but not the header row and not the Totals row. This selection creates a problem.
  4. Choices for 'At each change in' when header row is not selected.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.

      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 HUGE. This cell's formula adds up the column so it is adding the subtotals and grand total to what you really wanted. Whoops.

    Total after creating subtotals (Excel 2013)

  12. Delete the row containing your original Totals, row 29.
  13. Icon: Class diskSave.
    [trips22-Firstname-Lastname.xlsx]

Subtotals and Grand Total summary added 

Your new subtotal and grand total rows inherit their background color from the row above.


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
    Click
    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. Notice what happens with the alternating row colors.

    Level 1 = Grand Total only Level 2 showing = subtotals and grand total
    Level 3: all data, subtotals, grand total

    When you are ready to continue...

  2. Expand 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.
    [trips22-Firstname-Lastname.xlsx]

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.)
    • 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. 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!
  3. Button: Borders > More Borders... (Excel 2010)Select all of the rows from the header (row 4) through the Grand Total (row 27).
  4. 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.
  5. Dialog: Borders - preview initially (Excel 2013)Select More Borders...
    The Format Cells dialog opens to the Border tab.

    The preview in this dialog shows a solid border where all cells in the selection have that border and a fuzzy gray border where only some cells in the selection have that border.

    Currently the selection does not have a left border.

  6. Icon: Experiment Experiment: Borders

    • Dialog: Format Cells > Border > preview shows that some cells have a left border, but not allClick various of the buttons surrounding the borders preview to create various combinations of borders.
      These buttons are toggle buttons. Click to turn border on and click it again to turn border off.
    • Click directly on a border in the preview section to add and remove borders.
    • 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...

  7. Dialog: Format Cells > Borders > Outline (Excel 2010)Set the line style to a thin solid line and the color to Blue, Accent 1, Lighter 40%.
  8. Click on the button Outline to set a border around the outside of the selection.
  9. Dialog: Borders - preview shows outline and horizontal borders (Excel 2013)Apply border to the horizontal middle line and remove border from the vertical middle line.
    Icon: Tip Fuzzy gray borders: If there is a fuzzy gray border, it takes two clicks to remove the border. The first click applies a border and the second removes it.
  10. Click on OK to close the dialog and apply your changes.
  11. Open Print Preview again.
    Compare your print preview to the image below. You should have a border around the whole table and horizontal borders for each row of data. If necessary, add any missing borders and remove any extra ones.
  12. Icon: Class diskSave.
    [trips22-Firstname-Lastname.xlsx]
  13. Print Print just this sheet with only subtotals showing (Level 2).

    Print out: Subtotals and Grand Total