Working with Subtotals

Now that you have a database to work with, you might want to evaluate your database with the Data, Subtotals option. Excel has several database statistical functions that are similar in design and use Excel's standard statistical functions. Hour 5, "Letting Excel Do the Math," discusses the standard statistical function in more detail. Some of the common functions are SUM , AVERAGE , MIN , and MAX .

An example of using the SUM function is to find the total amount the company spends on salaries. You would choose the SUM function for the Salary field in the Subtotal dialog box. Excel would display each salary subtotal in the Salary column with the grand total for salaries at the bottom of the Salary column.

In the next To Do exercise, you use the SUM function to find the grand total for salaries at the company. Before you create any subtotals, you need to change some of the data and enter data in column G for the salary amounts. Be sure to open the My Database workbook you used in Hour 21, "Building an Excel Database."

To Do: Use Subtotals in a Database

1. In Sheet1, click cell E5, point to the fill handle and slowly drag to cell E7. Now there are three employees in the Accounting department. Click cell E8, point to the fill handle, and slowly drag to cell E10. Now there are three employees in the Administration department.

2. Click cell G4, type Salary , and press Enter. Starting in cell G5, type 35000 and press Enter. Type the rest of the data in cells G6 through G10: 40000 , 50000 , 45000 , 32000 , and 51000 . Format the numbers with commas and zero decimal places. Now you have data that you can subtotal and total, as shown in Figure 22.1.

   Figure 22.1. Data you can subtotal and total.

   

3. Select any cell in the list. This step selects a cell within the list you want to subtotal.

4. Choose Data, Subtotals. Excel shows you the Subtotal dialog box. Notice the entire database is selected automatically. From here, you choose the column you want to subtotal and the function you want to use.

5. In the At Each Change In box, choose Dept. This selection tells Excel that you want to subtotal the data for the Dept column.

6. In the Use Function box, choose Sum if necessary. Now Excel will know that you want to find the subtotals and total for the data in the Dept column. In the Add Subtotal To list, notice that Salary is chosen . That option tells Excel to add the subtotals and grand total to the Salary column.

   Figure 22.2 shows you the Subtotal dialog box with the options you have selected so far and the other options you can choose from at the bottom of the dialog box. You can replace the current subtotals, insert a page break between subtotal groups, and place a summary below the data in the database. The Remove All button allows you to remove the subtotals.

   Figure 22.2. The Subtotal dialog box.

   

7. Click OK. Excel places the SUM function results in cell G13. You want to widen column E to accommodate the long entries. Just double-click the column border between columns E and F. Figure 22.3 shows the subtotals in column E and the grand total in cell G13. The result should be 253,000 . Notice the buttons with a minus sign () to the left of the row header numbers on the worksheet. You can use these buttons to hide and show any one or all the subtotals in your database.

   Figure 22.3. The subtotals and grand total in the database.

   

Different Subtotaling Functions

The following list shows the database statistical functions available in the Subtotal dialog box. You can use these functions to subtotal and evaluate the database:

• Sum

• Count

• Average

• Max

• Min

• Product

• Count Nums

• StdDev

• StdDevp

• Var

• Varp

The Use Function list box in the Subtotal dialog box gives you a list of all the functions. Choose one, and Excel subtotals your data in the column you specify in the Subtotal dialog box.

Collapsing and Expanding a Subtotaled Database

You might want to hide some of the subtotal information in your database. You can do so by collapsing the subtotals. To collapse a subtotaled database, click the minus sign () button on the left side of the worksheet. Figure 22.4 shows two collapsed subtotals in the database. A plus sign (+) button appears in each subtotal row you collapsed .

Figure 22.4. Collapsed subtotals in the database.

To display the hidden subtotals in your database, you can expand the subtotals by clicking each plus sign button on the left side of your worksheet. Excel displays the subtotals you expanded. Excel displays a minus sign button in each subtotal row you expanded.

Removing Subtotals

What if you no longer want the subtotals in your database? You can easily dispose of them by removing them from the worksheet. To do so, click any cell in the database, and choose Data, Subtotal. In the Subtotal dialog box, click the Remove All button. The dialog box closes and Excel removes the subtotals from your database. Go ahead and remove the subtotals from your database.