Creating Relational Databases in Excel 2013 – What To Know

In order to create relational databases using Excel 2013, there are actually some factors to remember in order to correctly create these databases. They can be summarised into these points. Do you know what these factors are?

Relational databases are databases that are specifically structured to recognise relations in the data stored inside. This is the type of database usually used for large amounts of business information, built for easy information retrieval, error reduction and other useful organising factors.

When relational databases are used, people often do so by creating a master table and a child table. A master table, mainly the primary table, is usually the table containing unique information records and rarely changed. More tables can be linked to a master table as long as the user requires it. Child tables, or detail tables, are the tables that are linked to the master table, where their data are constantly changed.

If you have more than one child table, make sure you can avoid the confusion of repeating all the same master information by choosing to create relationships with only one unique fields for example- ID and other related aspects.

To further elaborate on relational databases, the most realistic example would be keeping track of sales. A master table would usually be the table that contains personal information on individual employees ( their names, locations, addresses etc). The child/detail table would most likely contain the number of products/sign ups they have attained and other relevant information that can be constantly updated. Based on these tables, you can use the relational databases to generate reports based on total yearly sales per individual and total by city, and other reports based on your requirements.

Relational databases are typically created with an identified factor as a relational field, the essential factor that works to connect both master and child tables together.

Recommended steps:

The very first step users should take when creating relational databases would be to key in information and requirements that are designed for the Master table. Once the information are all inputted, you can then choose to create the table by selecting Styles and choosing Format as Table. You will be prompted to choose colours, and formats. Make sure to label your Master table clearly by choosing the My Table has headers option. Choose the design tab under table tools and look for properties. Under the table name box, type in your Master table’s name. Now repeat the same steps accordingly to create a detail table and rename it.

After your master table and detail table are created, you should now set relationships using pivot table. Bear in mind that this is to define table relationships within the pivot table report section, failing to do this first would result in an unsuccessful attempt when creating relational connections. To begin, highlight the table cells of the detail table and choose Insert before locating the Pivot Table button. Make sure that the right table range is selected.

Now choose where the results of the Pivot report should be located. Choose existing worksheet if you want the Pivot Report to be right beside the detail table. If you want multiple detail tables to be analysed, choose add this data to data model. Select the relevant boxes using the Pivot table fields and choose All. Then click on the master table link and a yellow box prompting relationships to be defined would appear.

Choose Create and locate the table section in the dialog box that appears. Choose drop down arrow and select the relevant table. Choose the relational field under the column tab. Keep in mind that you will only have one relational field so when prompted for the rest of the detail tables, make the same choices and you should be able to successfully create a relational database in Excel 2013.