Excel 2010 Bible

Part IV: Using Advanced Excel Features

598

If the worksheets bear little or no resemblance to each other, your best bet may be to edit the

sheets so that they correspond to one another. Better yet, return the files to the department heads

and ask that they submit them using a standardized format.

You can use any of the following techniques to consolidate information from multiple workbooks:

l (^) Use external reference formulas.
l Copy the data and choose Home ➪ Clipboard ➪ Paste ➪ Paste Link (N).
l (^) Use the Consolidate dialog box, displayed by choosing Data ➪ Data Tools ➪ Consolidate.

Consolidating worksheets by using formulas

Consolidating with formulas simply involves creating formulas that use references to other work-

sheets or other workbooks. The primary advantages to using this method of consolidation are

l Dynamic updating: If the values in the source worksheets change, the formulas are

updated automatically.

l Open versus closed: The source workbooks don’t need to be open when you create the

consolidation formulas.

If you’re consolidating the worksheets in the same workbook and all the worksheets are laid out

identically, the consolidation task is simple. You can just use standard formulas to create the con-

solidations. For example, to compute the total for cell A1 in worksheets named Sheet2 through

Sheet10, enter the following formula:

=SUM(Sheet2:Sheet10!A1)

You can enter this formula manually or use the multisheet selection technique discussed in

Chapter 4. You can then copy this formula to create summary formulas for other cells.

If the consolidation involves other workbooks, you can use external reference formulas to perform

your consolidation. For example, if you want to add the values in cell A1 from Sheet1 in two

workbooks (named Region1 and Region2), you can use the following formula:

=[Region1.xlsx]Sheet1!B2+[Region2.xlsx]Sheet1!B2

You can include any number of external references in this formula, up to the 8,000-character limit

for a formula. However, if you use many external references, such a formula can be quite lengthy

and confusing if you need to edit it.

If the worksheets that you’re consolidating aren’t laid out the same, you can still use formulas, but

you need to ensure that each formula refers to the correct cell.