Part IV: Using Advanced Excel Features
600
Consolidating worksheets by using
the Consolidate command
For the ultimate in data consolidation, use the Consolidate dialog box. This method is very flexi-
ble, and in some cases, it even works if the source worksheets aren’t laid out identically. This tech-
nique can create consolidations that are static (no link formulas) or dynamic (with link formulas).
The Data Consolidate feature supports the following methods of consolidation:l By position: This method is accurate only if the worksheets are laid out identically.l (^) By category: Excel uses row and column labels to match data in the source worksheets.
Use this option if the data is laid out differently in the source worksheets or if some source
worksheets are missing rows or columns.
Figure 27.6 shows the Consolidate dialog box, which appears when you choose Data ➪ Data
Tools ➪ Consolidate. Following is a description of the controls in this dialog box:
FIGURE 27.6
The Consolidate dialog box enables you to specify ranges to consolidate.
l (^) Function drop-down list: Specify the type of consolidation. Sum is the most commonly
used consolidation function, but you also can select from ten other options.
l (^) Reference text box: Specify a range from a source file that you want to consolidate. You
can enter the range reference manually or use any standard pointing technique (if the
workbook is open). Named ranges are also acceptable. After you enter the range in this
box, click Add to add it to the All References list. If you consolidate by position, don’t
include labels in the range. If you consolidate by category, do include labels in the range.
l All References list box: Contains the list of references that you have added with the
Add button.