Linking multiple worksheets among each other is one of the more important tasks that we have to perform while working with big projects in Excel.
Today I will be showing how to link a data table in Excel from one worksheet to another sheet.
Download Practice Workbook
How to Link a Table in Excel to Another Sheet
Here we’ve got two worksheets called Sheet1 and Sheet2 consisting of the marks of Physics and Chemistry of some students of a school called Sunflower Kindergarten.
Then we have created another worksheet called Sheet3. There we want to import the marks of Physics and Chemistry from Sheet1 and Sheet2 respectively.
We can execute this in two days:
- By Importing the Marks Directly Using the Worksheet Names in Formula.
- By Creating Hyperlinks That will Lead us to the Respective Worksheets.
1. Linking by Using Worksheet Names in Formula
We can link data from one worksheet to another worksheet using the name of the worksheet in formulas along with an exclamation (!) symbol.
The syntax is:
[If you don’t have any space or punctuation marks in your worksheet name.]
Therefore, to import the marks of Physics from Sheet1 to Sheet3, insert this formula in the first cell of Sheet3.
Then drag the Fill Handle to fill the rest of the cells.
You will get the marks of Physics imported from Sheet1 to Sheet3.
Now you can repeat the same for Sheet2 also.
Insert this formula in the first cell of Sheet3.
Then drag the Fill Handle.
You will get the marks in Chemistry imported from Sheet2 to Sheet3.
2. Linking by Creating Hyperlink
i. Creating Hyperlink by Using the HYPERLINK Function
You can also create a link to a worksheet from another worksheet by creating a hyperlink.
When you click the hyperlink, it will directly lead you to the specific worksheet.
To create a hyperlink from Sheet3 to the marks of Physics of Sheet2, insert this formula in Sheet3:
"#Sheet1!C4"indicates that we want to link to the cell C4 of Sheet1 (Marks of the first student in Physics).
- The Hash (#) symbol is important. It indicates that the worksheet is from the same workbook.
- “Marks” is the name of the link that will be displayed. You can use anything you like.
You can then drag the Fill Handle to create a link for all the students.
Click on any of the links. And you will be directed to the cell reference of the worksheet that the link contains.
For example, if you click on the link HYPERLINK(“#Sheet1!C4″,”Marks”), you will be directed to cell C4 of Sheet1.
You can repeat the same procedure for the marks in Chemistry Sheet2. The formula in the first cell in Sheet3 will be:
ii. Creating Hyperlink from the Context Menu
If you want, you can create hyperlinks from the context menu of Excel.
- Select the cell where you want to enter the hyperlink, then right-click on your mouse and choose Hyperlink.
- Click on Hyperlink. You will get a dialogue box called Insert Hyperlink.
To add a hyperlink to a worksheet of the same workbook, Select Place in this Document from the left panel.
In the Text to Display box, enter the name of the link to show. For this example, I enter it as Marks.
Then in the Type the Cell Reference box, enter the cell reference of the cell that you want to link. For this example, it is C4.
And in the Select a Place in the Document box, select the worksheet name to which you want to link. For this example, it is Sheet1.
- Then click OK. You will find a hyperlink called “Marks” that has been created on your selected cell.
- If you click this, you will be directed to the cell that is linked (Cell C4 of Sheet1 in this case).
- If you want, do the same for the rest of the cells.
Using these methods, you can link a data table in Excel to another sheet. Do you know any other method? Or do you have any questions? Feel free to ask us.