Linking Documents in Excel
Excel can dynamically link a workbook to source data
in another workbook so that any changes you make in one
workbook are immediately reflected in the other workbook.
The following terms apply to linking documents:
- External Reference- A reference to another Excel
workbook cell, cell range, or defined name. A
formula containing an external reference is called
an external reference formula.
- Dependent Workbook- A workbook that contains a
link to another workbook. In other words, a workbook
that relies on information in another workbook.
- Source Workbook- A workbook that is the source
of the information referred to in an external
reference formula; source workbooks are referred to
by dependent workbooks.
Creating Links between workbooks
You will need two workbooks to create a link. The
company workbook will serve as your first and
as the Source Workbook. The second workbook will be
created and serve as the Dependent Workbook.
Let's start by creating the Dependent Workboook.
Choose New from the File menu to
start a new workbook.
Create the following workbook and call it budget:
Select cell C6 and enter the formula =C4+C5.
Select cell C11 and enter the formula =C9+C10.
Select C13 and enter the formula =C6-C11.
Your budget workbook should look as follows:
It is now time to create a link between the workbooks;
company and budget.
Have both workbooks open.
In the Source Workbook: company select
cell F18.
This is the cell you want to refer. You want to insert
this value into your budget workbook.
Click the Copy button or choose
Copy from the Edit menu.
A moving border should appear around cell F18.
Switch to the budget workbook.
This is the workbook you want to paste the
linked data.
Select cell C4.
This is the cell in which you want the linked data
to appear.
Choose Paste Special from the Edit
menu.
The following Paste Special dialog box
should appear:
Choose All in the Paste box and None in
the Operation box.
Choose the Paste Link button to paste the
link into cell C4.
Your budget workbook should look as follows:
Excel created an external reference formula that links
the workbooks. This formula appears in the formula bar.
You have successfully linked two documents. You can
now play around with What If? analysis and see how
changes in the the Book Company's income will effect
your personal budget.
Save your changes.
One of Excel's major features is its ability to
produce charts that illustrate the numbers on the
worksheets you produce. In the last part of the
tutorial you will learn the creation, formatting,
and printing of charts.