Microsoft Office Tutorials and References
In Depth Information
Consolidating by category
5. Click OK, and Excel ills out the Consolidated worksheet, as shown in Figure 8-81.
Figure 8-81 The Consolidate command created a separate line item in the consolidation
worksheet for each unique item in the source worksheets.
It’s important that your categories—in our example, the names of salespeople—are
spelled identically on each supporting worksheet. Otherwise, Excel creates a separate
line and consolidation for each spelling variation.
Creating links to the source worksheets
The previous examples consolidated numbers with the Sum function, resulting in a range
of consolidated constants. Subsequent changes to the source worksheets do not affect the
consolidation worksheet until you repeat the consolidation.
You can also use the Consolidate command to create links between the consolidation and
source worksheets. To do so, select the Create Links To Source Data check box in the
Consolidate dialog box and then consolidate using the same techniques. When you consolidate
with links, Excel actually creates an outline in the consolidation worksheet, as shown in
Figure 8-82. Each source item is linked separately to the consolidation worksheet, and Excel
creates the appropriate summary items. Excel creates additional columns and rows as
necessary for each category—one for each unique entry in each worksheet, as shown in rows
27 to 33. Figure 8-82 also shows, in the formula bar, the linking formula for the December
units figure in cell C29.