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.
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.
CLOSE the Consolidate, January Calls, and February Calls workbooks.