Another kind of outlining or grouping technique available in Excel is the subtotals feature. If you have some numeric data organized with clear column and row headings, you can use Excel to create automatic subtotals and grand totals for the data.

The following worksheet contains sales information for different products across geographical regions.

To use Excel’s subtotal feature, select the range of data you want to apply subtotals to, and choose Data->Subtotals from the menu bar. Be sure to include the column labels in your selection, so Excel will be able to discern what numbers to total. For this example, you could select A1:E10 and choose Data->Subtotals to invoke the following subtotals dialogue box.

The drop list under the At each change in: heading, gives you options as to the number of rows that will be totalled. (Totals will be applied every time the values under the chosen column label changes.)

The Use function: drop list lets you choose from a list of functions including SUM, AVERAGE, COUNT, PRODUCT, and STDEV to apply to your data. The function you choose, (normally SUM) will be used to calculate the totals.

Under the Add subtotals to: option list, you can select which columns to apply the totals to. You can apply totals to a single column, or to multiple columns in the selected range.

Finally, there are three check boxes at the bottom of the dialogue box that will allow you to:

·              replace any pre existing subtotals

·              put page breaks between totalled groups so they will be printed on separate pages

·              place the subtotals and grand totals above, or below the corresponding data

If you choose to apply totals to each change in region, using the sum function for the profit column, with no page breaks, the resulting worksheet will look like this.

Notice that there is a subtotal for the profit figures at every change in the region value. There is also a grand total for the Profit column at the bottom of the data. You can also that Excel has provided outlined levels, collapse/expand buttons, and numbered outline level buttons associated with the totals. These buttons and outlines work exactly as previously explained. The lower the number on the button, the less detailed information will be shown.

To remove these subtotals, invoke the subtotal dialogue box by choosing Data->Subtotals, and click the Remove All button.