Consolidating Data

graphics/microsoft59.gif

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

graphics/one_icon.jpg

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

graphics/tow_icon.jpg

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

graphics/three_icon.jpg

Select the destination range.

graphics/foure_icon.jpg

Click the Data menu, and then click Consolidate.

graphics/five_icon.jpg

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

graphics/six_icon.jpg

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.

graphics/seven_icon.jpg

Click the Expand Dialog button.

graphics/eight_icon.jpg

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

graphics/nine_icon.jpg

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

graphics/ten_icon.jpg

Select the Create Links To Source Data check box.

graphics/leven_icon.jpg

Click OK.

graphics/12inf34.jpg

graphics/12inf35.jpg

graphics/12inf36.jpg

graphics/12inf37.jpg

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.



Show Me Microsoft Office Excel 2003
Show Me Microsoft Office Excel 2003
ISBN: 0789730057
EAN: 2147483647
Year: 2002
Pages: 291

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net