Changing multiple Excel worksheet data consolidation calculations

After you consolidate data from multiple Excel worksheets, you may need to change how you want to consolidate the data. For example, you might want to add a worksheet for a new regional office, or delete a worksheet from a department that no longer exists, or change a reference to a three-dimensional reference (three-dimensional reference: a range that spans two or more worksheets in a workbook.) ) of the formula.

To change a consolidation by position or category

Note You can change the consolidation only if you have not previously selected the Create link to source data check box in the Merge Calculation dialog box. If the check box is selected, click Close, and then recreate the consolidation.

1, click the upper-left cell of the merged calculation data.

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

3. Do one or more of the following:

• Add source range for consolidation calculation

The new source range must have data in the same location (if it was previously consolidated by position), or a column label that matches those column labels in other areas of the consolidation, if previously consolidated by category.

A if the worksheet is in another workbook, click Browse to locate the file, and then click OK to close the Browse dialog box.

Enter the file path followed by an exclamation point in the Reference box.

B Type the name you specified for the range, and then click Add.

• Adjust the size or shape of the source area

A under all reference locations, click the source range that you want to change.

B in the Reference box, edit the selected reference.

C click "Add".

• Delete the source range from the merge calculation

A in all reference locations, click the source range that you want to delete.

B click "Delete".

• Automatically update consolidation calculations

Important You can select this check box only if the worksheet is in another workbook. Once this check box is selected, you cannot make changes to which cells and ranges are included in the consolidation calculation.

Select the Create link to source data check box.

4. To update the consolidation calculation, click OK.

Change the consolidation by formula

By editing a formula (for example, changing a function or an expression), you can change the consolidation by formula. For cell references, you can do one of the following:

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

• Add, change, or delete cell references to other worksheets. For example, to add a reference to cell G3 in the Supply Department worksheet that is inserted after the Marketing department worksheet, you need to edit the formula in the following example.

Before:

Before you change

After:

After the change

If the data you want to consolidate is in the same cell on different worksheets

• To add another worksheet to the consolidation, move the worksheet to the range that the formula refers to. For example, to add a reference to cell B3 on the Supply Department worksheet, move the Supply department worksheet between the sales department and the Human Resources worksheet, as shown in the following example.

Mobile Supply Department Worksheet

Because the formula contains a three-dimensional reference to a worksheet name area (Sales Department: Marketing Department!) B3 "), so all worksheets within the zone are included in the new calculation.

Note : More wonderful tutorials Please pay attention to the triple computer tutorial section, triple Computer office group: 189034526 welcome you to join