Inserting Subtotals


Excel's Data image from book Outline image from book Subtotal command is a handy tool that inserts formulas into a worksheet database automatically. These formulas use the SUBTOTAL function. To use this feature, your database must be sorted because the formulas are inserted whenever the value in a specified field changes. For more information about the SUBTOTAL function, refer to the sidebar, "About the SUBTOTAL Function," earlier in this chapter.

Note 

When a table is selected, the Data image from book Outline image from book Subtotal command is not available. Therefore, this section applies only to worksheet databases. If your data is in a table and you need to insert subtotals automatically, convert the table to a range by using Table Tools image from book Design image from book Tools image from book Convert To Range. After you insert the subtotals, you can convert the range back to a table by using Insert image from book Tables image from book Table.

Figure 9-26 shows an example of a range that is appropriate for subtotals. This database is sorted by the Month field, and the Region field is sorted within months.

image from book
Figure 9-26: This database is a good candidate for subtotals, which are inserted at each change of the month and at each change of the region.

On the CD 

This workbook, named image from book nested subtotals.xlsx, is available on the companion CD-ROM.

To insert subtotal formulas into a worksheet database automatically, move the cell pointer anywhere in the database and choose Data image from book Outline image from book Subtotal. You will see the Subtotal dialog box, as shown in Figure 9-27.

image from book
Figure 9-27: The Subtotal dialog box automatically inserts subtotal formulas into a sorted table.

The Subtotal dialog box offers the following choices:

  • At Each Change In: This drop-down table displays all fields in your table. You must have sorted the table by the field that you choose.

  • Use Function: Choose from 11 functions. (Sum is the default.)

  • Add Subtotal To: This list box shows all the fields in your table. Place a check mark next to the field or fields that you want to subtotal.

  • Replace Current Subtotals: If checked, Excel removes any existing subtotal formulas and replaces them with the new subtotals.

  • Page Break between Groups: If checked, Excel inserts a manual page break after each subtotal.

  • Summary below Data: If checked, Excel places the subtotals below the data (the default). Otherwise, the subtotal formulas appear above the data.

  • Remove All: This button removes all subtotal formulas in the table.

When you click OK, Excel analyzes the database and inserts formulas as specified-and even creates an outline for you. Figure 9-28 shows a worksheet after adding two sets of subtotals: one that summarizes by month, and another that summarizes by region. You can, of course, use the SUBTOTAL function in formulas that you create manually. Using the Data image from book Outline image from book Subtotals command is usually easier.

image from book
Figure 9-28: Excel adds the subtotal formulas automatically and creates an outline.

Caution 

If you add subtotals to a filtered database, the subtotals may no longer be accurate when you remove the filter.

Tip 

When you apply the second subtotal, remember to clear the Replace Current Subtotals check box on the Subtotal dialog box.

The formulas all use the SUBTOTAL worksheet function. For example, the formula in cell E9 (total sales for January) is as follows:

 =SUBTOTAL(9,E2:E7) 

Although this formula refers to two other cells that contain a SUBTOTAL formula (E5 and E8), those cells are not included in the sum to avoid double-counting.

You can use the outline controls to adjust the level of detail shown. Figure 9-29, for example, shows only the summary rows from the subtotaled table. These rows contain the SUBTOTAL formulas. I did column B because it shows only empty cells.

image from book
Figure 9-29: Use the outline controls to hide the detail and display only the summary rows.




Excel 2007 Formulas
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
Year: 2007
Pages: 212

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net