Relational Databases identify the relations among the information stored across many different data tables. They ease the operations in Excel whenever we have to work with a large number of datasets in multiple worksheets. The relational database helps us to quickly look for and pull out certain information. It can display the same data values in several ways. In this article, we’ll show you the step-by-step procedures to Create a Relational Database in Excel.
Download the following workbook to practice by yourself.
Step by Step Procedures to Create a Relational Database in Excel
Here, we’ll first establish 2 Tables. And then, we’ll form the relationship between the tables. Therefore, follow the steps carefully to Create a Relational Database in Excel.
STEP 1: Build a Primary Table
- First, open an Excel worksheet and input your information like it’s shown in the below image.
NOTE: You can’t keep an entire row or an entire column blank. It may result in errors in the table.
- Then, select the range B4:C10 and press the Ctrl and T keys together.
- As a result, the Create Table dialog box will pop out.
- There, press OK.
- After that, again select the range and name the table Primary like the way it’s shown below.
STEP 2: Form a Helper Table
- Firstly, enter the information for the second dataset in a separate worksheet.
- Now, press the keys Ctrl and T at the same time after selecting the range B4:C10.
- Consequently, in the pop-up dialog box, press OK.
- Again select the range to name the table as Helper.
STEP 3: Insert Excel Pivot Table
- First of all, select B4:C10 of the Primary table.
- Next, go to Insert ➤ Pivot Table.
- As a result, a dialog box will appear.
- There, select Primary in the Table/Range field.
- Then, choose New Worksheet or Existing Worksheet. In this example, choose New Worksheet.
- Subsequently, check the box as shown in the following picture.
- Press OK.
- Hence, it’ll return a new worksheet and on the left side, you’ll see PivotTable Fields.
- Under the Active tab, check the box for Product from Primary and place it in the Rows section as it’s demonstrated in the image below.
- After that, go to the All tab.
- Now, Check the box for Net Sales from Table2 which is our Helper table as you can see in the picture.
- Consequently, a yellow-colored dialog will emerge asking about the relationships between the tables.
- Here, choose to CREATE.
NOTE: You can also click the Auto-Detect option.
- Thus, the Create Relationship dialog box will pop out.
- Select Table2 (Helper) in the Table box, and choose Primary in the Related Table field.
- Afterward, select Salesman in both the Column fields like it’s shown below.
- Press OK.
- Lastly, it’ll return the desired data table in the new worksheet. See the picture below to understand better.
Read More: How to create a database in Excel (make in 8 easy steps)
How to Sort and Filter a Relational Database in Excel
We can sort & filter the database that we have created above. To do this, follow the below steps.
STEPS:
- To carry out Sort and Filter operations, click the drop-down icon beside the Row Labels header.
- Then, choose the option that you will like to perform.
Read More: How to Use Database Functions in Excel (With Examples)
How to Update a Relational Database in Excel
The major advantage of a relational database is that we don’t have to manually update the pivot table. Even if we make changes in the source tables, the pivot table will get updated just by clicking a Refresh option. In this example, to illustrate the process, we replace the Net Sales of Anthony with 20,000. So, follow the steps below to see how to update the Relational Database.
STEPS:
- Choose any cell inside the pivot table or the whole range at first.
- Subsequently, right-click on the mouse.
- Select Refresh from the options.
- At last, it’ll return the worksheet updating the data.
Read More: How to Create a Database That Updates Automatically in Excel
Conclusion
Henceforth, you will be able to Create a Relational Database in Excel following the above-described procedure. Keep using them and let us know if you have any more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Further Readings
- How to Create a Simple Database in Excel VBA
- Maintain Customer Database in Excel
- How to Create Inventory Database in Excel (3 Easy Methods)
- Create a Database with Form in Excel
- How to Create a Searchable Database in Excel (2 Quick Tricks)