Consolidate data in multiple worksheets

To summarize and report results from separate worksheets, you can consolidate data from each separate worksheet into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that 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 a 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.

To consolidate data, use the Consolidate command in the Data Tools group on the Data tab.

What do you want to do?

Consolidate by position

1. Set up the data to be consolidated on each separate worksheet.

   How to set up the data

   • 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. Don't put any of the ranges on the worksheet where you plan to put the consolidation.
   • Make sure that each range has the same layout.
   • Name each range: Select the entire range, and then on the Formulas tab in the Named Cells group, click the arrow next to Name a Range, and type a name for the range in the Name box.
2. Click the upper-left cell of the area where you want the consolidated data to appear in the master worksheet.

   Note Make sure that you leave enough cells to the right and below this cell for the consolidated data. The Consolidate command populates the area as needed.

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

   

4. In the Function box, click the summary function that you want Microsoft Office Excel to use to consolidate the data.
5. 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.

6. Type the name that you gave the range, and then click Add. Repeat this step for each range.
7. 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.
8. Leave the boxes under Use labels in blank. Excel does not copy the row or column labels in the source ranges to the consolidation. If you want labels for the consolidated data, copy them from one of the source ranges or enter them manually.

Consolidate by category

1. Set up the data to be consolidated on each separate worksheet.

   How to set up the data

   • 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. Don't put any of the ranges on the worksheet where you plan to put the consolidation.
   • Make sure that the labels for columns or rows that you want to combine have identical spelling and capitalization; for example, labels Annual Avg. and Annual Average are different and will not consolidate.
   • Name each range: Select the entire range, and then on the Formulas tab in the Named Cells group, click the arrow next to Name a Range, and type a name for the range in the Name box.
2. Click the upper-left cell of the area where you want the consolidated data to appear in the master worksheet.

   Note Make sure that you leave enough cells to the right and below this cell for the consolidated data. The Consolidate command populates the area as needed.

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

   

4. In the Function box, click the summary function that you want Excel to use to consolidate the data.
5. 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.

6. Type the name you gave the range, and then click Add. Repeat this step for each range.
7. 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.
8. 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.

Consolidate by formula

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:

     

     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:

     

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.