Part IV: Using Advanced Excel Features
604
Refreshing a consolidation
When you choose the option to create formulas, the external references in the consolidation work-
book are created only for data that exists at the time of the consolidation. Therefore, if new rows
are added to any of the original workbooks, the consolidation must be re-done. Fortunately, the
consolidation parameters are stored with the workbook, so it’s a simple matter to re-run the con-
solidation if necessary. That’s why specifying complete columns and including extra columns (in
Step 3 in the preceding section) is a good idea.Excel remembers the references that you entered in the Consolidate dialog box and saves them
with the workbook. That way, if you want to refresh a consolidation, you won’t have to re-enter
the references. Just display the Consolidate dialog box, verify that the ranges are correct, and then
click OK.More about consolidation
Excel is very flexible regarding the sources that you can consolidate. You can consolidate data from
the following:l Open workbooksl (^) Closed workbooks. You need to enter the reference manually, but you can use the
Browse button to get the filename part of the reference.
l (^) The same workbook in which you’re creating the consolidation
And, of course, you can mix and match any of the preceding choices in a single consolidation.
If you perform the consolidation by matching labels, be aware that the matches must be exact. For
example, Jan doesn’t match January. The matching is not case sensitive, however, so April does
match APRIL. In addition, the labels can be in any order, and they don’t need to be in the same
order in all the source ranges.
If you don’t select the Create Links to Source Data check box, Excel generates a static consolida-
tion. (It doesn’t create formulas.) Therefore, if the data on any of the source worksheets changes,
the consolidation won’t update automatically. To update the summary information, you need to
choose Data ➪ Data Tools ➪ Consolidate again.
If you do select the Create Links to Source Data check box, Excel creates a standard worksheet out-
line that you can manipulate by using the techniques described in Chapter 26.