Excel 2010 Bible

Chapter 27: Linking and Consolidating Worksheets

599

Consolidating worksheets by using Paste Special

Another method of consolidating information is to use the Paste Special dialog box. This technique

takes advantage of the fact that the Paste Special dialog box can perform a mathematical operation

when it pastes data from the Clipboard. For example, you can use the Add option to add the cop-

ied data to the selected range. Figure 27.5 shows the Paste Special dialog box.

This method is applicable only when all the worksheets that you’re consolidating are open. The

disadvantage is that the consolidation isn’t dynamic. In other words, it doesn’t generate formulas.

So, if any data that was consolidated changes, the consolidation is no longer accurate.

FIGURE 27.5

Choosing the Add operation in the Paste Special dialog box.

Here’s how to use this method:

1. Copy the data from the first source range.


2. Activate the dependent workbook and select a location for the consolidated data.
   A single cell is sufficient.


3. Display the Paste Special dialog box (choose Home ➪ Clipboard ➪ Paste ➪ Paste
   Special).


4. 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 consoli-

dation location in Step 2 is the same for each paste operation.

Caution
This method is probably the worst way of consolidating data. It can be rather error prone, and the lack of for-
mulas means that you have no way to verify the accuracy of the data. n