How to Link a Table in Excel to Another Sheet (2 Easy Ways)

HYPERLINK Function to Create Hyperlink to Another Sheet in Excel

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.

Data Set to Link Table to Another Sheet in Excel

Then we have created another worksheet called Sheet3. There we want to import the marks of Physics and Chemistry from Sheet1 and Sheet2 respectively.

New Worksheet Opened in Excel

We can execute this in two days:

  1. By Importing the Marks Directly Using the Worksheet Names in Formula.
  2. 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:

=Worksheet_Name!Cell_Reference 

Or

='Worksheet_Name'!Cell_Reference

[If you don’t have any space or punctuation marks in your worksheet name.]

='Worksheet_Name'!Cell_Reference
[If you have any space or punctuation marks in your cell reference.]

Therefore, to import the marks of Physics from Sheet1 to Sheet3, insert this formula in the first cell of Sheet3.

=Sheet1!C4

Then drag the Fill Handle to fill the rest of the cells.

You will get the marks of Physics imported from Sheet1 to Sheet3.

HYPERLINK Function to Link to Another Worksheet in Excel

 

Now you can repeat the same for Sheet2 also.

Insert this formula in the first cell of Sheet3.

=Sheet2!C4 

Then drag the Fill Handle.

You will get the marks in Chemistry imported from Sheet2 to Sheet3.

HYPERLINK Function to Link to Another Worksheet in Excel


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:

=HYPERLINK("#Sheet1!C4","Marks")
  • Here, "#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.

HYPERLINK Function to Create Hyperlinks Between Worksheets

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:

=HYPERLINK("#Sheet2!C4","Marks")

HYPERLINK Function to Create Hyperlink to Another Sheet in Excel


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.

Selecting Hyperlink from Context Menu

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

Insert Hyperlink Dialogue Box in Excel

 

  • Then click OK. You will find a hyperlink called “Marks” that has been created on your selected cell.

Hyperlink Created through Context Menu in Excel

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

Conclusion

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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo