Excel Consolidate

Consolidate Data

Before you begin consolidating by position or category, make sure the data is arranged in labeled rows and columns without any blank rows or columns. Each of the ranges you want to consolidate needs to be on a separate worksheet, with a blank worksheet for the consolidation’s destination.

When consolidating, you don’t actually specify whether you are consolidating by position or category—Excel knows how to consolidate based on the data range you select and whether or not the consolidating worksheets are arranged identically.

1. Select the sheet where the consolidated data will be placed.
2. Click the cell in the worksheet where you want to place the consolidated data.
3. Click the Data tab on the ribbon.
4. Click the Consolidate Data button in the Data Tools group.
   

   The Consolidate dialog box appears. Here you can choose what you want to do with the data, like find the sum, count, or average.

5. Select a function from the list.

   If source data is in a different workbook, click Browse to locate the file and click OK.

6. Click the Collpase Dialog Box button.
   
7. Navigate to the worksheet with the set of data to be consolidated.
8. Select the data you want to include in the consolidation.

   Be sure to select the data as well as any headings and totals to be included.

9. Click the Expand Dialog Box button.
   
10. Click the Add button.
    
11. Repeat Steps 6-10 until all data ranges you wish to consolidate are added.
12. (Optional) Check the Top row and/or Left column check box to use the label from the referenced data in the worksheet containing the consolidated data. This tells Excel where the labels are located in the source ranges.
13. (Optional) If you want the consolidation to update automatically whenever the source data changes, check the Create links to source data check box.
14. Click the OK.
    

The worksheet’s values are changed to the values you specified in the scenario. You can select any scenario here to update the values in your spreadsheet.