In Microsoft Excel, linking cells is one of the crucial tasks. You will face many instances where you may have to connect cells. When cells are linked, you can notice the change in destination cells after a change in source. It helps us to perform different operations. In this tutorial, you will learn to link cells in Excel with suitable examples and proper illustrations. So, let’s get into it.
Download Practice Workbooks
Download these practice workbooks.
Why do We Need to Link Cells in Excel?
When you create a link between cells or external cell reference 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.
Procedures to Link Cells in Excel
Before we start, let me clarify that there are three methods or procedures to link cells from the source worksheet to the destination worksheet. Here are these three:
- By typing the Cell references
- Using the Mouse directly
- Using the Paste Link command from Paste Special
Most of the time, we are going to use option 3. But, you can choose an option. It will work as same.
7 Ways to Link Cells in Excel
In the following sections, we will provide you with seven ways to create a link between cells in Excel. We recommend you learn and apply all these methods to your worksheet. It will definitely develop your Excel knowledge.
1. Link Two Cells in Same Worksheet
Now, creating a link between two cells is pretty easy. You have to type the cell reference in the destination cell that refers to another cell.
Have a look at the following screenshot:
Here, 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.
Now, to create a link between these cells, simply type the cell reference of the total bills:
After pressing Enter button, you will see the following:
As you can see, both cells show the same values. To verify, let’s make any change in the source dataset:
Here, you can see we made a change in the Transportation bill. For this reason, Total Bills changed. Also, you can see that our linked cell was also modified. So, clearly, we made a link between these cells in the Excel worksheet.
Read more: How to Link Two Cells in Excel (6 Methods)
2. Link Cells Between Multiple Worksheets
Now, linking cells between two different worksheets is pretty simple. Here, you have to enter also the cell reference along with the worksheet name. Take a look at the following screenshot:
Here, we have two different worksheets. Our source worksheet is Sales and our destination sheet is Link Sales.
Now, from these two sheets, we will create a link between Cell C10 of Sales and Cell C4 of Link Sales sheet.
- First, copy the total sales from the Sales sheet.
- Next, go to the Link Sales sheet.
- Now, right-click on Cell C4 and click on Paste Link like we showed (You can also find it to Paste Special option).
As you can see, our destination cell is linked to the source sheet. Now, any modification in the source worksheet will reflect in the destination worksheet.
3. Link Range of Cells in Excel
Now, you can link multiple ranges of cells to a different worksheet. Here, we are also going to use the Paste Special command of Excel to create a link between a range of cells.
Have a look at these screenshots:
Dataset from the sheet “RangeOfCells”
And we want to link this with the sheet “LinkRangeOfCells”
- At first, copy the range of cells from the sheet “RangeOfCells”.
- Now, go to the sheet “LinkRangeOfCells”.
- Now, right-click on Cell C5.
- From the Paste Options, click on Paste Link. You can also find it in Paste Special command.
As you can see, our range of cells is linked with the source worksheet. Now, any change in the source sheet will reflect in the destination sheet.
4. Link Cells in a Function
We use cell references in the functions. Also, we use cell references to link cells. So, we can mix these two in the function to create a link between cells in different worksheets.
Take a look at the following screenshot:
Here, we have two different sheets. Now, we will use the SUM function to add the total selling price in sheet “LinkFunction” from the sheet “Function”.
Here, we are going to use the Mouse to select the cell references.
- First, type the SUM function but do not give any cell reference yet.
- Now, go to the sheet “Function”.
- From here, select the range of cells C5:C9 and press Enter.
As you can see, we got the total selling price using the SUM function that linked to another worksheet.
If you are working with Microsoft Excel for a while, you already know the importance of the Hyperlink. By creating a Hyperlink in Excel, You can link cells, sheets, workbooks, links with this.
Take a look at the dataset:
Here, we will link one cell with another using the Hyperlink.
- First, create a list to insert hyperlinks. From this list, we will link Cell G5 with Cell D16 and Cell G6 with Cell E16.
- After that, click on any cell of that list.
- Now, press Ctrl+K to open the Insert Hyperlink dialog box.
Now, look carefully. First, click on the option Place in This Document option. Second, type the name of the cell in the Text to display. Third, select the sheet name of your workbook. Fourth, insert the cell reference where you want to go in the Type the cell reference box. Finally, click on OK.
- It will create a hyperlink in our list.
- In the same process, create another hyperlink to jump into cell E16 (Total Profit).
- Now, when you click any of those links, it will take you to the desired cell.
As you can see, this method successfully created a link between those cells in Excel.
6. Link Between Two Workbooks
You can also link cells of two separate workbooks. Here we have two workbooks. Have a look at these:
And this is another workbook:
- First, open both Excel workbooks.
- Next, go to the Source Workbook.
- Now, copy the total sales from this workbook.
- Then, go to the “Another Workbook.xlsx”.
- Next, right-click on Cell C4 and click on Paste Link.
Finally, our destination cell is linked to the source workbook. Now, any modification in the source workbook will reflect in the destination workbook.
7. External Reference to Another Workbook
When you close the original Excel file after establishing a link, you will see the this kind of reference:
=’D:\SOFTEKO\70- how to link cells in excel\[Another Workbook.xlsx]External Reference’!$C$10
Basically, it shows the file location on your device. Now, if you change the folder location, it will show an error. Also, 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. Or you can modify the folder location or the file name.
How to Break External Links in Excel
So, you want to break links between two different excel files and want to keep the values only. Follow these steps to break external links :
- First, go to the sheet where you created a link.
- Then, click on the cell.
- After that, go to the Data tab.
- From the Queries & Connections group, click on Edit Links.
- Now, from the Edit Links dialog box, click on Break Link. Another dialog box will pop up, click on Break Links.
As you can see, our cell doesn’t contain any links in it. It is only showing the value instead.
💬 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.
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to link cells in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!