HOW TO: Consolidate Multiple Worksheets to a Single Sheet in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q214270
For a Microsoft Excel 97 and earlier version of this article, see 141577.
For a Microsoft Excel 98 version of this article, see 191019.

IN THIS TASK

Summary
This step-by-step article describes how to consolidate multiple worksheets to a single sheet in Excel.

You can summarize data from one or more source areas by consolidating itand creating a consolidation table. These source areas can be on the sameworksheet as the consolidation table, on different sheets in the sameworkbook, or in different workbooks. When you consolidate the source data,you apply a summary function, such as the SUM() function, to create thesummary 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.

  • 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.
back to the top

How to Consolidate Data by Position

To consolidate data by position, follow these steps:
  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					
  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					
  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 list, select the summary function that you want Microsoft Excel to use to consolidate the data. In this example, use Sum.
  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.
  7. Repeat step 6 for all of the source areas that you want to consolidate.
  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).
  9. Click OK.
back to the top

How to Consolidate Data by Category

To consolidate data by category, follow these steps:
  1. 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					
  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 list, select the summary function that you want Microsoft Excel to use to consolidate the data. In this example, use Sum.
  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.
  6. Repeat step 5 for all source areas you want to consolidate.
  7. Under Use labels in, click to select the Left column check box (in this example, there are labels in the left column).
  8. Click OK.
NOTE: If you want Microsoft Excel to update your consolidation tableautomatically 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.

back to the top

Selecting Source Areas for a Consolidation Table

You specify the source areas of the data that you want to summarize in theReference 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 Referencebox, and then select the source area.

back to the top
References
For more information about Consolidating Data, click Microsoft Excel Help on the Help menu, type Consolidate Data in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

back to the top
XL2003 XL2007 XL2010
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.
Properties

Article ID: 214270 - Last Review: 12/19/2012 21:39:00 - Revision: 3.0

Microsoft Office Excel 2003, Microsoft Office Excel 2007, Microsoft Excel 2010

  • kbhowtomaster KB214270
Feedback