How to Link Data in Excel from One Sheet to Another (5 Methods)

Dataset Overview

In this tutorial, we’ll explore five methods for linking data between Excel sheets. We’ll use a concise dataset with approximately 6 rows and 2 columns. Initially, we’ve formatted all the cells containing dollar values in Accounting format, but you can adapt these methods for other data enters like currency, percentage, and time with minor adjustments.

Method 1 – Using Equal Sign

• Open the sheet named Equal2.
• In cell C5, enter an equal sign (=) but do not press Enter.
• Switch to the sheet named Equal1.
• Select the cells from C5 to C10.

• Press Enter to link the data from Equal1 to Equal2.

Method 2 – Applying Plus Symbol

• Go to the sheet named Plus2.
• Enter a plus symbol (+) in cell C5.

• Navigate to the sheet named Plus1.
• Select the cells from C5 to C10.
• Press Enter to link the Sales column as in the previous method.

Method 3 – Using the Paste Link Option

• Open the sheet named Paste Link1.
• Copy the cells from C5 to C10.

• Switch to the sheet named Paste Link2.
• Right-click on cell C5.
• From the Paste Options, select Paste Link to create an immediate data link from the previous sheet.

Method 4 – Applying SUM Function

• Click on the sheet named SUM2.
• Insert the following formula in cell C10:
`=SUM(`

• Go to the sheet named SUM1.
• Select the cells from C5 to C9.

• Press Enter to link the data between the two sheets and calculate the Total value in sheet SUM2.

Method 5 – Linking Data from One Sheet to Another Based on a Condition

In this method, we’ll explore how to link data from one sheet to another in Excel using conditions instead of a direct connection.

• Open the sheet named Condition2.
• Enter the following formula inside cell C5:
`=IF(Condition1!C5>=1000,Condition1!C5,"")`

• Press Enter and then copy this formula to the cells below using the Fill Handle.
• You should see that this formula links 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, VBA (Visual Basic for Applications) is the best option. Here’s how to use it:

• Go to the Developer tab and select Visual Basic.

• Click on Insert in the VBA window and choose Module.

• Enter the following VBA code 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``````

• Click on the sheet named VBA1 and insert some data in the two columns.
• Open the macro from the Developer tab by clicking on Macros.

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

• The data from the first sheet should now transfer to the second sheet (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, let’s discuss how to create links between ordinary Excel sheets and a master sheet:

• Click on the sheet named MASTER1 and select cell C5.
• Navigate to the Insert tab and click on Insert Link under Link.

• In the new window, select Place in This Document and enter B5 as the cell reference.
• Next, select the MASTER2 sheet and click OK.

• This should insert the link of the master sheet beside the sheet name.

You can download the practice workbook from here:

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF