Why Do We Need to Link Cells in Excel?
When you create a link between cells or external cell references in a sheet, it helps us maintain the same data in different worksheets. It is a matter of data integrity. One simple modification in the source dataset creates a change in the destination file. When we have multiple datasets in our workbook but need a summary, we use this kind of cell reference in Excel.
How to Link Cells in Excel: 7 Ways
Method 1 – Link Two Cells in the Same Worksheet
We’ll use a simple dataset as an example.
- We have a dataset of bills. We have a cell that indicates the total bills. On the right-hand side, we created another field to link the cell with the total bills.
- In the result cell, type = and then type in the cell reference you want to put by its row and column identifier or click on the cell.
- After pressing Enter, you will see the following:
- Both cells show the same values.
- Change a cell in the source, and the value in C8, which is perpetuated to the cell we made a link in.
Method 2 – Link Cells Between Multiple Worksheets
We have two different worksheets. Our source worksheet is Sales and our destination sheet is Link Sales. We will create a link between cell C10 of Sales and cell C4 of the Link Sales sheet.
Steps
- Copy the total sales from the Sales sheet.
- Go to the Link Sales sheet.
- Right-click on Cell C4 and click on Paste Link (under the Paste Special option).
Read More: Link Multiple Cells from Another Worksheet
Method 3 – Link a Range of Cells in Excel
Dataset from the sheet RangeOfCells.
We want to link this with the sheet LinkRangeOfCells.
Steps
- Copy a range of cells from the sheet RangeOfCells.
- Go to the sheet LinkRangeOfCells.
- Right-click on Cell C5.
- From the Paste Options, click on Paste Link.
Method 4 – Link Cells in a Function
We have two different sheets. We will use the SUM function to add the total selling price in the sheet LinkFunction from the sheet Function.
Steps
- Type the SUM function but do not provide a cell reference yet or close the function.
- Go to the sheet Function.
- Select the range of cells C5:C9 and press Enter.
Method 5 – Link Cells Using Hyperlink
Consider the following dataset, where we’ll create a hyperlink to go to a specific cell.
Steps
- Create a list to insert hyperlinks. We will link Cell G5 with Cell D16 and Cell G6 with Cell E16.
- Click on any cell of that list.
- Press Ctrl + K to open the Insert Hyperlink dialog box.
- Click on the option Place in This Document.
- Type the name of the cell in the Text to display.
- Select the sheet name of your workbook.
- Insert the cell reference where you want to go in the Type the cell reference box.
- Click on OK.
- Repeat for another hyperlink in cell G6 to jump into cell E16 (Total Profit).
- When you click any of those links, it will take you to the desired cell.
Method 6 – Link Between Two Workbooks
You can also link cells of two separate workbooks. Here we have two workbooks. Here’s the first workbook.
And this is the other workbook.
Steps
- Open both Excel workbooks.
- Go to the Source Workbook.
- Copy the total sales from the workbook.
- Go to Another Workbook.xlsx.
- Right-click on Cell C4 and click on Paste Link.
Method 7 – External Reference to Another Workbook
When you close the original Excel file after establishing a link, you will see this kind of reference:
=’D:\SOFTEKO\70- how to link cells in excel\[Another Workbook.xlsx]External Reference’!$C$10
It shows the file location on your device. If you change the folder location, it will show an error. If you rename the file, the link will stop working.
If you want to link the external Excel file again, you have to open the file and create a link again.
How to Break External Links in Excel
Steps
- Go to the sheet where you created a link.
- Click on the cell.
- Go to the Data tab.
- From the Queries & Connections group, click on Edit Links.
- From the Edit Links dialog box, click on Break Link.
- Another dialog box will pop up.
- Click on Break Links.
Things to Remember
✎
Your external links won’t work if you change the location or the name of the file.
✎
Also, you can manually type the cell references to link cells.
✎
Break Link method won’t work for the links present in the same worksheet.
✎
You can also copy one worksheet to another workbook without reference.
Download the Practice Workbooks
Excel Link Cells: Knowledge Hub
- Link Cells in Same Excel Worksheet
- Link Multiple Cells
- Link Cells for Sorting
- Link Tables
- Keep Formatting When Referencing Cells
- Automatically Link a Cell Color to Another
- Stop Cell Mirroring
<< Go Back To Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!