Microsoft Office Tutorials and References
In Depth Information
Consolidating multiple sets of data
IMPORTANT The ranges must all have the same shape.
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 clicking Delete.
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 worksheet.