Consolidate data from multiple worksheets in a single worksheet

To summarize and report results from data on separate worksheets, you can consolidate the data from each separate worksheet into one worksheet (or master worksheet). The worksheets you consolidate can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data in one worksheet, you can more easily update and aggregate it on a regular or ad hoc basis. For example, if you have a worksheet of expense figures for each of your regional offices, you might use data consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.

There are several ways to consolidate data:

  • Consolidate by position    Use this method when the data from multiple source areas is arranged in the same order and uses the same row and column labels. For example, when you have a series of expense worksheets that are created from the same template.
  • Consolidate by category    Use this method when the data from multiple source areas is arrange differently, but the same row and column labels are used. For example, you can use this method when you have a series of inventory worksheets for each month that use the same layout, but each worksheet contains different items or a different number of items.
  • Use a formula to consolidate data    Instead of using the Consolidate command (Data tab, Data Tools group), you can also consolidate data by using a formula.
What do you want to do?


Consolidate data by position

  1. In each worksheet that contains the data that you want to consolidate, set up the data by doing the following:
    • Make sure that each range of data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
    • Put each range on a separate worksheet, but don't put any ranges on the worksheet where you plan to put the consolidation.
    • Make sure that each range has the same layout.

 Tip   If you frequently consolidate data, it might help to base your worksheets on a worksheet template that uses a consistent layout.

  1. In the master worksheet, click the upper-left cell of the area where you want the consolidated data to appear.

 Note   To avoid overwriting existing data in the destination worksheet with the data you are consolidating, make sure that you leave enough cells to the right and below this cell for the consolidated data.

  1. On the Data tab, in the Data Tools group, click Consolidate.

Data Tools group on the Data tab

  1. In the Function box, click the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) that you want Microsoft Excel to use to consolidate the data.
  2. If the worksheet that contains the data that you want to consolidate is in another workbook, click Browse to locate that workbook, and then click OK to close the Browse dialog box.

The file path is entered in the Reference box followed by an exclamation point.

  1. If the worksheet that contains the data that you want to consolidate is in the current workbook, do the following:
  1. In the Reference box, click the Collapse Dialog button to select the data in the worksheet.
  2. Click the worksheet that contains the data you want to consolidate, select the data, and then click the Expand Dialog button.
  1. In the Consolidate dialog box, click Add, and then repeat steps 6 and 7 to add all of the ranges that you want.
  2. Specify how you want to update the consolidation, by doing one of the following:
    • Update the consolidation manually    To set up the consolidation so that you can update the consolidation manually by changing the included cells and ranges, clear the Create links to source data check box.
  • Update the consolidation automatically when the worksheet that contains the data is in another workbook    To set up the consolidation so that it updates automatically when the source data in another workbook changes, select the Create links to source data check box.

 Note   Once you select this check box, you won't be able to change which cells and ranges are included in the consolidation.

Top of Page Top of Page

Consolidate data by category

  1. In each worksheet that contains the data that you want to consolidate, set up the data by doing the following:
    • Make sure that each range of data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.
    • Put each range on a separate worksheet, but don't put any ranges on the worksheet where you plan to put the consolidation.
    • Make sure that each range has the same layout.

 Tip   If you frequently consolidate data, it might help to base your worksheets on a worksheet template that uses a consistent layout.

  1. In the master worksheet, click the upper-left cell of the area where you want the consolidated data to appear.

 Note   To avoid overwriting existing data in the destination worksheet with the data you are consolidating, make sure that you leave enough cells to the right and below this cell for the consolidated data.

  1. On the Data tab, in the Data Tools group, click Consolidate.

Data Tools group on the Data tab

  1. In the Function box, click the summary function (summary function: A type of calculation that combines source data in a PivotTable report or a consolidation table, or when you are inserting automatic subtotals in a list or database. Examples of summary functions include Sum, Count, and Average.) that you want Excel to use to consolidate the data.
  2. If the worksheet is in another workbook, click Browse to locate the file, and then click OK to close the Browse dialog box.

The file path is entered in the Reference box followed by an exclamation point.

  1. If the worksheet is in the current workbook, do the following:
  1. Click the reference button to the right of the Reference box, to open the Consolidate - Reference dialog box.
  2. Open the worksheet that contains the data you want to consolidate, select the data, and then click the button to the right of the box.
  1. In the Consolidate dialog box, click Add, and then repeat steps 6 and 7 to add all of the ranges that you want.
  2. Decide how you want to update the consolidation. Do one of the following:
  • To set up the consolidation so that it updates automatically when the source data changes, select the Create links to source data check box.

 Important   You can only select this check box if the worksheet is in another workbook. Once you select this check box, you won't be able to change which cells and ranges are included in the consolidation.

  • To set up the consolidation so that you can update the consolidation manually by changing the included cells and ranges, clear the Create links to source data check box.
  1. Select the check boxes under Use labels in that indicate where the labels are located in the source ranges: either the Top row, the Left column, or both.

 Notes 

  • Any labels that don't match up with labels in the other source areas result in separate rows or columns in the consolidation.
  • Make sure that any categories that you don't want to consolidate have unique labels that appear in only one source range.

Top of Page Top of Page

Use a formula to consolidate data

In addition to consolidating data by position or by category, you can also consolidate data by using formulas or PivotTable reports.

  1. On the master worksheet, copy or enter the column or row labels that you want for the consolidated data.
  2. Click a cell that you want to contain consolidated data.
  3. Type a formula that includes a cell reference to the source cells on each worksheet or a 3-D reference that contains data that you want to consolidate. Regarding cell references, do one of the following:
    • If the data to consolidate is in different cells on different worksheets    Enter a formula with cell references to the other worksheets, one for each separate worksheet. For example, to consolidate data from worksheets named Sales (in cell B4), HR (in cell F5), and Marketing (in cell B9), in cell A2 of the master worksheet, you would enter the following:

Formula to consolidate cells from three worksheets that uses cell references

 Tip    To enter a cell reference, such as Sales!B4, in a formula without typing, type the formula up to the point where you need the reference, click the worksheet tab, and then click the cell.

  • If the data to consolidate is in the same cells on different worksheets    Enter a formula with a 3-D reference that uses a reference to a range of worksheet names. For example, to consolidate data in cells A2 from Sales through Marketing inclusive, in cell A2 of the master worksheet you would enter the following:

Formula to consolidate cells from three worksheets that uses 3-D cell references

 Note   If the workbook is set to automatically calculate formulas, a consolidation by formula always updates automatically when the data in the separate worksheets change.

Top of Page Top of Page

 
 
Applies to:
Excel 2013