Relationship between tables

With Excel 2013, you can create one or several relationships between tables without using any formula.

 

Relationship include in the tool bar

In Excel 2013, a new feature have been added to the Data menu, it is the Relationships button.

Relation_Between_Tables_1

Create Tables

You must create at least 2 tables in your workbook in order to use this feature.

Relation_Between_Tables_2

 

Tables have made their appearances since Excel 2007. But honestly, as they made through the use of automated formats, they were hardly used.
Excel 2013 will allow you to link multiple tables between. For more than shaping convenience, tables also automatically generated names for the table but names all columns.

 

 

Tables have the advantage of making your dynamic data ranges. Simply enter in the document data using the formula below to calculate the sum of the Total column. Use your SUM formula if you need to add information to your table “Customer”: it will automatically adjust to new lines.

= SUM (Customer [Total])

Relation_Between_Tables_3

 

 

Converting one table in many tables

Often, Excel workbooks looks like this, a long list of data with the same information (in this example, City and State are repeated)

Relation_Between_Tables_4

A customer lives in one city, and a city belongs to a single state. So rather than repeat for each client, the names of his city and state, we will create two other tables with only cities and states name as follows:

Relation_Between_Tables_5

Besides the fact that your workbook will be smaller, and thus faster to open, split your main table in 3 tables will greatly decrease data entry errors :)


4 comments

Skip to comment form

  1. Chris

    Is this feature broken in Excel 2013? I am being told the relationship cannot be created because of duplicate values in both columns. Neither column has duplicate values. I used the duplicate checker to confirm that, but It’s also obvious from the test data.

    Here’s a setup similar to what I’m doing:

    Table:Car
    Column (Foreign): OwnerID
    Related Table: Owner
    Related Column (Primary): OwnerID

    My sample data has seven rows in Car and Car.OwnerID has values 1 through 7. There are 15 rows in Owner and Owner.OwnerID has values 1 through 15.

  2. Suysha A. Chouhan

    nicely explained every thing but still can create confusion and imbrogalio in your mind.

  3. Mahmoud

    I want to compare two tables on is of 28,000 people to be paid 100$ the second table is of 26,500 people who were actually paid out of the planned 28000. I want to find out people who were not paid. every person has a ID number but it is not unique which makes the process very difficult and confusing. can anybody help me?

  4. Thomas Cooper

    :-))

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>