Today I will be showing how to link a table in Excel from one worksheet to another sheet. As we know, linking multiple worksheets among each other is one of the more important tasks that we have to perform while working on big projects in Excel. In this article, I will be covering some ways to deal with these situations. So let’s get started.
How to Link a Table in Excel to Another Sheet: 2 Useful Methods
In this section, I will show 2 effective methods for linking a table in Excel to another sheet. Before jumping into those methods, let’s see an example where we need to link a table in Excel to another sheet. Here we’ve got two worksheets called Sheet1 and Sheet2. In Sheet1, we have the marks of Physics of some students of a school called Sunflower Kindergarten.
In Sheet2, we have the marks of Chemistry of the same students.
Then we created a table in another worksheet where we want to link the marks of Physics and Chemistry from Sheet1 and Sheet2 respectively.
Now, let’s use our 1st method to link the table to Sheet1 and Sheet2.
1. Use of Worksheet Names in Formula to Link Table
We can link data from one worksheet to another worksheet using the name of the worksheet in formulas along with an exclamation (!) symbol. To know more follow the steps below.
- First, go to the worksheet where you have the data table.
- Then select the top cell of a column. In this case, I am selecting cell C5 (the first cell under the Physics column).
- Now, In this cell, we want to link the C5 cell from Sheet1 as it contains the corresponding Physics marks of the student (Richard Kettlebrough) in this row. To do that, write an equal sign(=) on the cell, then go back to Sheet1 and select C5 from there, and click Enter.
- As a result, you will see the value of C5 from Sheet1 in this cell.
- In this way, the cell C5 of this table has become completely linked with the cell C5 from Sheet1. Hence, if we make any change to the Sheet1 C5 cell, then the change will also reflect on this cell.
- Now, we can use the Fill Handle to autofill the rest of the Physics column cells.
- We can do the same thing for Chemistry.
- Here, alternatively, we can also manually write down the Sheet name in the formula. As we have seen above, the syntax will be
- If you have any space or punctuation marks in your referred worksheet’s name, then you must enclose the worksheet name within a single quotation..
- However, we can do a similar processing on a table To make the above data table to an Excel table, first, select the total data set then click on Ctrl+L.
- Now, on the popped-up window, select My table has headers option and click Ok. As a result, the whole dataset will be converted into an Excel Table. This table will contain previously linked cells of other sheets.
In this way, we can link a table to another sheet.
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. You can create hyperlink either by using the HYPERLINK function or by using the context menu. Below, I will describe both of the methods.
The HYPERLINK function is a very useful function when it comes to linking another sheet’s cell to a cell. To know how we can use the HYPERLINK function to link a table in Excel, follow the steps below.
- To create a hyperlink with the marks of Physics of Sheet2, insert this formula in cell C5 and press Enter.
- “#Sheet1!C5” indicates that we want to link to the cell C5 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.
- Now, you can drag the Fill Handle to create a link for all the students.
- Now, if you click on any of the linked cells, it will lead to Sheet1.
- Now, you can repeat the same procedure for the marks in Chemistry Sheet2. The formula in the first cell under the Chemistry column will be:
- And the final result will be like this.
Read More: How to Link Sheets in Excel with a Formula
If you want, you can create hyperlinks from the context menu of Excel as well. To do that, follow the steps below.
- Select the cell where you want to enter the hyperlink, then right-click on your mouse and choose Link.
- Consequently, 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 C5.
- 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 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 C5 of Sheet1 in this case).
- Now, if you repeat the same process for the rest of the cells you will get the following results.
- Now, if we click on any linked cell, we will be directed to the sheet with which the cell is linked.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
This is the end of the article regarding how to link a table in Excel to another sheet. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries.