Use the Consolidate command to join values from a number of different ranges into one new range. You can use Consolidate to sum the values in other ranges, to get an average of the values, to find the min or max value, and more.
To consolidate data from different ranges:
- On the DATA tab, in the Data Tools group, click the Consolidate command:
- In the Consolidate dialog box:
- Select the Function to use.
- Click the red arrow to the right of the Reference data entry box.
- Select the first series of cells you want to consolidate and then click the red arrow in the Consolidate - Reference dialog box to return to the Consolidate dialog box.
- Click Add to add the series of cells to the All references box.
- Repeat steps B, C, and D until you have selected all series of cells that you want to consolidate.
- Click OK: