In this tutorial, I will show you how you can link multiple cells from another worksheet in Microsoft Excel. Linking cells between worksheets has a great advantage. Such as if you change data in the source worksheet’s cells, a link will change the related data in the destination worksheet too. Moreover, it helps us to avoid duplication in data. Eventually, linking reduces errors in calculation and saves a lot of time. As a whole, linking multiple cells with another worksheet (from a similar or different workbook) improve data integrity.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
5 Easy Ways to Link Multiple Cells from Another Worksheet in Excel
Luckily, there are several methods available in excel to link multiple cells between worksheets. I will describe some easy and effective methods in the following discussion.
1. Use of ‘Paste Link’ Option to Connect Several Cells from a Different Worksheet
You can use the Paste Link option in excel to link several cells between worksheets. Let’s say I have some people’s names in Sheet1. On the other hand, Sheet2 contains the corresponding ages of the people in Sheet1. Now I will link these ages of Sheet2 with the names of Sheet1 so that I do not have to type ages again in Sheet1.
Steps:
- First, go to Sheet2 and copy the range of multiple cells (here B5:B10).
- Next, come to Sheet1, and put the cursor where you want to paste the data (Here, Cell C5). Now, right-click on Cell C5 and click on the Paste Link icon from the Paste Options (see screenshots).
- To get the Paste Link option, you can follow the following path too: Home > Clipboard > Paste > Other Paste Options > Paste Link icon.
- As a result, you will get the below output. All the ages from Sheet 2 are now pasted to Sheet1 and these data are connected through links. If you change any of the ages in Sheet2, it will be automatically updated in Sheet1.
Read More: How to Link Cells in Same Excel Worksheet (4 Quick Ways)
2. Link Multiple Cells from Another Worksheet Using Excel Formula
You can link a range of cells from another worksheet by using the following formula in excel.
=Sheet_name!First_cell:Last_cell
Let’s follow the below instructions to link sheets using the above formula. For this method, I am using the same dataset that was used for Method 1.
Steps:
- Type the below formula in Cell C5 and press Enter from the keyboard.
=Sheet2!B5:B10
- Consequently, Excel will return the following output. From the below screenshot, you can see that the above formula returns the result as an array. Besides that, the result is linked to multiple cells of Sheet2.
⏩ Note:
You can use the above formula manually. To illustrate, follow the steps.
- Type an equal to (=) sign in Cell C5 of Sheet1.
- Then go to Sheet 2, select the range of cells to whom you want to put the link and press Enter.
- Upon pressing Enter in Sheet2, excel will bring you to Sheet1, and thus two sheets are linked as shown in the below screenshot.
Read More: How to Link Cells in Excel (7 Ways)
Similar Readings
- How to Link Cells for Sorting in Excel (5 Methods)
- Keep Formatting in Excel When Referencing Cells (2 Methods)
- How to Link Tables in Excel (3 Easy Methods)
- Link Excel Sheets to Another Sheet (5 Ways)
- How to Link Files in Excel (5 Different Approaches)
- Link Word Document to Excel (2 Easy Methods)
- How to Link Excel Workbooks (4 Effective Methods)
3. Apply Excel Function to Link Several Cells from Another Worksheet
In this method, I will use the SUM function in excel to link multiple cells between worksheets. Let’s consider in Sheet2, we have some test score data. Now, I want to get the summation of these scores in Sheet1.
Steps:
- Firstly, go to Sheet1, and type the function as shown in the below screenshot. But do not enter the function’s arguments here.
- Next, go to another sheet (Sheet2), select multiple cells (here, B5:B10), and hit Enter.
- As a consequence, excel will direct us to Sheet1 and we got the total scores. Now if you change any of the scores in Sheet2, the total score will change in Sheet1 accordingly.
Read More: How to Link Two Cells in Excel (6 Methods)
4. Use Plus (+) Sign to Connect Multiple Cells from Other Excel Sheet
This time, I will use a plus (+) sign to link several cells from another worksheet in excel. Follow the below steps to do the task. While doing the method, I will use the similar dataset that was used in Method 1 & 2.
Steps:
- First, go to Sheet1, type ‘+’ in Cell B5, but do not press Enter.
- Then go to Sheet2, select the below range of cells, and hit Enter.
- Consequently, excel will take us to Sheet1, and two sheets are linked as displayed in the below screenshot.
Read More: How to Link Cell to Another Sheet in Excel (7 Methods)
5. Link Multiple Cells from Different Excel Workbooks
Till now, I have linked excel worksheets that are located in the same workbook. Let’s consider, in a workbook, I have several fruit names in a sheet named ‘Destination’. Conversely, another workbook named ‘Sales’ has corresponding sales data of fruits in the sheet: Source.
Now I will link the above excel sheets that are from different workbooks. While linking multiple cells of two worksheets from different workbooks, I will use the following generic formula:
=[Workbook_Name.xlsx]Sheet_Name!CellReference
Steps:
- First, go to the workbook that has the worksheet named ‘Destination’ and type the below formula.
=[Sales.xlsx]Destination!$B$5:$B$10
- Then press Enter from the keyboard.
- Finally, upon entering the formula, all the sales data of the ‘Source’ worksheet is linked to the ‘Destination’ worksheet. That means if you change any data in the ‘Source’ worksheet it will dynamically change the data in the ‘Destination’ worksheet too.
Read More: Link Excel Workbooks for Automatic Update (5 Methods)
Turn On Automatic Calculation While Linking Worksheet in Excel
When you link two excel sheets, changing in source worksheet data updates the destination worksheet data automatically. To ensure the automatic data update, make sure you have turned on the Automatic Calculation Options. To do that, go to Formulas > Calculation Options > Automatic.
Read More: How to Link Sheets to a Master Sheet in Excel (5 Ways)
Things to Remember
- While linking worksheets, make sure the cell formatting of both the source and destination worksheet should be similar. Otherwise, after linking, data might be displayed differently, which will cause confusion.
- If you link multiple cells between worksheets from different workbooks that are located in different folders, you have to enter the source workbook’s file path in the linking formula.
Conclusion
In the above article, I have tried to discuss several methods to link multiple cells from another worksheet in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
Related Articles
- How to Link Two Workbooks in Excel (5 Methods)
- Transfer Specific Data from One Worksheet to Another for Reports
- How to Link Data from One Spreadsheet to Another in Excel
- Reference from Another Excel Workbook without Opening (5 Examples)
- How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)
- Link Data in Excel from One Sheet to Another (4 Ways)
- How to reference cell in another Excel sheet based on cell value!