Consolidating Data

You can consolidate data to summarize and report results of data that appears on separate worksheets. The worksheets can be in different workbooks. The consolidated data is easier to update and aggregate on a regular basis.

Use the following procedure to consolidate data.

1. Select the starting cell where you want to display the consolidated data. Make sure to leave enough room for the consolidated data, so that you don’t overwrite other information. In this example, choose the top left cell in Sheet 3.
2. Select the Data tab from the Ribbon.

3. Select Consolidate.

4. In the Consolidate dialog box, do the following:

5. Select the Function from the drop down list. In this example, use Average.

6. Select the Reference for each worksheet you are consolidating. If the worksheet is in another workbook, select Browse to open it. Select the cells to include in the consolidation from the first worksheet and select Add. Repeat for each reference.