United Kingdom
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers. About consolidating data in multiple worksheets
Applies to: Microsoft Office Excel 2003
To summarize and report results from multiple worksheets, you can consolidate data from each worksheet into a master worksheet. The worksheets can be in the same workbook or other workbooks. When you consolidate data, you are assembling data so you can more easily update and aggregate it on a regular or ad hoc basis. For example, if you have a worksheet of expense figures for each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise. To consolidate data, you use the Consolidate command from the Data menu to display the Consolidate dialog box. You can use this dialog box in several ways to consolidate your data:
Consolidate by position when the data in all source areas is arranged in identical order and location; for example, if you have data from a series of worksheets that were created from the same template, you can consolidate the data by position. You can set up the consolidation to update automatically when the source data changes, but you won't be able to change which cells and ranges are included in the consolidation. Or you can update the consolidation manually, allowing you to change the included cells and ranges. Consolidate by category when you want to summarize a set of worksheets that have the same row and column labels but organize the data differently. This method combines data that has matching labels from each worksheet. You can set up the consolidation to update automatically when the source data changes, but you won't be able to change which cells and ranges are included in the consolidation. Alternatively, you can update the consolidation manually, allowing you to change the included cells and ranges.
When you use 3-D references (3-D reference: A reference to a range that spans two or more worksheets in a workbook.) in formulas, there are no restrictions on the layout of the separate ranges of data. You can change the consolidation any way you need to. The consolidation updates automatically when the data in the source ranges changes. Use formulas to combine data In the following example, the formula in cell A2 adds three numbers that are located in different positions on three different worksheets. Add data to a consolidation with 3-D references When all of your source worksheets have the same layout, you can use a range of sheet names in 3-D formulas. To add another worksheet to the consolidation, just move the sheet into the range your formula refers to. You can create a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources including ones external to Excel.) from multiple consolidation ranges. This method is similar to consolidating by category but offers more flexibility to reorganize the categories. For more information see About PivotTable and PivotChart source data. |
Printer-Friendly Version
Show All
Hide All
| Related Web Sites |
| ||||||
