Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you work with large data collections, especially those stored in relational databases such as Access or SQL Server, it's likely that your data will be spread amongst several tables. For example you can have a table of sales data that lists each month by its number. January is one, February is two, and so on. If you have the month's number in one table and a list of which month corresponds to which number in another you can often create a relationship between those two tables and in this case use the month's name instead of its number. As an example let's take a look at the data model within this PowerPivot file.
So to do that I will click the PowerPivot tab on the Ribbon and click the PowerPivot Window button to display PowerPivot. When I do, the PowerPivot for Excel window opens and I have two different tables in this data model. I have months and I have data. Let's take a look at the months table first. In this case I have 12 rows and those are the month names in English with the corresponding MonthID number. January is one, February is two, and so on.
In this table the MonthID field contains a unique value in each row and it distinguishes that row from all other rows in the table. That means that it can be called a key field because you can use it to identify a single row within the data collection. Now let's take a look at the same data over in the data table. In this table we have the OrderMonth field and as you can see, not every row contains a unique value. Some orders are from January, which is month one, others from February, month two, and so on.
However, we do know from the way that the calendar is composed that the values in the OrderMonth field will always be the numbers 1 through 12 and that means we can create a correspondence or a relationship between the column OrderMonth in this table and the column MonthID in the other table. In other words we can create a relationship. To do that on the Design tab, in the PowerPivot window we click the Create Relationship button. Then in the Create Relationship dialog box we can select the table in the columns to identify the two fields that contain the same data.
So in this case the Data table contains the OrderMonth field and then the Related Lookup Table is in the Months table and the Related Lookup Column is the MonthID. So again just dropping this down a little bit so we can see the numbers in the OrderMonth column. The OrderMonth column contains the numbers 1 through 12, corresponding to the month in which the order was placed. In the Months table, the MonthID field contains the numbers 1 through 12 and those correspond to the months January, February, March, and so on.
So with this relationship defined we can click Create and PowerPivot creates a relationship between those two tables. So how does this affect us? Well, what it allows us to do is to create a PivotTable and instead of displaying month numbers we can display the month names by using the relationship. So to do that we click the Home tab on the Ribbon and then click the PivotTable button. That puts us back into Excel and we can create the PivotTable on the new worksheet and click OK.
So now we have a new worksheet with a PivotTable and I'll just create a very simple PivotTable based on this data. I will have the StoreID in the Row Labels area and then the OrderMonth below it, and yes, this will show a number. I am going to change that in a bit. I just wanted you to see what it would look like before we use the related table. So we have Store ID, OrderMonth, and then I can put Total in the Values area. So now let's see what happens when I use the MonthName field from the Months table.
So I will remove OrderMonth from the Row Labels area and now I will click the expand button next to the Months table, and then drag MonthName to below Store ID on Row Labels area. When I do the month names appear in the PivotTable but they're sorted into alphabetical order. However, if I click one of the cells that contain a month name and then on the Home tab click the Sort&Filter button and click Sort A to Z, Excel knows to sort them into proper month order because it recognizes the series.
Creating a relationship between two tables lets you expand the types of data you can include in your PivotTable. In this example we used the relationship between the Data and Months tables to change a month's number to its name, which made the summary easier to understand.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98788 Viewers
80 Video lessons · 141646 Viewers
59 Video lessons · 59987 Viewers
52 Video lessons · 73159 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.