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 that will be very useful in any Excel-related task.

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.

1. Using Equal Sign to Link Data from One Excel Sheet to Another

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.

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

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

Read More: Best Practices for Linking Excel Spreadsheets

2. Applying Plus Symbol to Link Data in Excel

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.

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

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

Read More: How to Link Excel Data Across Multiple Sheets

4. Applying SUM Function to Link Data Between Excel Sheets

We can also use the 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:

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

Read More: How to Link Sheets in Excel with a Formula

5. Linking Data from One Sheet to Another 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:

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

Read More: How to Reference Cell in Another Excel Sheet Based on Cell Value

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.

Read More: How to Link Sheets to a Master Sheet in Excel

Download Practice Workbook

You can download the practice workbook from here.

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. If you have any queries, please let me know in the comments.

Related Articles

• How to Link Cell to Another Sheet in Excel
• How to Link Two Sheets in Excel
• How to Link a Table in Excel to Another Sheet

<< Go Back To Excel Link Sheets | Linking in Excel | Learn Excel