Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 71 Data Linking
CHAPTER 7 DATA LINKING
The process of displaying or using data that resides in another cell,
perhaps even in another workbook, is known as data linking.
Excel enables you to link the data in your worksheet with data in
other sheets, other workbooks and even other applications.
In this session you will:
gain an understanding of data linking
learn how to create links between worksheets
learn how to link between workbooks
learn how to update links between workbooks.
INFOCUS
WPL_E837
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 72 Data Linking
UNDERSTANDING DATA LINKING
Links between worksheets and workbooks can
be created in Excel by including workbook and
worksheet names, as well as the cell reference,
within a formula. An internal link refers to one
within the same file, while an external link refers to
a link with another file. The cells referenced by a
link are known as the source data. When source
data changes, the linked cells will also change.
The Advantages of Linking Data in Excel
linking data from one worksheet to another worksheet ensures that the core data is entered or
calculated only once and then shared with other files
you can develop a large system using smaller, more manageable worksheets and workbooks
linked workbooks do not all need to be open for the links to be updated, freeing memory for other
activities
separate modules can be created and managed by different people
The Syntax of a Link
A typical link has the following structure:
=drive:\path\[filename]worksheet name!cell reference
The full path name is only displayed when the source file is not opened.
Sample of Internal and External Links
Internal links to two
other worksheets in
the same workbook
External link to an
open workbook
External link to a
closed workbook
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 73 Data Linking
LINKING BETWEEN WORKSHEETS
Try This Yourself:
Open
File
Before starting this exercise you
MUST open the file E837
Linking_1.xlsx...
Examine the workbook
This worksheet contains data for
three years on separate worksheets
and a summary page at the front...
Click on cell B6 and type =
Click on the worksheet tab for
Year2009 and click on F6
Press to complete the formula
Let’s try another technique...
Click on B8, type =Year2008!E4
and press
You can add multiple references
with a plus sign...
Click on B13 and use the technique
in steps 2 and 3 to build the formula:
=Year2008!F4+Year2009!F4+
Year2010!F4
Press then click on B15 and
type the formula:
=Year2008!F8+Year2009!F8+
Year2010!F8
Press
For Your Reference…
To create a link between worksheets:
1. Type as much of the formula as is required
before the linked cell reference
2. Click on the cell or type the reference
3. Press or continue with the formula
Handy to Know…
When creating links in a workbook, although
clicking on each cell to pick up the reference
may seem tedious, it guarantees the
accuracy of the reference and therefore your
formula.
You can link cells between worksheets in a
workbook by preceding the source cell reference
in the formula with the sheet name, followed by
an exclamation mark. For example, if you want to
work with cell B6 in the Accounts sheet, your
reference to the cell would be Accounts!B6. A
common use for this technique is to create a
summary sheet of other sheets in a workbook.
3
6
8
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 74 Data Linking
LINKING BETWEEN WORKBOOKS
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or open
the file E837 Linking_2.xlsx...
Press + to create a
new workbook
Enter the headings as shown,
then click on A5 and type =
Click on the View tab of the
Ribbon, click on Switch
Windows and select 2
E837 Linking_2.xls
Click on the Year2010
worksheet tab and click on
B8
Type *39% and press
Click on A5 and examine the
formula
We need to make the cell
reference relative so we can
fill the formula to other cells...
Double-click on $B$8 in the
formula bar, type B8 and
press
Click on A5 then drag the fill
handle to copy the formula
across to D5
Click on the Home tab and
click on Comma Style to
format the numbers
For Your Reference…
To link between workbooks:
1. Type as much of the formula as is required
before the linked cell reference
2. Switch to the other workbook and click on
the cell(s)
3. Press or continue with the formula
Handy to Know…
Links to cells in other workbooks are
automatically absolute (i.e. displayed with $
signs). This means that if you copy the
formula, the column and row references will
remain unchanged. You must remove the
dollar signs first if you want to fill the formula
to other cells.
2
4
Linking between workbooks involves the same
basic steps as linking between worksheets within
a workbook. In addition, the linking formula must
be very specific so that Excel can find the file that
you want to link to. If the workbook resides in
another folder, the formula must include the full
path to the workbook file. Workbook names need to
be enclosed in square brackets.
The linked reference contains the file and worksheet name,
plus the cell reference ($B$8) as an absolute reference…
6
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 75 Data Linking
UPDATING LINKS BETWEEN WORKBOOKS
Try This Yourself:
Open
File
Before starting this exercise
you MUST open the file E837
Linking_3.xlsx...
Click on the worksheet tab for
Year2010
Click on B4, type 678000 and
press to change the data
Press + to save the
workbook, then press +
to close it
Click on the File tab and select
Open, then open Workbook
Linking.xls
As this file contains links to an
unopened file, a Security
Warning will appear above the
formula bar...
Click on the Data tab of the
Ribbon, then click on Edit
Links to display the Edit
Links dialog box
Click on [Update Values]
The status of the source
workbook changes to OK – for
more than one link, you would
need to repeat this step...
Click on [Close]
The figure for Qtr1 now reflects
the change
For Your Reference…
To update links between workbooks:
1. Open the destination workbook
2. On the Data tab, click on Edit Links
3. Click on the link to update
4. Click on [Update Values]
5. Click on [Close]
Handy to Know…
When you open a workbook with links to a
closed workbook, you can click on [Options]
in the Security Warning and click on Enable
this content to update the links – this is an
alternate method to using the Edit Links
dialog box and will update all links
simultaneously.
4
5
The workbook that contains the original data is
known as the source workbook. The workbook
that contains the link formula is known as the
destination workbook. When the source is open,
the destination workbook is automatically updated.
If you open the destination workbook when the
source is closed, you can update it from the source
immediately or update it later.
7
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 76 Data Linking
NOTES:
1
4
