XL: How to Consolidate Multiple Worksheets to a Single Sheet

ID: Q141577

The information in this article applies to:

Microsoft Excel for Windows, version 5.0
Microsoft Excel for the Macintosh, version 5.0
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

SUMMARY

You can summarize data from one or more source areas by consolidating it and creating a consolidation table. These source areas can be on the same worksheet as the consolidation table, on different sheets in the same workbook, or in different workbooks. When you consolidate the source data, you apply a summary function, such as the SUM() function, to create the summary data.

There are two ways to consolidate data: by category or by position.

   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.

   Consolidating 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, you should consider creating a PivotTable.

MORE INFORMATION

To Consolidate Data By Position

 1. Enter 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

 2. Enter 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

 3. Click the upper-left cell of the destination area for the consolidated
    data, in this example, click cell A1 on Sheet3.

 4. On the Data menu, click Consolidate.

 5. In the Function box, select the summary function you want Microsoft
    Excel to use to consolidate the data. In this example, use Sum.

 6. In the Reference box, enter the source areas you want to consolidate.
    In this example, the first area is Sheet1!$A$1:$C$6 and the second
    area is Sheet2!$A$1:$C$6.

 7. Click Add.

 8. Repeat steps 6 and 7 for all source areas you want to consolidate.

 9. Under Use labels in, click Top row and Left column (in this
    example there are labels both on the first row and also in the left
    column).

10. Click OK.

To Consolidate Data by Category

1. Enter 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

2. Click the upper-left cell of the destination area for the consolidated

   data, which would be cell A1 on Sheet2.

3. On the Data menu, click Consolidate.

4. In the Function box, select the summary function you want Microsoft

   Excel to use to consolidate the data. In this example, use Sum.

5. In the Reference box, enter the source areas you want to consolidate.

   The first area is Sheet1!$A$2:$C$6 and the second area is
   Sheet1!$A$12:$C$16.

6. Click Add.

7. Repeat steps 6 and 7 for all source areas you want to consolidate.

8. Under Use labels in, click Left column (in this example, there

   are labels in the left column).

9. 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.

Selecting Source Areas for a Consolidation Table

You specify the source areas of the data you want to summarize in the Reference box of the Consolidate dialog box. Use these guidelines to define source areas:

When the sources and destination are on the same worksheet, use cell references.
When the sources and destination are on different worksheets, use sheet and cell references.
When the sources and destination are in different workbooks, use book, sheet, and cell references.
When the sources and destination are in different workbooks in different locations on a disk, use the full path, book, sheet, and cell references. You can also type the complete path, workbook name, and sheet name for the source area. Type an exclamation point following the sheet name, and then type the cell reference or name of the source area.
When the source area is a named range, use its name.

TIP: To enter a source definition without typing, click in the Reference box, and then select the source area.

REFERENCES

For more information about Consolidating Data in Microsoft Excel 7.0, click Answer Wizard on the Help menu, and type:

   Consolidate Data

"Microsoft Excel User's Guide," version 5.0, page 527

Additional query words: 7.00 8.00 97 98

Keywords          : kbualink97 xllist 
Version           : WINDOWS:5.0,7.0,97; MACINTOSH:5.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: January 7, 1999