Step 2: Find columns that can be used to create a path from one table to the next Step 1: Determine which tables to specify in the relationship “Relationships between tables may be needed” More about relationships between tables in ExcelĮxample: Relating time intelligence data to airline flight data For example, if you had a date column in both tables, you would choose that column now.įor Related Table, select a table that has at least one column of data that is related to the table you just selected for Table.įor Related Column (Primary), select a column that has unique values that match the values in the column you selected for Column. Using our customer and time intelligence example, you would choose the customer sales table first, because many sales are likely to occur on any given day.įor Column (Foreign), select the column that contains the data that is related to Related Column (Primary). In a one-to-many relationship, this table should be on the many side. In the Create Relationship box, click the arrow for Table, and select a table from the list. In the Manage Relationships box, click New. If Relationships is grayed out, your workbook contains only one table. Excel can only create the relationship if one column contains unique values.įor example, to relate customer sales with time intelligence, both tables must include dates in the same format (for example, ), and at least one table (time intelligence) lists each date just once within the column.
Verify the column in one of the tables has unique data values with no duplicates. Give each table a meaningful name: In Table Tools, click Design > Table Name > enter a name. Make sure the workbook contains at least two tables, and that each table has a column that can be mapped to a column in another table.ĭo one of the following: Format the data as a table, or Import external data as a table in a new worksheet. For all other cases, you’ll need to create relationships manually. When you import related tables from a relational database, Excel can often create those relationships in the Data Model it’s building behind the scenes.
0 kommentar(er)
