Page 52 - Excel Data Analysis
P. 52

03 537547 Ch02.qxd  3/4/03  11:47 AM  Page 38







                     EXCEL DATA ANALYSIS




                  CONSOLIDATE DATA (CONTINUED)



                     f you manually enter a range of data, you must include  Reference field of the Consolidation dialog box. When you
                     the worksheet name and the cells to merge. For   accept the range of cells, Excel adds them to the All
                  I example, if you want to merge cells A1 through A15  References box. You must define the range for each of the
                  from Sheet1, you type:                              worksheets that you want to combine.
                                                                      If you do not have the worksheet containing the data values
                  'Sheet1'!$A$1:$A$15
                                                                      open, you can locate the appropriate workbook via the
                  When you manually specify a worksheet name, you must  Browse button. Excel opens the workbook so you can select
                  type the name of the worksheet between single quotes. Also,  the desired range of cells.
                  you use an exclamation mark to separate the worksheet
                  name reference from the range of cells. Therefore, in the  Excel needs to know what labels to use for the combined
                  sample range, 'Sheet1'! indicates the worksheet containing  data values. Excel matches the data in the specified
                  the range of cells to consolidate. You define the range of cells  worksheets based upon matching labels in the top row, left
                  by selecting first and last cells in the range, separating the cell  column, or both. You need to select at least one of the
                  references with a colon. See Chapter 1 for more information  location options.
                  on specifying a range of cells.
                                                                      Keep in mind that consolidating data does not affect the
                  To specify a range in an open workbook, you select the  values contained in the original worksheets.
                  appropriate range of cells until they correctly appear in the



                   CONSOLIDATE DATA (CONTINUED)


























                  Á Select the range of cells   ■ The range of the selected   ° Click Add to add the range  · Repeat steps 5 to 8 to
                  containing the data to    cells displays in the    to the All References list box.  select the other ranges.
                  consolidate.              Reference field.
                  ■ Alternatively, you can type   ‡ Click the Restore Dialog
                  the range in the Reference   button.
                  Field.
                   38
   47   48   49   50   51   52   53   54   55   56   57