Page 53 - Excel Data Analysis

03 537547 Ch02.qxd 3/4/03 11:47 AM Page 39

ORGANIZE WORKSHEET DATA 2

When you consolidate, Excel simply copies or combines
the data in the specified ranges into the consolidation
location. If you modify the data after you perform the
consolidation, Excel does not automatically update to
reflect your changes. To have it do so, you must click the
Create links to source data option ( changes to ) in
the Consolidate dialog box. This option places formulas
in each cell of the consolidation worksheet. These
formulas link directly back to the consolidated data,
allowing Excel to automatically update after you make
modifications to the original data.

If you choose to use this option, you must ensure that
Excel can always find the worksheets containing the
consolidation data. You do this by placing the original
worksheets in the same locations. Each time you open
the worksheet, Excel attempts to locate the
consolidation data so that it can update the worksheet.
Excel opens the original worksheets and updates the
data in the consolidation worksheet. If Excel cannot
locate a consolidation range, the worksheet will not
reflect all the data you modified.

'Sales 3'!$A$2:$M$15

■ The All references box ‚ Click the location of your ■ Excel consolidates the data
displays the ranges to label. values in the specified ranges.
consolidate.
— Click OK.

39