How to Add a Conditional Column in Power Query in Excel?

How to Create Relational Tables in Excel?

Last Updated: 30 Jan, 2023

Dhatri

Excel directly doesn’t provide us ready to use a database, but we can create one using relationships between various tables. This type of relationship helps us identify the interconnections between the table and helps us whenever a large number of datasets are connected in multiple worksheets. We can look for or easily find out certain information very quickly.

Create Relational Tables in Excel

We will create two tables: one is the Master table and the other one is the detail table. The master table will have information like customer_id, product, sales channel, and cost. It will act as a primary table and rarely it will undergo a change. The detail table will have information like customer_id, name, and country. Data in the child table (detail table) changes frequently. We will create a relationship between two tables using the common column – customer_id in both fields.

Table 1: Order table: We create an order table with fields customer_id, Product, sales channel, and Cost.

Table 2: Customer table: We create a customer table with fields customer_id, Name, and country.

Create both tables in excel either in the same sheet or as a separate file.

Method 1: By Creating a Pivot Table

Step 1: Go to the insert tab at the top of the ribbon and then Select PivotTable.

Step 2: “Create pivot table” dialog box appears. Here select the orders table in the first selection. (Here we have selected the range in our datasheet). Make sure to check the “add this data to the data model” field and then click OK.

Step 3: Do the same process for the customer table. Visualize the pivot table fields and go to “All view”. Both tables will be displayed. Here we have selected from the ranges so range name (range 2, range 3 in our case) will be displayed.

Step 4: To build a pivot table, select the name from the Customers table. Place it in the Rows area. We can either check the mark by expanding the table or dragging the field to the rows area. Select cost from the Orders table. Place it in the Values area.

Step 5: Pivot the table fields pane showing the notice that “Relationship between the tables is needed”. There are two options available:

We can create the relationship between tables.
Let the excel guess by clicking on “Auto Detect”.

Step 6: In Auto Detect, excel will create a relationship. Click on “manage relationship” to check. By clicking on “Auto Detect,” simple associations with smaller tables and consistent field names may be built automatically. Once done, click on the close button.

Step 7: The pivot table so created will have the customer name from the customer’s table and the total cost from the orders table.

If we want to create a relationship by ourselves, without letting excel do the auto-creation, click on the create button in the pivot table fields.

Step 8: It will open the edit relationship menu. Select the table containing orders as the main Table. (“range” in our case). Select Customer_id for the Column (Foreign). Select the table containing Customers as the Related Table. (“range1” in our case). Select Customer_id as the Related Column (Primary). Click ok to finish the relationship creation.

The pivot table so created will have the customer name from the customer’s table and the total cost from the orders table.

Method 2: By Creating a Relationship between Two Tables

Before creating a pivot table, we can create a relationship between two tables as shown:

Step 1: Go to the Data tab on the top of the ribbon and then to the data tools group. Click on the relationships button.

Step 2: This will open the relationships manage dialog box. All the existing relationships will be displayed with “active” status. We can perform all the options viz. edit, activate, deactivate, delete, auto-detect, etc.

Step 3: Once done, click on close. This will create a table having a similar customer_id from both tables. It will display the customer’s name from the customer’s table and the total cost from the orders table.