Change a data consolidation of multiple worksheets

After you have consolidated data from multiple worksheets, you may want to change the way that the data is consolidated. For example, you may want to add worksheets from new regional offices, delete worksheets from departments that no longer exist, or change formulas with 3-D references.

What do you want to do?

Change a consolidation made by position or category

Change a consolidation by formula

Change a consolidation made by position or category

Note: You can change the consolidation only if you did not previously select the Create links to source data check box in the Consolidate dialog box. If the check box is selected, click Close, and then re-create the consolidation.

1. Click the upper-left cell in the consolidated data.

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

3. Do one or more of the following:

   Add another source range to the consolidation

   The new source range must have either data in the same positions (if you previously consolidated by position) or column labels that match those in the other ranges in the consolidation (if you previously consolidated by category) .

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

   2. Type the name that you gave the range, and then click Add.

   Adjust the size or shape of a source range

   1. Under All references, click the source range that you want to change.

   2. In the Reference box, edit the selected reference.

   3. Click Add.

   Delete a source range from the consolidation

   1. Under All references, click the source range that you want to delete.

   2. Click Delete.

   Make the consolidation update automatically

   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.

   Select the Create links to source data check box.

4. To update the consolidation with the changes, click OK.

Top of Page

Change a consolidation by formula

You change a consolidation by formula by editing the formulas, such as changing the function or expression. Regarding cell references, you can do one of the following:

If the data to consolidate is in different cells on different worksheets    

• Add, change, or delete the cell references to other worksheets. For example, to add a reference to cell G3 in a Facilities worksheet that you have inserted following the Marketing worksheet, you would edit the formula as shown in the following example.

  Before:

  

  After:

  

If the data to consolidate is in the same cells on different worksheets    

• To add another worksheet to the consolidation, move the sheet into the range that your formula refers to. For example, to add a reference to cell B3 in the Facilities worksheet, move the Facilities worksheet between the Sale and HR sheets as shown in the following example.

  

  Because your formula contains a 3-D reference to a range of worksheet names, Sales:Marketing!B3, all worksheets in the range are included in the new calculation.

Top of Page