Chapter 28: Linking and Consolidating Worksheets
28
Time to Rethink Your Consolidation Strategy?
If you’re reading this chapter, there’s a good chance that you’re looking for a way to combine data from
multiple sources. The consolidation methods we describe can work, but they may not be the most
efficient way to approach the problem.
A typical budget is actually a summary. It’s usually much easier to work with “normalized” data, which
consists of one row per data item. Then you can use Excel’s most sophisticated tool (a PivotTable) to
consolidate and summarize the information.
For example, a budget for Region 1 might show a value for training expenses for the IT department
for January. Instead of just entering this number into a grid, you gain a lot of flexibility by putting it
into a table with multiple columns that describe the number. For example, this single item can be
represented as a row in a normalized table with these six headings: Region, Department, Expense
Description, Month, Year, and Budget Amount.
If each regional manager submitted his budget information in this format, it would be a simple matter
to combine the data in a single worksheet and then create a PivotTable that displays a summary in just
about any layout you want.
Consolidating worksheets by using formulas
Consolidating with formulas simply involves creating formulas that use references to other
worksheets or other workbooks. Here are the primary advantages to using this method of
consolidation:■ (^) If the values in the source worksheets change, the formulas are updated
automatically.
■ (^) 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 consolidations. 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. You can
then copy this formula to create summary formulas for other cells.
See Chapter 4, “Working with Excel Ranges and Tables,” for more on multisheet selection.
If the consolidation involves other workbooks, you can use external reference formulas to per-
form your consolidation. For example, if you want to add the values in cell B2 from Sheet1 in
two workbooks (named Region1 and Region2), you can use the following formula:
=[Region1.xlsx]Sheet1!B2+[Region2.xlsx]Sheet1!B2