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.


Create Tables

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



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])




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)


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:


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 :)


Skip to comment form

    • Akenji on 21/08/2018 at 19:10
    • Reply

    I want to link two tables one containing the standing of teams in a soccer league and the other table containg the scores of the various days of play of the teams, in such a way that , when I enter the scores in the it automatically updates the table containing the Standing in terms of points and goal differences, three points for a match won, one point for a match drew and off course no point for loses. It would be nice to have a practical idea about how to link these two tables together, thanks.

    • Chris on 15/10/2014 at 16:41
    • Reply

    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:

    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.

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

    • Mahmoud on 12/04/2014 at 22:23
    • Reply

    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?

    • Thomas Cooper on 21/03/2014 at 16:58
    • Reply


Leave a Reply

Your email address will not be published.