Page 285 - Excel 2013 All-in-One For Dummies

Consolidating Worksheets 267
by category, you include these identifying labels as part of the source areas. Unlike when consolidating by position, Excel copies the row labels and/or column labels when you specify that they should be used in the consolidation.
When consolidating spreadsheet data by category, you must specify whether to use the top row of column labels and/or the left column of row labels in determining which data to consolidate. To use the top row of column labels, you select the Top Row check box in the Use Labels In sec- tion of the Consolidate dialog box. To use the left column of row labels, you select the Left Column check box in this area. Then, after you’ve specified all the source areas (including the cells that contain these column and row labels), you perform the consolidation in the destination area by clicking the Consolidate dialog box’s OK button.
Linking consolidated data
Excel allows you to link the data in the source areas to the destination area during a consolidation. That way, any changes that you make to the values in the source area are automatically updated in the destination area of the consolidation worksheet. To create links between the source worksheets and the destination worksheet, you simply select the Create Links to Source Data check box in the Consolidate dialog box to put a check mark in it when defining the settings for the upcoming consolidation.
When you perform a consolidation with linking, Excel creates the links between the source areas and the destination area by outlining the destina- tion area. (See “Outlining worksheets” earlier in this chapter for details.) Each outline level created in the destination area holds rows or columns that contain the linking formulas to the consolidated data.
Figure 4-15 shows an outline created during consolidation after expanding only the level of the outline showing the consolidation of the Rock music
CD sales. Here, you can see that, during consolidation, Excel created three detail rows for each of the three years of sales (2008, 2009, and 2010) used in the linked consolidation. These rows contain the external reference formulas that link to the source data. For example, the formula in cell B4 contains the following formula:
=’[CG Media - 2010 Sales.xlsx]Sales 2010’!$B$4
Book II Chapter 4
Managing Worksheets