Microsoft Office Tutorials and References
In Depth Information
After you open the dialog box, you move to the worksheet that contains the first cell range
you want to include in your summary. When you select the cells, the 3-D reference for
the range appears in the Consolidate dialog box. Clicking Add stores the reference. You
can then choose the other cell ranges that contain data you want to include in the
summary, or you can remove a range from the calculation by clicking the range and then
Cells that are in the same relative position in the ranges have their contents summarized
together. When you consolidate the ranges, the cell in the upper-left corner of one range
is added to the cell in the upper-left corner of every other range, even if those ranges are in
different areas of the worksheet. After you choose the ranges to be used in your
summary, you can choose the calculation to perform on the data (sum, average, and so on).
When you’re done selecting ranges to use in the calculation, click OK to have Excel
summarize the data on your target worksheet.
Important You can define only one data consolidation summary per workbook.
In this exercise, you’ll define a data consolidation range consisting of ranges from two
other workbooks. You’ll then add the contents of the ranges and show the results in a
SET UP You need the Consolidate_start, JanuaryCalls_start, and FebruaryCalls_start
workbooks located in your Chapter07 practice file folder to complete this exercise.
Open the Consolidate_start, JanuaryCalls_start, and FebruaryCalls_start workbooks,
and save them as Consolidate , JanuaryCalls , and FebruaryCalls , respectively. Then
follow the steps.
1. In the Consolidate workbook, on the Data tab, in the Data Tools group, click
The Consolidate dialog box opens.
2. Click the Collapse Dialog button at the right edge of the Reference field.
The Consolidate dialog box contracts.