Microsoft Office Tutorials and References
In Depth Information
Consolidating by position
has the correct locations at which to find each workbook file. (This should not be a
problem unless you moved them since you last opened them in Excel.) You must save
all supporting workbooks before you begin consolidation.
You can also use PivotTable reports to consolidate worksheets. For information, see Chapter
23, “Analyzing data with PivotTable reports.”
Consolidating by position
When you consolidate by position, Excel applies the consolidation function (Sum,
Average, or whatever else you select) to the same cell references in each supporting worksheet.
By position is the simplest way to consolidate, but your supporting worksheets must have
exactly the same layout.
Figure 8-77 shows a simple example of a workbook containing a master worksheet—Con-
solidated—that matches the layout of 12 supporting monthly worksheets. These
worksheets can be consolidated by position because each contains identically structured data.
You’ll find the Northwind Brass Sales.xlsx file with the other examples on the companion
website.
To consolidate the monthly worksheets in Figure 8-77 into the worksheet named
Consolidated, follow these steps:
Open the consolidation worksheet, and select the block of cells that will receive the
consolidated data. In Figure 8-77, the destination area is the range B5:I10.
1.
Figure 8-77 All the worksheets in this workbook are identical, which is necessary when
you are consolidating by position.
Click Data, Consolidate.
2.
 
Search JabSto ::




Custom Search