MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

Joolas is a small company that manufactures makeup. For each transaction, they track the name of the salesperson, the location of the transaction, the product sold, the units sold, and the revenue. They want answers to the following questions:

• Is there an easy way to set up a worksheet to calculate total revenue and units sold by region?

• Can I also obtain a breakdown by salesperson of sales in each region?

We know that PivotTables can be used to “slice and dice” data in Microsoft Office Excel 2007. Often, however, we’d like an easier way to summarize a list or a database within a list. In a sales database, for example, we might want to create a summary of sales revenue by region, a summary of sales revenue by product, and a summary of sales revenue by salesperson. If we sort a list by the column in which specific data is listed, the Subtotal command allows us to create a subtotal in a list on the basis of the values in the column. For example, if we sort our makeup database by location, we can calculate total revenue and units sold for each region and place the totals just below the last row for that region. As another example, after sorting our database by product, we can use the Subtotal command to calculate total revenue and units sold for each product and display the totals below the row in which the product changes. In the next section, we’ll look at some specific examples.

• Is there an easy way to set up a worksheet to calculate total revenue and units sold by region?

• Our data is in the file Makeupsubtotals.xlsx. In Figure 42-1 on the next page, you can see a subset of the data as it appears after sorting the list by the Location column.

  Figure 42-1: After sorting a list by the values in a specific column, you can easily create subtotals for that data.

• To calculate revenue and units sold by region, place the cursor anywhere in the database and then click Subtotal in the Outline group on the Data tab of the Ribbon. In the Subtotal dialog box, we fill in the values as shown in Figure 42-2 on the next page.

  Figure 42-2: Subtotal dialog box

• By selecting Location from the At Each Change In list, we ensure that subtotals are created at each point in which the value in the Location column changes. This corresponds to the different regions. Selecting Sum from the Use Function box tells Excel to total the units and dollars for each different region. By checking the Units and Dollars boxes in the Add Subtotal To area, we indicate that subtotals should be created on the basis of the values in these columns. The Replace Current Subtotals option causes Excel to remove any previously computed subtotals. Because we haven’t created any subtotals, it doesn’t matter whether or not this option is checked for this example. If the Page Break Between Groups box is checked, Excel inserts a page break after each subtotal. Checking the Summary Below Data box causes Excel to place subtotals below the data. If this box is not checked, the subtotals are created above the data used for the computation. Clicking Remove All removes subtotals from the list.

• A sample of our subtotals results is shown in Figure 42-3. We find that 18,818 units were sold in the East region, earning revenue of $57,372.09.

  Figure 42-3: Subtotals for each region

• Notice that in the left corner of the screen below the Name Box, buttons with the numbers 1, 2, and 3 appear. Clicking the largest number (in this case, 3) yields the data and subtotals. If we click the 2 button, we see just the subtotals by region, as shown in Figure 42-4. Clicking the 1 button yields the Grand Total, as shown in Figure 42-5. In short, clicking a lower number reduces the level of detail shown.

  Figure 42-4: When you create subtotals, Excel adds buttons that, when clicked, display only subtotals or both subtotals and details.

  Figure 42-5: Displaying the overall total without any detail

• Can I also obtain a breakdown by salesperson of sales in each region?

• If you wish, you can nest subtotals. In other words, you can obtain a breakdown of sales by each salesperson in each region, or you can even get a breakdown of how much each salesperson sold of each product in each region. See the file Nestedsubtotals.xlsx. To demonstrate the creation of nested subtotals, we will create a breakdown of sales by each salesperson in each region.

• To begin, you must sort your data first by Location and then by Name. This will give a breakdown for each salesperson of units sold and revenue within each region. If we sorted first by Name and then by Location, we would get a breakdown of units sold and revenue for each salesperson by region. After sorting the data, we proceed as before and create the subtotals by region. Then we click Subtotal again and fill in the dialog box as shown in Figure 42-6.

  Figure 42-6: Creating nested subtotals

• We now want a breakdown by Name. Clearing the Replace Current Subtotals box ensures that we will not replace our regional breakdown. We now see the breakdown of sales by each salesperson in each region as shown in Figure 42-7.

  Figure 42-7: Nested subtotals