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.
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.
Link Tables in Excel: 3 Quick Ways
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.
Sheet2 contains the Order ID Table. This table has 4 columns. These are; Order ID, Product Name, Month & Sales.
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.
- Secondly, go to the INSERT tab and select Table.
- Thirdly, a Create Table window will occur. Make sure ‘My table has headers’ is selected.
- Clicking OK will convert your dataset into a table just like below.
- Now, follow the above procedure to make an Order ID Table.
- Go to the DESIGN tab and change the name of the tables. We have changed Table1 to Sales and Table2 to Order.
- Next, go to the INSERT tab and select Pivot Table.
- After that, Create PivotTable window will occur. Select ‘New Worksheet’ and ‘Add this data to the Data Model’ Do this for both tables.
- 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.
- Finally, hit OK and a linked table will appear.
Read More: How to Link Multiple Cells from Another Worksheet in Excel
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.
- After selecting Go, a COM Add–Ins will open. Select ‘Microsoft Office PowerPivot for Excel 2013’ from there and click OK.
- Now, select the range of the data from your table.
- Then, go to POWERPIVOT ribbon and select Add to Data Model.
- Next, PowerPivot for Excel window will appear. Do the above steps for the Order Table.
- After that, go to Design and select Create Relationship.
- 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
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.
Download Practice Book
Download the practice book.
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.
