How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)

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.

Use of ‘Paste Link’ Option to Connect Several Cells from a Different Worksheet

Steps:

  • First, go to Sheet2 and copy the range of multiple cells (here B5:B10).

Use of ‘Paste Link’ Option to Connect Several Cells from a Different Worksheet

  • 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).

Use of ‘Paste Link’ Option to Connect Several Cells from a Different Worksheet

  • 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

Link Multiple Cells from Another Worksheet Using Excel Formula

  • 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.

Link Multiple Cells from Another Worksheet Using Excel Formula

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


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.

Apply Excel Function to Link Several Cells from Another Worksheet

Steps:

  • Firstly, go to Sheet1, and type the function as shown in the below screenshot. But do not enter the function’s arguments here.

Apply Excel Function to Link Several Cells from Another Worksheet

  • Next, go to another sheet (Sheet2), select multiple cells (here, B5:B10), and hit Enter.

Apply Excel Function to Link Several Cells from Another Worksheet

  • 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.

Apply Excel Function to Link Several Cells from Another Worksheet

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.

Use Plus (+) Sign to Connect Multiple Cells from Other Excel Sheet

Steps:

  • First, go to Sheet1, type ‘+’ in Cell B5, but do not press Enter.

Use Plus (+) Sign to Connect Multiple Cells from Other Excel Sheet

  • Then go to Sheet2, select the below range of cells, and hit Enter.

Use Plus (+) Sign to Connect Multiple Cells from Other Excel Sheet

  • Consequently, excel will take us to Sheet1, and two sheets are linked as displayed in the below screenshot.

Use Plus (+) Sign to Connect Multiple Cells from Other Excel Sheet

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.

Use Plus (+) Sign to Connect Multiple Cells from Other Excel Sheet

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

Use Plus (+) Sign to Connect Multiple Cells from Other Excel Sheet

  • 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.

Turn On Automatic Calculation While Linking Excel Worksheet

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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo