Consolidating Multiple Sets of Data into a Single Workbook

When you create a series of worksheets that contain similar data, perhaps by using a template, you build a consistent set of workbooks in which data is stored in a predictable place. For example, the workbook template in the following graphic uses cell C5 to record the number of calls received from 9:00 a.m. to 10:00 a.m. regarding phone calls handled by the Northeast distribution center.

Using links to bring data from one worksheet to another gives you a great deal of power to combine data from several sources in a single spot. For example, you can create a worksheet that lists the total revenue just for certain months of a year, use links to draw the values from the worksheets in which the sales were recorded, and then create a formula to perform calculations on the data. However, for large worksheets with hundreds of cells filled with data, creating links from every cell to cells on another worksheet is time-consuming. Also, to calculate a sum or an average for the data, you would need to include links to cells in every workbook.

Fortunately, there is an easier way to combine data from multiple worksheets in a single worksheet. This process, called data consolidation, enables you to define ranges of cells from multiple worksheets and have Excel 2007 summarize the data. You define these ranges in the Consolidate dialog box.

After you open the dialog box, you move to the worksheet with the cells in the first range you want to include in your summary. When you select the cells, the 3-D reference for the cell range appears in the Consolidate dialog box.

Clicking Add stores the reference, whereas clicking Delete removes a range from the calculation. You can then choose the other cell ranges with data to include in the summary. Cells that are in the same relative position in the ranges have their contents summarized together. So the cell in the upper-left corner of one range is added to the cell in the upper-left corner of another range, even if those ranges are in different areas of the worksheet. After you choose the ranges to be used in your summary, you can choose the calculation to perform on the data (sum, average, and so on). When you're done selecting ranges to use in the calculation, click OK to have Excel 2007 summarize the data on your target worksheet.

Important

You can define only one data consolidation summary per workbook.

In this exercise, you'll define a series of ranges from two workbooks to be included in a data consolidation calculation. You then add the contents of the ranges and show the results in a worksheet.

USE the Consolidate, January Calls, and February Calls workbooks from the My Documents\Microsoft Press\Excel SBS\MultipleFiles folder.

OPEN the Consolidate, January Calls, and February Calls workbooks.

1.	In the Consolidate workbook, on the Data tab, in the Data Tools group, click Consolidate. The Consolidate dialog box appears.
2.	Click the Contract Dialog button at the right edge of the Reference field. The Consolidate dialog box contracts.
3.	On the View tab, in the Window group, click Switch Windows and then click January Calls. The January Calls workbook appears.
4.	Select the cell range C5:O13 and then click the Expand Dialog control. The Consolidate dialog box reappears.
5.	Click Add. The range '[January Calls.xlsx]January'!$C$5:$O$13 appears in the All references pane.
6.	Click the Contract Dialog button at the right edge of the Reference field. The Consolidate dialog box contracts.
7.	On the View tab, in the Window group, click Switch Windows and then click February Calls. The February Calls workbook appears.
8.	Select the cell range C5:O13 and then click the Expand Dialog control. The Consolidate dialog box reappears.
9.	Click Add. The range '[February Calls.xlsx]February'!$C$5:$O$13 appears in the All references pane.
10.	Click OK. Office Excel 2007 consolidates the January Calls and February Calls workbook data into the range C5:O13 in the Consolidate workbook.

CLOSE the Consolidate, January Calls, and February Calls workbooks.