How to Link Data in Excel from One Sheet to Another

In this tutorial, I am going to show you 5 easy methods on how to link data in excel from one sheet to another. You can quickly use these methods even in large datasets to link between multiple worksheets. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.


Download Practice Workbook

You can download the practice workbook from here.


5 Easy Methods to Link Data from One Sheet to Another in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 2 columns. Initially, we formatted all the cells containing dollar values in Accounting format. But you can use the methods of this tutorial for other types of data like currency, percentage, time, etc although you might have to do some minor modifications.

how to link data in excel from one sheet to another


1. Using Equal Sign to Link Data

For this first method, we will use the equal sign to link data in excel from one sheet to another which will be relatively easy.

Steps:

  • First, go to sheet Equal2 and type a = inside cell C5 but do not press Enter.

using equal sign to link data in excel from one sheet to another

  • Now, go to sheet Equal1 and select the cells from C5 to C10.

using equal sign to link data in excel from one sheet to another

  • Finally, press Enter and this will link the data of Equal1 to Equal2.


2. Applying Plus Symbol to Link Data

Although you might not expect this to work, we can actually utilize the simple plus symbol to link data in excel from one sheet to another. Let us see this in action.

Steps:

  • To begin with, navigate to sheet Plus2 and type a + symbol inside cell C5.

using plus sign to link data in excel from one sheet to another

  • Then, go to sheet Plus1 and select the cells from C5 to C10.
  • Now, press the Enter key and this should link the Sales column as in the previous method.


3. Linking Spreadsheet Data Using Paste Link Option

The Paste Link feature in excel gives us a quick option to link data from one sheet to another and also for any type of data. Follow the steps below to do this.

Steps:

  • To start with, go to sheet Paste Link1 and copy the cells from C5 to C10.

paste link option to link data in excel from one sheet to another

  • Next, move to sheet Paste Link2 and right-click on cell C5.
  • Now, from the Paste Options, select Paste Link, and immediately you will get the data link from the previous sheet.


4. Utilizing SUM Function to Link Data

We can also use the common SUM function to link data in excel from one sheet to another and perform a math operation as well.

Steps:

  • As previously, click on sheet SUM2 and insert the following formula in cell C10:
=SUM(

sum function to link data in excel from one sheet to another

  • After that, click on sheet SUM1 and select the cells from C5 to C9.

  • Finally, press the Enter key and this will link the data of the two sheets and calculate the Total value inside sheet SUM2.


5. Link Data Based on Condition

In this method, we will see how to link data from one sheet to another in excel using conditions instead of a direct connection.

Steps:

  • For this method, click on sheet Condition2 and type in the formula below inside the cell C5:
=IF(Condition1!C5>=1000,Condition1!C5,"")

  • Then press Enter and copy this formula to the cells below using Fill Handle.
  • Consequently, you should see that this formula has linked the data of the two sheets based on the given conditions.


How to Transfer Data from One Excel Worksheet to Another Automatically

If you want to transfer data from one excel worksheet to another automatically, then VBA will be the best option. Let us see how to use this.

Steps:

  • To begin this method, go to the Developer tab and select Visual Basic.

  • Now, select Insert in the VBA window and click on Module.

  • Next, type in the formula below in the new window:
Option Explicit
Sub Automatic_Transfer()
Dim Smartphone As String, Price As String
Worksheets("VBA1").Select
Smartphone = Range("B5")
Price = Range("C5")
Worksheets("VBA2").Select
Worksheets("VBA2").Range("B4").Select
If Worksheets("VBA2").Range("B4").Offset(1, 0) <> "" Then
Worksheets("VBA2").Range("B4").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Smartphone
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Price
Worksheets("VBA1").Select
Worksheets("VBA1").Range("B5:C5").ClearContents
End Sub

  • Then, click on sheet VBA1 and insert some data in the two columns.
  • Next, open the macro from the Developer tab by clicking on Macros.

  • Now, in the Macro window, select the Automatic_Transfer macro and click Run.

  • As a result, the data from the first sheet should now transfer to the second sheet namely VBA2 and you can fill the table in the same manner.


How to Link Sheets in Excel to a Master Sheet

In this final section, we will discuss a common task to create links between ordinary excel sheets and a master sheet.

Steps:

  • First of all, click on sheet MASTER1 and select cell C5.
  • Now, navigate to the Insert tab and click on Insert Link under Link.

  • Next, in the new window, select Place in This Document and type B5 as Type the cell reference.
  • After that, select the MASTER2 sheet and click OK.

  • Consequently, this should insert the link of the master sheet beside the sheet name.


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to link data in excel from one sheet to another. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo