Consolidate by wuxiangyu

VIEWS: 26 PAGES: 23

									      Consolidate
Consolidate Multiple Worksheets
   to a Single Sheet in Excel
       What are Consolidated
          Worksheets ?
If you have a group of tables (or lists) it is possible
to COMBINE (or consolidate) all this data into one
table (or list).
  This can be done using the
     Data > Consolidate dialog box.
  You can use Data > Consolidate to combine
corresponding values from different sheets onto a
summary sheet.
  You can consolidate up to 255 source areas.
  Your data can be consolidated from several
different source, either on the same worksheet, on
different worksheets within the same workbook or
even in different workbooks.
   Data > Consolidate Menu
Function - One of the following 11
functions: Average, Count, Count
Nums, Max, Min, Product, Stddev,
Stddevp, Sum, Var, Varp.
  Reference - Specifies the range of
cells you select as a source area to
consolidate with other source areas
listed in the All references box.
  All references - Lists the source
area references selected for the
consolidation.
  Top row - This only used when you
are consolidating by category.
  Left column - This is only used
when you are consolidating by
category.
  Create links to source data - This
allows you to import all the detailed
data of the source zones, but a
generated outline will hide them.
Without a link Excel will consolidate
only the final values.
         Main Scenarios
How to Consolidate Data by Position

How to Consolidate Data by Category

How to Select Source Areas for a
Consolidation Table
  Consolidation by position
When the data in the source areas is
arranged in the same order and uses the
same labels. Use this method to
consolidate data from a series of
worksheets, such as departmental budget
worksheets that have been created from
the same template
         How to Consolidate
     Data by Position: Steps 1&2
  To consolidate data by position, follow these steps:
  Step 1: Type the following data on Sheet1:
A1:Letter B1:Code Number C1:More Number
A2:A      B2:50                C2:62
A3:H      B3:99                C3:11
A4:G      B4:86                C4:68
A5:K      B5:18                C5:31
A6:K      B6:67                C6: 9


  Step 2: Type the following data on Sheet2:
A1:Letter B1:Code Number C1:More Number
A2:M      B2:38                C2:17
A3:H      B3:53                C3:25
A4:G      B4:48                C4:18
A5:C      B5:59                C5:53
A6:K      B6:78                C6:97
      How to Consolidate
  Data by Position – Steps 3-9
Step 3: Click the upper-left cell of the destination area for the
consolidated data. In this example, click cell A1 on Sheet3.
Step 4: On the Data menu, click Consolidate.
Step 5: In the Function list, select the summary function that you
want Microsoft Excel to use to consolidate the data. In this example,
use Sum.
Step 6: In the Reference box, type each source area you want to
consolidate, and then click Add. In this example, type the first area,
Sheet1!$A$1:$C$6, and then click Add. Type the second area,
Sheet2!$A$1:$C$6 and then click Add.
Step 7: Repeat Step 6 for all of the source areas that you want to
consolidate.
Step 8: Under Use labels in, select the Top row check box and the
Left column check box (in this example, there are labels both on
the first row and also in the left column).
Step 9: Click OK.
   Data > Consolidate Menu
Function - One of the following 11
functions: Average, Count, Count
Nums, Max, Min, Product, Stddev,
Stddevp, Sum, Var, Varp.
  Reference - Specifies the range of
cells you select as a source area to
consolidate with other source areas
listed in the All references box.
  All references - Lists the source
area references selected for the
consolidation.
  Top row - This only used when you
are consolidating by category.
  Left column - This is only used
when you are consolidating by
category.
  Create links to source data - This
allows you to import all the detailed
data of the source zones, but a
generated outline will hide them.
Without a link Excel will consolidate
only the final values.
  Consolidation by category
When the data in the source areas is not
arranged in the same order but uses the
same labels. Use this method to
consolidate data from a series of
worksheets that have different layouts but
have the same data labels.
NOTE:
Consolidating data by category is similar to creating a PivotTable.
With a PivotTable, however, you can easily reorganize the categories.
If you want a more flexible consolidation by category, consider
creating a PivotTable.
               How to Consolidate
                Data by Category
  To consolidate data by category, follow these steps:
Step1: Type the following data on Sheet1:
      A2:A       B2:50        C2:62
      A3:H       B3:99        C3:11
      A4:G       B4:86        C4:68
      A5:K       B5:18        C5:31
      A6:K       B6:67        C6: 9

      A12:M      B12:38      C12:17
      A13:H      B13:53      C13:25
      A14:G      B14:48      C14:18
      A15:C      B15:59      C15:53
      A16:K      B16:78      C16:97
          How to Consolidate
       Data by Category-Steps 2-8
   Step 2: Click the upper-left cell of the destination area for the consolidated
   data, which would be cell A1 on Sheet2.
   Step 3: On the Data menu, click Consolidate
   Step 4: In the Function list, select the summary function that you want
   Microsoft Excel to use to consolidate the data. In this example, use Sum.
   Step 5: In the Reference box, type each source area you want to consolidate
   and then click Add. Type the first area, Sheet1!$A$2:$C$6 and then click
   Add. Type the second area, Sheet1!$A$12:$C$16 and then click Add.
   Step 6: Repeat Step 5 for all source areas you want to consolidate.
   Step 7: Under Use labels in, click to select the Left column check box (in
   this example, there are labels in the left column).
   Step 8: Click OK

NOTE: If you want Microsoft Excel to update your consolidation table automatically when
  the source data changes, select the Create links to source data check box. You
  cannot create links when source and destination areas are on the same sheet.
         Consolidating Ranges
           More Examples
Lets assume that all our data is
on the same worksheet and the
data is arranged in simple
tables, one below the other.
 First of all create a table below
the other tables that will contain
the consolidated data, in this
case table "B14:F17".
 Select the first cell in this
range, i.e. "C15" and then select
(Data > Consolidate) to display
the Consolidate dialog box.
 Altenatively you could
highlight the whole range of
cells "C15:F17", although Excel
will populate the whole range
automatically.
              Regions Example
For this example we are going to use the Sum consolidation function
which is the default.
 In the Reference box select the first cell range you want to use in
the consolidation, in this case "C3:F5".
 Press the Add button to add this range to the "All references" list.
 In the Reference box select the second cell range, in this case
"C9:F11" and press the Add button.




Press OK to consolidate the selected ranges and to create the
consolidated table of data.
  When you are linking ranges on the same worksheet you cannot
link the data, although the consolidated table can be very easily
updated by selecting (Data > Consolidate) and then pressing OK.
    Consolidating Worksheets
     in the same Workbook
Lets assume that the have the same data as above but this time
the data is displayed on separate worksheets within the same
workbook.
 There are 2 worksheets in this workbook, one called 2004 and
one called 2005.
 First lets create another worksheet in this workbook which will
contain the consolidated data.




• Lets also assume that every worksheet lots identical. The cell
ranges you want to consolidate need to be in exactly the same
cells on every worksheet.
  First of all create a table on the new "totals" worksheet that
looks identical the other tables on all the other worksheets.
  You can select the whole range of cells to ensure you get the
whole range consolidated it is safer to just select the first cell.
    “Totals" worksheet




In this example we are going to use the
Average consolidation function which
can be changed in the Function drop-
down list.
Average consolidation function
In the References box select the "2005" tab and select the
range you want to use in the consolidation, in this case
"C3:F5".
 Press the Add button to add this range to the "All
references list".
 In the References box select the "2004" tab. You will notice
that the corresponding range is automatically highlighted so
just press Add.
           Things to Remember

The method can be used to consolidate up to 255 worksheets into a single
worksheet. The number of worksheets that you can have in a workbook is only
restricted by the memory on the PC (i.e. how much RAM the PC has).

You can also use the Pivot Table Reports to consolidate data, for more
information please refer to the Pivit Tables HELP

To scroll without changing the current selection press Scroll Lock and use the
arrow keys.

You can scroll a worksheet without changing the active cell. Press Scroll Lock.

You can also have your changes on one worksheet reflected on other sheets by
making your changes to one worksheet, holding down Ctrl and selecting the
other worksheets.
     Press (Edit > Fill > Across Worksheets).
             Useful Links
Joseph Rubin’s Excel Assistance at
http://www.exceltip.com/se/27.html
MicroSoft at
http://support.microsoft.com/?kbid=214270#
appliesto
BetterSolutions at
http://www.bettersolutions.com/excel/
Excel User at
http://www.exceluser.com/help/chart/long001
.htm
               Q&A
Consolidate and Business Graphics
…
     Excel -- Pivot Tables –
        Grouping Data
In a Pivot Table, you can group the items
in a Row or Column field.
For example, items in a date field can be
grouped by month, and items in a number
field can be grouped by tens.
     Pivot - Grouping Dates
To group the items in a
Date field:
Right-click the Date field
button.
Choose Group and Show
Detail | Group
       Pivot - Grouping Dates 2
     In the Grouping dialog box,
     select one or more options
     from the 'By' list.
     To limit the dates that are
     grouped, you can set a Start
     and End date, by typing the
     dates in the 'Starting at' and
     'Ending at' boxes
     Click OK
If you try to group a date or field,
     you may see an error
     message that says, "Cannot
     group that selection."
This problem usually occurs when
     the field contains records with
     a blank date/number field, or
     text in a date/number field
                 Example
PivotSales.xls

								
To top