Linking in Excel – A Complete Guide

This is an overview.

Overview of linking in Excel


Download Practice Workbook

Download the practice workbook.


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; parts are missing. 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 common to use relative URLs. If the files go to another server, the links still work if the positions of the pages stay the same. For example, 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 URL is correct.


Why Do We Use Linking in Excel?
We use linking in Excel to:

  • 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 create later.
  • Send an email.
  • Begin moving a file, like downloading, or use FTP (File Transfer Protocol).

Best Practices for Linking in Excel

  • 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

1. Mirror Cell Data from Another Sheet Using the Equal (=) Sign

1.1 Single Cell

  • Move to the Single sheet and enter Equal (=) sign in D5 >> click the Summary sheet.

Writing = in D5 and opening Summary sheet

  • In the Summary sheet, click D5 >> press Enter.

Selecting D5 in Summary sheet

  • The sheets are linked and the value is displayed in D5.
  • Drag down the Fill Handle to see the result in the rest of the cells.

Completed linking in Single and Summary sheets in Excel


1.2 Multiple Cells

  • To link multiple cells, add Equal (=) to D5 in the Multiple sheet >> go to the Summary sheet.

Typing = in D5 of Multiple sheet

  • In the Summary sheet, select D5:D12 >> press Enter.

Selecting D5:D12 in Summary sheet

  • The list appears in Multiple sheet linking the two sheets.

Linking Multiple and Summary sheets in Excel


2. Use the Copy and Paste Link Commands

  • In the Summary sheet, select D5:D12 >> go to Home tab >> Copy.

Select Copy option in Home tab for linking in Excel

  • Go to Paste Link sheet >> place cursor in D5 >> go to Home tab >> Paste >> Paste Link.

Paste Link option in Paste section

Cells in the current sheet are linked to the Summary sheet.


3. Link Cells Using a Named Range

  • Create a named range by selecting D5:D12 >> Enter Details in Name box.

Naming D5:D12 Details in Name box

  • In another sheet, enter the following in D5:
=Details
  • It calls the named range in the corresponding cell.

Calling Details named range in D5

  • The value appears linking the ranges in the 2 sheets.

Details named range appears in column D

Note:

In this type of linking, a Blue border appears around the range. Any editing or changing in the range will return the #SPILL! error.

#SPILL! Error while linking in Excel

Read More: Link Cells in Excel


4. Use Plus Sign to Sum Multiple Linked Values

  • Enter Equal (=) in C5 >> click the Summary sheet.

Entering Equal in C5 of Multiple Sum sheet

  • Click C5 >> enter Plus (+) sign >> Click E5.

Selecting C5 and E5 and adding Plus sign

  • The sum of the two cells appears in another sheet.

Total price appears in C5 for linking in Excel


5. Apply the SUM Function to Get Multiple Linked Data

Use the SUM function.

  • Enter =SUM( in C5 >> click the Summary sheet.

Using SUM function in C5 of SUM sheet

  • In Summary tab, click C5 >> add Plus (+) >> E5 and close the 1st bracket.

Selecting SUM components in the Summary sheet

  • This is the output.

Summation of 2 columns appears in SUM sheet


How to Link Sheets in Excel with Index

  • To link sheets with an Index page, select B5 >> go to Insert tab >> Link >> Insert Link.

Clicking Insert Link option in Link feature

  • In the Insert Hyperlink dialog box, click Place in This Document >> Quarter 1 >> OK.

Selecting Quater 1 sheet in Place in This Document

  • Enter the following formula in C5 to get the total revenue of Quarter 1.
='Quarter 1'!D16

Matching Total Revenue in C5 of Index sheet with C5 of Quater 1 sheet

  • Enter the links in other cells.
  • Click Q3.

Clicking Q3 to text linking in Excel

  • The Quarter 3 sheet is displayed automatically.

Completed linking to Index Excel sheet


How to Link Excel Files in Excel

1. Link to a New File

  • To link a new file, select D5 >> press Ctrl + K.

Pressing Ctrl + K shortcut keys

  • In the Insert Hyperlink dialog box, click Creat New Document >> Change >> select file in Name of new document >> enter the link text in Text to display >> OK.

Clicking Create New Document option in Insert Hyperlink dialog

  • The link is displayed.

Linking text in D5


2. Link Existing Microsoft Files

  • To link a Microsoft file, select D5 >> and press Ctrl + K to insert a hyperlink.

Press Ctrl + K keys to link Microsoft files

  • In the Insert Hyperlink dialog box, click Existing File or Web Page >> Current Folder >> select a Microsoft Word file >> enter the text you want to display in ScreenTip >> OK.

Clicking Existing File or Web Page in Insert Hyperlink dialog

  • The link with the text is displayed in D5.

Link appears in D5


How to Link Web Pages to an Excel File

Use the HYPERLINK function to link web pages to Excel files.

  • In D5, enter the following formula:
=HYPERLINK(C5,B5)

Cell reference in HYPERLINK function in D5

 

  • See the formula in D6,
=HYPERLINK("https://www.google.com", "Google")

Web links in HYPERLINK function in D6

This formula returns a link to go to Google web browser.


How to Link an Image File in Excel

  • To link an image file, click Existing File or Web Page >> Current Folder >> select an image >> enter the link text in ScreenTip >> OK.

Existing File or Web Page for linking Image file

  • The link to open the image file is displayed in D5.

Image file link appears in D5 for linking in excel


How to Link an Email Address in Excel

  • To link an Email Address in Excel, open the Insert Hyperlink dialog box as shown previously >> click Email Adress >> fill E-mail address box >> Text to display >> Subject >> Click OK.

E-mail Address option in Insert Hyperlink dialog

  • The email address with the link is displayed in the cell.

E-mail address link appears in D5 for linking in excel


How to Customize Existing Links Appearance in Excel Worksheet

  • To customize the link appearance, select the links >> go to Home tab >> Cell Styles.

Clicking Cell Styles option in Home tab for changing link appearance

  • Select Linked Cell in Data and Model.

Selecting Linked Cell option in Data and Model group

  • The link text appearance changes.

Link appearance changes in column D


How to Copy or Move Links in Excel

  • To move a link, select the cell with link >> press Ctrl + X to cut the link. Here, D6.

Pressing Ctrl + X keys to cut link

  • Click B6 >> press Ctrl + V to paste the link. The link moved from D6 to B6.

Moving link by pressing Ctrl + V in B6


How to Modify Links for Error Handling in Excel

  • Clicking the link in D6 returns the error alert below.

Warning alert for clicking link in D6

  • In that case, right-click D6 >> go to Edit Hyperlink.
  • Alternatively, you can go to the Link feature in the  Insert tab or press Ctrl + K.

Edit Hyperlink option in context menu

  • Click Place in This Document >> choose a source >> OK.

Fixing link error in Edit Hyperlink dialog for linking in excel

 


How to Remove Links in Excel

  • To remove a link, right-click it >> click Remove Hyperlink.

Remove Hyperlink option in context menu

  • The link is removed from D5.

Removing link in Excel in regards to linking in excel

Read More: Remove Links in Excel


Things to Remember

  • Make sure your calculations in the spreadsheet update automatically with new data insertion: go to the Calculation section on the ribbon, click the arrow by Calculation Options, and choose Automatic.
  • If there’s a broken link in your workbook, fix it: go to Edit Links in Data tab.

Frequently Asked Questions

1. What is a URL and how does it work 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. You can also set links to update automatically when the source data changes.

 


Linking in Excel: Knowledge Hub

<< Go Back To Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo