How to Link Tables in Excel (3 Easy Methods)

Excel Table helps us to represent our data easily. Sometimes, we need to Link Tables in Excel. We can do this in the same worksheet as well as from different worksheets. Linking tables in excel always saves time and make the calculations easy. In this article, we will learn how to link tables in Excel.


Download Practice Book

Download the practice book.


Why Linking Tables?

Sometimes, we need to know a portion of information from any big dataset. Linking tables gives you the advantage of maintaining a large dataset quickly. It also helps us to manage any relationships easily and build charts. Most importantly, organizing datasets becomes easier.


3 Quick Ways to Link Tables in Excel

1. Link Tables Using Pivot Tables in Excel

We will link tables using the pivot tables in this method. In our dataset, we will use two different tables from two different sheets. Sheet1 contains Sales Table. This table has 3 columns. These are; Salesman, Product Name & Region.

Link Tables Using Pivot Tables in Excel

Sheet2 contains the Order ID Table. This table has 4 columns. These are; Order ID, Product Name, Month & Sales.

Link Tables Using Pivot Tables in Excel

Follow the steps below to know about this method.

STEPS:

  • First, we need to convert our dataset into a table. To do that, select the range of cells in your dataset. We have selected the cells from B4 to D10.

Link Tables Using Pivot Tables in Excel

  • Secondly, go to the INSERT tab and select Table.

Link Tables Using Pivot Tables in Excel

  • Thirdly, a Create Table window will occur. Make sure ‘My table has headers’ is selected.

Link Tables Using Pivot Tables in Excel

  • Clicking OK will convert your dataset into a table just like below.

Link Tables Using Pivot Tables in Excel

  • Now, follow the above procedure to make an Order ID Table.

Link Tables Using Pivot Tables in Excel

  • Go to the DESIGN tab and change the name of the tables. We have changed Table1 to Sales and Table2 to Order.

Link Tables Using Pivot Tables in Excel

  • Next, go to the INSERT tab and select Pivot Table.

Link Tables Using Pivot Tables in Excel

  • After that, Create PivotTable window will occur. Select ‘New Worksheet’ and ‘Add this data to the Data Model’ Do this for both tables.

Link Tables Using Pivot Tables in Excel

  • PivotTable Fields window will open. Select the columns you want to link from this window. And then select Create.

  • Here, the Create Relationship window will open. Select the tables and columns you want to use for your relationship.

Link Tables Using Pivot Tables in Excel

  • Finally, hit OK and a linked table will appear.

Link Tables Using Pivot Tables in Excel

Read More: How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)


Similar Readings:


2. Apply Power Pivot to Link Tables

Excel is a powerful tool when it comes to data analysis. The Power Pivot feature of excel gives us the opportunity to link tables easily.

Observe the steps below to know everything about this method.

STEPS:

  • To use this method, you need to activate the Power Pivot feature first. To do that, go to the FILE tab and select Options.
  • Next, the Excel Options window will appear. Go to the Add-Ins and select COM Add-ins Then, select Go.

Link Tables Using Power Pivot

  • After selecting Go, a COM AddIns will open. Select ‘Microsoft Office PowerPivot for Excel 2013’ from there and click OK.

  • Now, select the range of the data from your table.

Link Tables Using Power Pivot

  • Then, go to POWERPIVOT ribbon and select Add to Data Model.

Link Tables Using Power Pivot

  • Next, PowerPivot for Excel window will appear. Do the above steps for the Order Table.

Link Tables Using Power Pivot

  • After that, go to Design and select Create Relationship.

Link Tables Using Power Pivot

  • Select the Table and Related Lookup Table for making the linked table. You will have to use the same column in both tables for creating a relationship.

  • Now, go to Home and select PivotTable.

  • Create PivotTable window will occur. Select where you want to create the pivot table. We selected New Worksheet for this purpose. You can also select Existing Worksheet.

  • Finally, click OK and you will see the new table.

Read More: How to Link Cells in Same Excel Worksheet (4 Quick Ways)


3. Link Multiple Tables Manually

We can also link tables manually. It is very effective when we are working with a small dataset. We will use the previous tables for this method. The Sales column of the Order ID table will be added to the Sales table.

Pay attention to the steps for more.

STEPS:

  • In the beginning, add a Sales column beside the Region This new column will be automatically added to the existing table.

  • Secondly, type the formula.
=Sheet2!E5

Here, this formula will link the E5 cell from the Order ID table to our Sales table.

  • Finally, hit Enter and the whole column will be linked in the table.


Things To Remember

To link tables using the pivot table method, we need to have a common column in all tables. Otherwise, we can not create relationships. The PowerPivot feature is available from Excel 2013 versions. So, if you are using older versions, you can try the manual method.


Conclusion

Here, I have discussed 3 quick methods of linking tables easily in excel. These methods will also help you to know about the Pivot Table and its different features. Hope this article will help you to link tables in Excel. Last of all, if you have any queries or suggestions, feel free to comment below.


Further Readings

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo