Part IV: Managing and Analyzing Data
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 reenter the references. Just display the Consolidate dialog box, verify that the ranges
are correct, and then click OK.When Excel has an outline applied, like after you consolidate, rerunning the consolidation can be unpredictable. The
sure-fire way to rerun a consolidation is to clear the outline, delete the cell contents, display the consolidation dia-
log, and click OK.Learning more about consolidation
Excel is flexible regarding the sources that you can consolidate. You can consolidate data
from the following:■ (^) Open workbooks.
■ (^) Closed workbooks. You need to enter the reference manually, but you can use the
Browse button to get the filename part of the reference.
■ (^) 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, how-
ever, 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 of the source ranges.
If you don’t select the Create Links to Source Data check box, Excel generates a static con-
solidation. (It doesn’t create formulas.) Therefore, if the data on any of the source work-
sheets 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 outline
that you can manipulate by using the techniques described in Chapter 27.