Page 50 - Excel Data Analysis
P. 50
03 537547 Ch02.qxd 3/4/03 11:47 AM Page 36
EXCEL DATA ANALYSIS
CONSOLIDATE DATA
f you have related data values in multiple worksheets, or The options in the Function field in the Consolidate dialog
even workbooks that you want to combine into one box determine how Excel combines the data values when
I location, you can do this using the Consolidate option. you perform the consolidation. By default, Excel selects
When you use this option, Excel merges the data from the Sum, the most common consolidate function, to
specified locations into one central location, combining any consolidate values. The Sum function adds the values in
common data values by whatever method you specify. A each of the merged ranges. For example, if each worksheet
good use of this feature is to combine different workbooks contained monthly sales figures for a salesperson named
containing sales data for the year into one consolidated Simon, Excel totals the sales amounts for each month and
worksheet so that you can analyze all the data. places these totals in the consolidation worksheet.
Before you merge your data values, you must first decide After you determine how Excel will merge your data, you
where you want to place everything. You can make the can decide what range of cells you want to merge. You can
location either a separate worksheet in an existing workbook, manually enter the range in the Reference field, select a
or a worksheet in a separate workbook. You then select the range from an open worksheet, or open another workbook
first cell where you want Excel to place the consolidated and select the range of cells.
values. In the Consolidate dialog box, you specify the ranges
of the workbooks or worksheets that you want to consolidate
and the type of consolidation to perform.
CONSOLIDATE DATA
Sum
SELECT A CONSOLIDATION ¤ Click Data ➪ Consolidate. ■ The Consolidate dialog box ‹ Click the function you
FUNCTION
displays. want to perform.
⁄ Select the top-left cell of
the worksheet on which you
want to consolidate data.
36
