How to Create a Relational Database in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Relational Database in Excel



How to Create a Relational Database in Excel: With Easy Steps

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 database that is relational in Excel.


STEP 1: Build a Primary Table

  • First, open an Excel worksheet and input your information like the below image.

Build a Primary Table for Creating a Relational Database

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.

Build a Primary Table for Creating a Relational Database

  • 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.

 Form an Excel Helper Table

  • 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.

Create a Relational Database by Inserting Excel 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.

Create a Relational Database by Inserting Excel Pivot Table

  • 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.

Create a Relational Database by Inserting Excel Pivot Table

  • 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.

Create a Relational Database by Inserting Excel Pivot Table

  • Consequently, a yellow-colored dialog will emerge asking about the relationships between the tables.
  • Here, choose to CREATE.

Create a Relational Database by Inserting Excel Pivot Table

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 Recipe Database in Excel


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.


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 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.

Update a Relational Database in Excel

  • At last, it’ll return the worksheet updating the data.


Download Practice Workbook

Download the following workbook to practice by yourself.


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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

<< Go Back To Database in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo