Consolidating Data

In some cases, you'll want to consolidate data from different worksheets or workbooks into one workbook, rather than simply linking the source data. For instance, if each division in your company creates a budget, you can pull together, or consolidate , the totals for each line item into one company-wide budget. If each divisional budget is laid out in the same way, with the budgeted amounts for each line item in the same cell addresses, then you can very easily consolidate the information without any retyping. If data in individual workbooks change, the consolidated worksheet or workbook will always be correct.

Consolidate Data from Other Worksheets or Workbooks

Open all the workbooks that contain the data you want to consolidate.

Open or create the workbook that will contain the consolidated data.

Select the destination range.

Click the Data menu, and then click Consolidate.

Click the Function list arrow, and then select the function you want to use to consolidate the data.

Type the location of the data to be consolidated, or click the Reference Collapse Dialog button, and then select the cells to be consolidated.

Did You Know?

You can include all labels . Make sure you select enough cells to accommodate any labels that might be included in the data you are consolidating.

Click the Expand Dialog button.

Click Add to add the reference to the list of consolidated ranges.

Repeat steps 6 through 8 until you have listed all references to consolidate.

Select the Create Links To Source Data check box.

Click OK.

Did You Know?

You can consolidate worksheets even if they are not laid out identically . If the worksheets you want to consolidate aren't laid out with exactly the same cell addresses, but they do contain identical types of information, select the Top Row and Left Column check boxes in the Consolidate dialog box so that Excel uses labels to match up the correct data.

You can arrange multiple workbooks . Use the Window menu to move between workbooks or to arrange them so they are visible at the same time.