Locked lesson.
About this lesson
Learn about creating and updating external links, and about the potential dangers of external links in Excel.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
External Links - Master.xlsx10 KB External Links - Source.xlsx
10 KB External Links - Extra Practice.xlsx
13.7 KB
Quick reference
Topic
Creating, updating and the dangers of external links in Excel.
When to use
When you have little alternative but to link to data in another workbook.
Instructions
Creating
- Open the “Source” workbook and the “Target” workbook
- Go to the Source workbook, and select the cell where you’d like the value from Target workbook to show up
- Type = then, using the mouse, navigate to the Target workbook, click the cell and press Enter
- You can now save and close Target workbook and the link will exist in Source workbook
Updating
- When you open the Source workbook, you should be prompted to update the data
- If you need to update at other times, go to Data > Edit Links
- Select the links you wish to update and click Update Values
Breaking links
- To convert links to hard coded values, go to Data > Edit Links
- Select the links you wish to convert and click Break Link
Dangers
- Links are set to certain file paths. If the file you’re linking to moves, then your links will break
- If you insert a new row or column in the target workbook, and the source workbook is not open, the source workbooks formulas will NOT update, and your links will point to the wrong places. (In order to modify the target workbook, the source workbook must also be open to avoid this issue.)
- Your ability to email the file to others is impaired. Unless you send them the target file and they put it in the exact same path on their machine, the links will be broken
- If you decide to make a backup copy of your entire solution, you may find your links pointing to the original data copy unless you are very careful. (The proper method to do this is open both the source and target workbooks, save the target under a new name, then save the source under its new name.)
Lesson notes are only available for subscribers.