Database Design for Excel

The last versions of Excel offer some incredible new possibilities to link tables, making the use of VLOOKUP obsolete in a lot of cases. Basically Excel got pimped and offers the same possibilities of linking tables as does a full relational database.

Database Design with Excel

Let's look at how this works !

The Ribbon Data tab contains a 'Relationships' icon.

This icon only make sense if you have two tables that you want to link. In essence you create a third virtual table.

Imagine you created a table 'tblPersons' that contain a 'Country' column (with country codes). If you make a Pivot Table based on that table you'll get the country codes as column or row header. If you decide to show the country names you'll need to link this table to a table that explain those codes.

The next image shows a small 'tblPersons' table at the left, and a 'tblCountries' at the right. The second column of the 'tblCountries' table is the name of the country, while the first also contains the country code.

Each of those country codes ('tblCountries') must be unique for the system to work.

tblPersons tblCountries

Clicking on the 'Relationship' icon will make the 'Create Relationship' dialog appear. If you specify that the Primary Key (with unique values) is the 'CountryCode' column of the 'tblCountries' table and indicate that the 'Country' column of 'tblPersons' contains the related values (Foreign Key) then a valid relationship will have been created upon confirmation.

The final result will be that you'll be able to build Pivot Tables that see both table as one virtual table:

in the Pivot Table Fields dialog you'll be able to pick the 'CountryName' from one table and for instance count the names from the other table.

Of course this is only a glimpse of the possibilities.

For more, look at this link:


Featured Posts
Recent Posts
Search By Tags
Follow Us
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Social Icon

© 2020 by Intermotion Media & Publishing

  • Facebook Social Icon
  • LinkedIn Social Icon

Created with