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

Overview

Suppose your company sells products in several regions of the United States. Each region keeps records of the number of units of each product sold during the months of January, February, and March. You might have a question like the following: Is there an easy way to create a “master workbook” that always combines each region’s sales and gives a tally of the total amount of each product sold in the U.S. during each month?

A business analyst often receives worksheets that tally the same information (such as monthly product sales) from different affiliates or regions. To determine the company’s overall profitability, the analyst usually needs to combine or consolidate this information into a single Microsoft Office Excel 2007 workbook. PivotTables built from multiple consolidation ranges can be used to accomplish this goal, but the little known Consolidate command (on the Data tab of the Ribbon) is another way to accomplish this goal. With Consolidate, you can ensure that changes in the individual worksheets will be automatically reflected in the consolidated worksheet.

• Is there an easy way to create a “master workbook” that always combines each region’s sales and gives a tally of the total amount of each product sold in the U.S. during each month?

• The file East.xlsx (shown in Figure 41-1 on the next page) displays monthly unit sales of Products A–H in the eastern U.S. during January, February, and March. Similarly, the file West.xlsx (shown in Figure 41-2 on the next page) displays monthly unit sales of Products A–H in the western U.S. from January through March. We want to create a consolidated worksheet that tabulates each product’s total sales by month.

  Figure 41-1: East region sales during January–March

  Figure 41-2: West region sales during January–March

• Before using the Consolidate command, it’s helpful to see both spreadsheets together on the same screen. To do this, open both workbooks, click Arrange All in the Window group on the View tab, and select the Tiled option. Your screen should look like Figure 41-3.

  Figure 41-3: East and West sales arranged on the same screen

• Now open a blank worksheet in a new workbook and click Arrange All and Tiled again. In the blank worksheet, click Consolidate in the Data Tools group on the Data tab, and you’ll see the Consolidate dialog box, shown in Figure 41-4.

  Figure 41-4: Consolidate dialog box

• To consolidate the data from the East and West regions into our new, blank worksheet, we enter the ranges we want to consolidate in the Reference box of the Consolidate dialog box, clicking Add after selecting each range. By checking the Top Row and Left Column boxes in the Use Labels In area, we ensure that Excel will consolidate the selected ranges by looking at labels in the top row and left column of the ranges we select. The Create Links To Source Data option enables changes in our selected ranges to be reflected in the consolidated worksheet. We select Sum in the Function box because we want Excel to add up the total sales of each product by month. Selecting Count, for instance, would count the number of transactions for each product during each month; selecting Max would compute the largest sales transaction for each product during each month. The Consolidate dialog box should be filled out as shown in Figure 41-5 on the next page.

  Figure 41-5: Completed Consolidate dialog box

• After clicking OK, our new worksheet looks like the one shown in Figure 41-6 on the next page. See file Eastandwestconsolidated.xlsx.

• Figure 41-6: Total sales after consolidation

  We find, for example, that 1317 units of Product A were sold in February, 597 units of Product F were sold in January, and so on.

• Now go to the cell C2 of East.xlsx and change the February Product A sales from 263 to 363. Note that in the consolidated worksheet, our entry for February Product A sales has also increased by 100 (from 1317 to 1417). This change occurs because we checked the Create Links To Source Data box in the Consolidate dialog box. (By the way, if you click the 2 right below the workbook name in the consolidated worksheet, you’ll see how Excel grouped the data to perform the consolidation.) Our final result is contained in the file Eastandwestconsolidated.xlsx.

• If you frequently download new data to your source workbooks (in this case, East.xlsx and West.xlsx), it’s a good idea to name the ranges including your data as a table. Then new data will automatically be included in the consolidation.