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
Different Subtotaling FunctionsThe following list shows the database statistical functions available in the Subtotal dialog box. You can use these functions to subtotal and evaluate the database:
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 DatabaseYou 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 SubtotalsWhat 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. |