Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

Excel's Data Outline 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 Outline 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 Design Tools Convert To Range. After you insert the subtotals, you can convert the range back to a table by using Insert Tables 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.

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 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 Outline Subtotal. You will see the Subtotal dialog box, as shown in Figure 9-27.

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 Outline Subtotals command is usually easier.

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.

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

Категории