In this tutorial, we will demonstrate a guide about linking in Excel. This further teaches us to link files, web pages, email addresses, etc. Finally, we will customize links, move and remove them from our workbook.
Linking dynamically pulls data from one sheet into another, and updates the data in your destination sheet whenever you change the contents of a cell in your source sheet. Using linking in Excel, you can make an Index sheet or a Summary page for your projects.
Read the full article to have an expert perspective on linking in Excel. Before that, see the overview image of this article below.
Download Practice Workbook
You can download the practice workbook for free.
An Overview of Linking in Excel
A link is like a clicky door that takes you to another place, like a webpage, a picture, an email, or a program. You can click on the text or a picture to use a link.
When you click a link, it shows the new place in your internet explorer, opens a file or starts a program. For instance, a link to a webpage makes the page appear in your browser, and a link to a video file opens it in a video player.
Absolute v/s Relative Linking in Excel
An Absolute URL has the whole address, including the protocol, server, and path with the file name.
A Relative URL is like a puzzle piece; it’s missing some parts. Those missing pieces come from the page where the URL is found. If, for example, the protocol and server are missing, the web browser uses the current page’s protocol and domain (like .com, .org, or .edu).
On the web, it’s usual to use relative URLs with just a part of the path and file name. If the files go to another server, the links still work if the positions of the pages stay the same. For instance, a link on Products.htm points to a page named apple.htm in a folder named Food. If both pages move to a different server but still stay in a folder named Food, the link’s URL stays correct.
Why Do We Use Linking in Excel?
We use linking in Excel for:
- Go to a file or webpage on a network, inside a company’s network, or on the internet.
- Go to a file or webpage you’ll make later.
- Send an email.
- Begin moving a file, like downloading, or use FTP (File Transfer Protocol).
Best Practices for Linking in Excel
5 practices for linking in Excel are:
- Use Short File Name.
- Insert Meaningful Name Range.
- Enable Automatic Calculation.
- Backup your data.
- Show Snapshot of Linked Range.
How to Link Cells Within Same Workbook in Excel
In this section, we will discuss all possible ways to link cells within the same workbook in Excel.
1. Mirror Cell Data from Another Sheet Using Equal (=) Sign
We can mirror cell data from different sheets by linking cells.
1.1 Single Cell
- Move to Single sheet and enter Equal (=) sign in D5 >> click on Summary sheet.
- In Summary sheet, click on D5 >> press Enter key.
- Thus, we link the sheets and value appears in D5.
- Drag the AutoFill tool down to get other values.
1.2 Multiple Cells
- To link multiple cells, add Equal (=) to D5 of Multiple sheet >> go to Summary sheet.
- In Summary sheet, select the range D5:D12 >> press Enter button.
- Consequently, the list appears in Multiple sheet linking the two sheets as well.
2. Use Copy and Paste Link Commands
- In the Summary sheet, select range D5:D12 >> go to Home tab >> Copy.
- Now, go to Paste Link sheet >> place cursor on D5 >> go to Home tab >> Paste >> Paste Link.
Cells of present sheet are now linked with Summary sheet.
3. Link Cells Using Named Range
- First, create a named range by selecting range D5:D12 >> type Details in Name box. See the below picture for better understanding.
- Later, in another sheet, type the following texts in D5:
=Details
- It calls the named range in the corresponding cell.
- Subsequently, the value appears linking the ranges in 2 sheets.
In this type of linking, a Blue border appears around the range. It means any editing or changing in the range will return #SPILL! error.
Read More: Link Cells in Excel
4. Use Plus Sign to Sum Multiple Linked Values
- Initially, type Equal (=) in C5 >> click on Summary sheet.
- Further, click C5 >> enter Plus (+) sign >> tap E5.
- As a result, the sum of the two cells appears in another sheet.
5. Apply SUM Function to Get Multiple Linked Data
You can also use an alternative of the previous method by using the SUM function.
- Firstly, type =SUM( in C5 >> click Summary sheet.
- In Summary tab, click C5 >> add Plus (+) >> E5 and close the 1st bracket as well.
- Therefore, we obtain the summation using SUM function.
How to Link Sheets in Excel with Index
Previously, we showed how to link cells in Excel from different sheets. Now, we will show how to link sheets in Excel. Here, we will link the cell values with desired sheets. By clicking on cells, we will move to linked sheets as well.
- To link sheet with an Index page select B5 >> go to Insert tab >> Link >> Insert Link.
- In Insert Hyperlink dialog box, click Place in This Document >> Quarter 1 >> OK.
Here, the cell reference A1 means we will move to cell A1 of the linked sheet.
- Also, type the following formula in C5 to get the total revenue of Quarter 1.
='Quarter 1'!D16
- Similarly, insert the links in other cells as well.
- Now, click on Q3.
- In return, we will move to the Quarter 3 sheet automatically.
How to Link Excel Files in Excel
In this section, we will show how to link other Excel files in Excel.
1. Link to a New File
- To link a new file, select D5 >> press Ctrl + K keys.
- In the Insert Hyperlink dialog, click Creat New Document >> Change >> select file in Name of new document >> write link text Text to display in ScreenTip >> OK.
- As a result, the link appears.
2. Link Existing Microsoft Files
We can also existing Microsoft files easily in Excel.
- To link a Microsoft file, select D5 >> and tap Ctrl + K to insert a hyperlink.
- In Insert Hyperlink dialog, click on Existing File or Web Page >> Current Folder >> select a Microsoft Word file >> type the text you want to display in ScreenTip >> OK.
- Consequently, the link with text appears in D5.
How to Link Web Pages to Excel File
We will use the HYPERLINK function to link web pages to Excel files. We have web page names and web addresses in the modified dataset, Now, we will link those web pages in Excel.
- In D5, enter the following formula:
=HYPERLINK(C5,B5)
Here, we use cell reference in the function. You can also use links and texts in HYPERLINK function as well.
- See the formula in D6,
=HYPERLINK("https://www.google.com", "Google")
This formula returns a link to go to Google web browser.
How to Link Image File in Excel
We can also link any image file in Excel easily.
- To link an image file, click on Existing File or Web Page >> Current Folder >> select an image file >> type link text in ScreenTip >> OK.
- Therefore, the link to open the image file appears in D5.
How to Link Email Address in Excel
- To link an Email Address in Excel, open the Insert Hyperlink dialog as shown previously >> click Email Adress >> fill E-mail address box >> Text to display >> Subject >> hit OK.
- The email address with the link appears in the respective cell.
How to Customize Existing Links Appearance in Excel Worksheet
- To customize link appearance, select the links >> go to Home tab >> Cell Styles.
- Select Linked Cell option in Data and Model.
- Eventually, the link text appearance changes.
How to Copy or Move Links in Excel
- To move a link, simply select cell with link >> press Ctrl + X to cut the link. Here, we selected D6.
- Now, locate B6 >> press Ctrl + V to paste the link. Subsequently, we moved the link from D6 to B6.
How to Modify Links for Error Handling in Excel
Sometimes, linking may return error alerts in Excel due to the deletion of the source file or other reasons.
- For example, clicking the link in D6 returns the below error alert.
- In that case, right-click on D6 >> go to Edit Hyperlink dialog.
- Alternatively, you can go to Link feature in Insert tab or press Ctrl + K shortcut keys.
- There, click Place in This Document >> choose a correct source >> OK.
Hence, we handle the error.
How to Remove Links in Excel
- Lastly, to remove a link, right-click on the link >> locate and click Remove Hyperlink option in the context menu.
- Consequently, the link disappears in D5.
Read More: Remove Links in Excel
Things to Remember
- Make sure your calculations in the spreadsheet update automatically with new data insertion. For that, go to the Calculation section in Excel’s ribbon, click the arrow by Calculation Options, and choose Automatic.
- Find where your links are, match them with spreadsheet names, and this avoids accidental changes in your spreadsheets.
- If there’s a broken link in your workbook, then you can either fix the link or remove it. To fix the link, go to Edit Links in Data tab.
Frequently Asked Questions
1. What is a URL and how it works in Excel?
A URL (Uniform Resource Locator) is a web address that points to a specific location on the internet, such as a website, a file, or a resource. In Excel, you can use URLs to create hyperlinks that allow you to quickly access web pages, files, or other content.
2. How do I update linked data?
You can update linked data manually by right-clicking the cell containing the link and selecting Update Link. Alternatively, you can set links to update automatically when the source data changes.
3. What if I encounter errors with my links?
Excel provides error messages for invalid or broken links. Double-check your link addresses and the integrity of your linked files.
Conclusion
To summarize, we have discussed all things to know about linking in Excel. Hopefully, you can now implement the methods in your own projects. Linking sheets enable you to replicate the sheet, saving a lot of time. If you have any queries or suggestions, let us know in the comment box. Thank you for reading.
Linking in Excel: Knowledge Hub
- Link Sheets in Excel
- Link Excel Workbooks
- Hyperlink in Excel
- Anchoring in Excel
- Update Excel Links
- Find Links in Excel
- Edit Links in Excel
- Break Links in Excel
<< Go Back To Learn Excel
Get FREE Advanced Excel Exercises with Solutions!