Excel is not a database for sure; however, people tend to use it as a database. Since primary versions of Excel, people keep on using it for making the connection between different tables with the help of VLOOKUP, INDEX, MATCH, SUMIF, etc.
Making reports was never an easier task as handling larger datasets is a tricky thing. Excel Data Model is a true blessing that lets you save time using automatic data reports. Here we will talk about how to create a relationship in Excel. Using the Data Model, you can make the connection between two tables and a figure of the relationship in a pivot table side by side.
To understand how to create a relationship between two tables in Excel, let’s start with a quick overview of the relationship.
What is a Relationship?
Merging two different tables result in a relationship:
- Within two tables, you must find a mutual column. However, you don’t need to allocate the same name to the columns.
- The new column must have distinctive entries only.
Hundreds of tables exist in relational databases that make such kinds of relationships. In Excel, you can make a fundamental relational structure using the Data Model option. Suppose an Order data table and a Sales data table, both contain a mutual column State. And both tables have duplicate data in these columns, so you are not allowed to add that column to the new table. You have to skip the State column.
Why Is It Important to Make a Relationship?
Realizing the importance of anything helps you understand why you should use it. Just a single data table is not enough to provide all the information needed to make a report. It often happens in many cases, that’s why the need of creating relationship tables in Excel comes up.
Taking the above example, making a report about a person who marked the order is not likely to be placed in the Sales data table. Making a relationship between two tables Order and Sales just need the Order ID to extract the name from the table to be used in the Sales report.
Note: Remember that PivotTables replace the term “column” with “field.”
The Fundamental Needs
Before understanding how to create a relationship in Excel, let’s understand the basics of this process. For this, you must have Power Pivot and Power Query to complete the process of making an Excel Data Model.
Below you will find out the starting procedure:
How to Get Power Pivot
For Excel 2010, the Power Pivot add-in is needed to be downloaded from Microsoft. Once the downloading is complete, make sure to install it properly on your system.
For Excel 2013, you don’t need to download or install anything manually as the Office Pro Plus edition of Excel 2013 has a built-in Power Pivot. You just need to activate it before using it.
To activate the Power Pivot follow the steps given below:
- Open the Excel workbook and click the Ribbon on the File.
- Click on the Options and you will see a window of Excel Options.
- Next, click on Add-ins.
- On the Manage box, click the drop-down menu and choose COM Add-ins.
- Press Go and choose the checkbox for Microsoft Power Pivot for Excel.
How to Get Power Query (Get & Transform)
- For Excel 2010, similar to the Power Pivot add-in, you need to download the Power Query add-in from Microsoft.
- Once the downloading is complete, you need to install it properly and you will start seeing the Power Query on the Ribbon.
- For Excel 2013, again you don’t need to download and install anything as there is a built-in Power Query. You just have to activate it by following the same steps you did to make Power Pivot functional in Excel 2013.
- For Excel 2016 and Later, you have to go to the Data tab on Excel Ribbon to get the Power Query (Get & Transform).
How to Create a Relationship in Excel – Step-by-Step Guide
- At first, you need to set your data as tables. In the process of making a table, you have to select a random cell in the range and press Ctrl + T. From the design tab, you have to spell out a name for the table.
- Next, you have to open the data ribbon & click on the relationships button.
- To make a new relationship, you have to click on the New.
- Choose Source table & column name. To mark the table and column name, you need to record it. It does not matter what order you are using for making this relationship table. You should never underestimate the power of Excel no matter what.
- Now, you can add more relationships as required.
Things to Consider About Relationship
Keep in mind that VLOOKUPS and making relationships both are alike somehow. Duplicate data columns are needed for Excel to cross-reference rows of one table to the other.
While using relationships, you must keep in mind that there must be similar data types in both columns. For instance, connecting columns of both tables must have the same data type.
Excel 2013 holds up one to one or one to many relationships only. It simply shows that one of the tables is free from duplicate values on the linking columns. For instance, the products table must not contain similar product IDs.
Slicing these Pivot Tables is easier just the way you can do with normal Pivot Tables. For instance, you can further slice the products report on the products prices.
Do You Like Using Relationships?
Well, the relationships feature has replaced many tools and things. People skipped VLOOKUP for most of their analysis. Now, relationships features have become the central part of Excel, which is being used more and more.
You should try using relationship tables and data models in Excel. Elaborate your thoughts about using relationship tables and data models with us. I hope that you got an idea about how to create a relationship in Excel. Now, try it, share it and practice it.
