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.
To 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.
|What you will learn:||to use Subtotal command
to expand/collapse subtotal display
to edit borders with Format Cells > Borders tab
Start with: trips21-Lastname-Firstname.xlsx - Agent Totals sheet (saved in previous lesson)
Save As trips22-Lastname-Firstname.xlsx in the excel project4 folder of your Class disk.
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.
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.
Your new subtotal and grand total rows inherit their background color from the row above.
On the left is a new area, showing the arrangement of subtotals, which ones are expanded and which ones are collapsed.
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 of data also.
The number buttons at the top of this new area collapse and expand whole levels of the display all at once- Level 1 = Grand Total, Level 2 = all Subtotals, Level 3 = All Data.
Click on each of the 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.
When you are ready to continue...
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.
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.
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.
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...