Microsoft Office Tutorials and References
In Depth Information
Ungrouping and grouping columns and rows
Figure 8-76 The default function in the Consolidate dialog box is Sum.
For example, if you have data for each month in separate worksheets or financial data for
several divisions in separate workbooks, you can use the Consolidate command to create
a master worksheet that comprises the totals for the corresponding items in each location.
You can use the Consolidate command in a number of ways:
Link the consolidated data to the supporting data so that subsequent changes in the
supporting worksheets are reflected in the consolidation worksheet.
Consolidate the source data on an ad hoc basis, without creating a link.
Consolidate by position, where Excel gathers information from the same cell location
in each supporting worksheet.
Consolidate by category, where Excel uses column or row labels as the basis for
associating worksheets. Consolidating by category gives you more flexibility in the
way you set up your supporting worksheets. For example, if your January column is
column B in one worksheet and column D in another, you can still gather the correct
January numbers when you consolidate by category.
Use any of the functions listed in the Function list in the Consolidate dialog box,
including Count (which corresponds to the COUNTA function), Average, Max, Min,
Product, Count Nums (which corresponds to the COUNT function), StdDev, StdDevp,
Var, and Varp. As shown in Figure 8-76, the default function is Sum.
For more information about functions, see Chapter 13 and Chapter 14.
Consolidate worksheets in workbooks that are currently open or in workbooks that
are stored on disk. The workbook containing the worksheet that receives the
consolidated data must be open, but supporting workbooks can be closed—provided Excel