How to Create a Relational Database in Excel (in 3 Steps)

STEP 1 – Build a Primary Table

• Open an Excel worksheet and input your information, as shown in the image below.

NOTE: You can’t leave an entire row or column blank. Doing so may result in errors in the table.

• Select the range B4:C10 and press Ctrl and T together.
• The Create Table dialog box will pop out.
• Press OK

• Select the range and name the table Primary, as shown below.

STEP 2 – Form a Helper Table

• Enter the information for the second dataset in a separate worksheet.

• Press keys Ctrl and T simultaneously after selecting the range B4:C10.
• In the pop-up dialog box, press OK.
• Select the range to name the table as Helper.

STEP 3 – Insert Excel Pivot Table

• Select B4:C10 of the Primary table.
• Go to Insert ➤ Pivot Table.

• A dialog box will appear. Select Primary in the Table/Range field.
• Choose a New Worksheet or an Existing Worksheet. Check the box for your selection, as shown below.
• Press OK.

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

• Go to the All tab.
• Check the box for Net Sales from Table2, which is our Helper table, as shown below.

• A yellow-colored dialog will open asking about the relationships between the tables.
• Choose to CREATE.

NOTE: You can also click the Auto-Detect option.

• The Create Relationship dialog box will pop out.
• Select Table2 (Helper) in the Table box, and choose Primary in the Related Table field.
• Select Salesman in both the Column fields.

• Press OK.
• It’ll return the desired data table in the new worksheet.

Read More: How to Create a Recipe Database in Excel

How to Sort and Filter a Relational Database in Excel

STEPS:

• Click the drop-down icon beside the Row Labels header.
• Choose the option that you would like to perform.

How to Update a Relational Database in Excel

STEPS:

• Choose any cell inside the pivot table or the whole range at first.
• Right-click on the mouse.
• Select Refresh from the options.

• It’ll return the worksheet updating the data.

Download Practice Workbook

Download the following workbook to practice.

Related Articles

• How to Create a Library Database in Excel
• How to Create an Employee Database in Excel
• How to Create a Client Database in Excel
• How to Create Student Database in Excel

<< Go Back To Database in Excel | Learn Excel