Consolidation and Linking

There are many spreadsheet applications that require data from several worksheets in a workbook to be summarized onto one additional worksheet. For example, if you were the business manager for Georgetown College, you might want to have the budgets for each individual academic department on a separate worksheet. Then you would add one additional worksheet that would summarize the income and expenditures for each of these departments. That sheet would then represent the academic budget for the College. Thus your workbook would contain separate worksheets representing the individual budgets of the separate academic departments and one additional sheet that summarizes the entire budget situation.

The process of summarizing information found on multiple sheets is called consolidation and the term “consolidated balance sheet” is often used when describing this process in accounting.

Microsoft Excel's three-dimensional capabilities make it easy to complete a consolidation process by linking one worksheet to another. Data in one (or more) worksheets is automatically transferred from one sheet (or multiple sheets) to the summary sheet by use of formulas or functions that reference cells in other sheets.

To reference cells in other sheets in a workbook, you use the sheet name (aka sheet reference) in the formula or function. To help you follow these references, make sure you rename the worksheets in your workbook!

Three-dimensional, or linked, references can be completed using the same techniques for building formulas and functions explained earlier. That is, you can use either

The advantage in using the first two methods is that the sheet references are automatically entered for you in the correct syntax.

A Simple Example of Linked Worksheets

You have just linked three worksheets! Now let's see what happens when we enter some numbers.

Go Back

Copyright©2003
last updated August 12, 2003