Learn how to link Link cells in the same or different Excel worksheets.
Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another.
For example, if I’m creating a personal cash flow worksheet and at the end of the month I have $4083.58 in my bank account. I can easily link the closing balance from the end of last month to the opening balance for this month. This saves me having to do double-entry, and it ensures the two figures are always the same.
When worksheets are linked, generally one worksheet contains a reference to data in another.
- The worksheet supplying the data is called the source worksheet.
- The worksheet containing the reference is termed the dependent worksheet.
- The actual reference is called an external reference.
As a simple example, consider the following.
There are two worksheets open, Monthly Actuals and Yearly Budget.
Join the 'Excel at Work Insiders' group today!
100% free. Join now and unlock offers exclusive to this group!
And gain instant access to your first exclusive bonus:
'Steps to becoming Proficient in Excel'!
A list of over 100 skill topics you need to know to become a proficient user of Excel
The worksheet Yearly Budget contains the following external reference, =MonthlyActuals!$B$6:$B$7.
Yearly Budget is the dependent worksheet relying on data from Monthly Actuals, and Monthly Actuals is the source worksheet, providing data for Yearly Budget.
Tip: links aren’t obvious and it can sometimes be frustrating trying to locate them within the worksheet. To quickly locate linked cells check out my blog post Find, modify and break links to an Excel workbook.
Linking a range of cells
The following techniques describe how to link cells from a source worksheet into a dependent worksheet.
Follow the same technique to link data between workbooks.
Option 1: Using Paste Link
1. In the source worksheet select the required cells.
2. Copy the selected data, e.g. CTRL + C or right-click, Copy.
3. Switch to the dependent worksheet and then select the upper left corner of the range where you want the linked data to appear.
4. To paste the link do one of the following:
- Right-click where you want to paste the link and then select Paste Link from the shortcut menu.
- From the Home tab, in the Clipboard group click on the arrow under the Paste option and select Paste Link.
5. The data will be pasted as a link through to the source worksheet.
Note: using this option may use an absolute cell reference to refer to the linked cell. If you would prefer a relative reference refer to the steps in Option 2.
Option 2: Create a link manually
You can manually create a link to any cell by inserting a reference to the source data.
1. In the dependant worksheet select the cell to hold the linked data and then type equals (=).
2. Switch to the source worksheet/workbook and select the cell holding the data to be linked.
3. Press ENTER.
Hint: you may like to have all source worksheets open before saving the dependent worksheet, as this will automatically update any external references if the source worksheet is saved in a different folder.
Identifying linked cells
You can easily identify where cells are linked as the link address will show in the Formula bar.
| Link type | Formula examples |
| Linking within the same workbook | =’Worksheet name’!Reference e.g. =Northern!B9 |
| Linking to an external workbook | =’Full pathname for worksheet’!Reference e.g. ='[C:\Documents\Link Between Worksheets.xlsx]Northern’!B9 |
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
How to freeze a row in Excel
Let’s look at how to freeze a row in Excel. It can be a real pain when you don’t have freeze panes on, and when you start to scroll down the screen you lose the headings at the top of your columns.
Use Conditional Formatting to identify cells that are of interest
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using
Find, modify and break links to an Excel workbook
Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data
Use the SUMIF function to total only the cells that match your requirement
Use the SUMIF function to total only the cells that match your requirement. For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a
Insert an Excel function into your workbook
Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few.
Insert subtotal rows into sorted data
Learn how to insert subtotal rows into sorted data without having to spend time doing it manually. Recently I ran a training session for an Accounts Manager and her staff. They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at every change