Chapter 28: Linking and Consolidating Worksheets
28
- Choose Home ➪ Clipboard ➪ Paste ➪ Paste Special. The Paste Special dialog box
appears. - Choose the Values option and the Add operation and then click OK.
Repeat these steps for each source range that you want to consolidate. Make sure that the
consolidation location in step 2 is the same for each paste operation.This method is probably the worst way of consolidating data. It can be rather error prone, and the lack of formulas
means that there is no “trail.” If an error is discovered, it may be difficult or impossible to determine the source of
the error.Consolidating worksheets by using the Consolidate dialog box
For the ultimate in data consolidation, use the Consolidate dialog box. This method is flex-
ible, and in some cases it even works if the source worksheets aren’t laid out identically.
This technique can create consolidations that are static (no link formulas) or dynamic
(with link formulas). The data consolidation feature supports the following methods of
consolidation:By position This method is accurate only if the worksheets are laid out identically.
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 28.6 shows the Consolidate dialog box, which appears when you choose Data ➪ Data
Tools ➪ Consolidate.FIGURE 28.6
The Consolidate dialog box enables you to specify ranges to consolidate.