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
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.
Click the upper-left cell in the consolidated data.
On the Data tab, in the Data Tools group, click Consolidate.
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) .
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.
Type the name that you gave the range, and then click Add.
Adjust the size or shape of a source range
Under All references, click the source range that you want to change.
In the Reference box, edit the selected reference.
Delete a source range from the consolidation
Under All references, click the source range that you want to 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.
To update the consolidation with the changes, click OK.
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.
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.