How Can I Link Data From One Excel Spreadsheet To Another?

Accept you always used VLOOKUP to bring a column from 1 table into another table? Now that Excel has a born Data Model, VLOOKUP is obsolete. You can create a relationship between two tables of information, based on matching data in each table. Then you lot tin create Ability View sheets and build PivotTables and other reports with fields from each table, even when the tables are from dissimilar sources. For case, if you have client sales data, you might want to import and chronicle time intelligence data to analyze sales patterns by year and month.

All the tables in a workbook are listed in the PivotTable and Power View Fields lists.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

When you import related tables from a relational database, Excel can often create those relationships in the Data Model it's building backside the scenes. For all other cases, you'll need to create relationships manually.

Make sure the workbook contains at to the lowest degree 2 tables, and that each tabular array has a column that can be mapped to a cavalcade in some other table.
Do one of the following: Format the data as a table, or Import external data as a tabular array in a new worksheet.
Requite each table a meaningful proper noun: In Table Tools, click Blueprint > Table Name > enter a name.
Verify the column in ane of the tables has unique data values with no duplicates. Excel can only create the relationship if ane column contains unique values.

For example, to chronicle customer sales with fourth dimension intelligence, both tables must include dates in the same format (for example, ane/i/2012), and at to the lowest degree 1 table (fourth dimension intelligence) lists each date just one time within the column.
Click Data > Relationships.

If Relationships is grayed out, your workbook contains only one table.

In the Manage Relationships box, click New.
In the Create Relationship box, click the pointer for Table, and select a table from the listing. In a one-to-many relationship, this tabular array should exist on the many side. Using our client and time intelligence example, you lot would choose the customer sales tabular array showtime, considering many sales are probable to occur on whatever given solar day.
For Column (Foreign), select the column that contains the information that is related to Related Column (Primary). For example, if you had a date column in both tables, you lot would choose that cavalcade now.
For Related Table, select a table that has at to the lowest degree one column of information that is related to the table you but selected for Table.
For Related Column (Primary), select a cavalcade that has unique values that match the values in the cavalcade you selected for Column.
Click OK.

More almost relationships between tables in Excel

Notes about relationships
Instance: Relating time intelligence data to airline flying information
"Relationships between tables may be needed"
- Step 1: Make up one's mind which tables to specify in the relationship
- Step two: Discover columns that tin can be used to create a path from one table to the adjacent

Notes about relationships

You'll know whether a relationship exists when you drag fields from different tables onto the PivotTable Fields list. If yous aren't prompted to create a relationship, Excel already has the relationship information information technology needs to relate the information.
Creating relationships is like to using VLOOKUPs: you need columns containing matching data so that Excel can cantankerous-reference rows in one table with those of another table. In the time intelligence instance, the Client table would need to accept engagement values that also exist in a time intelligence table.
In a data model, table relationships tin exist i-to-one (each passenger has ane boarding pass) or one-to-many (each flight has many passengers), but not many-to-many. Many-to-many relationships upshot in circular dependency errors, such as "A circular dependency was detected." This error will occur if you make a direct connection between two tables that are many-to-many, or indirect connections (a chain of tabular array relationships that are 1-to-many inside each relationship, but many-to-many when viewed end to terminate. Read more about Relationships between tables in a Information Model.
The data types in the two columns must be uniform. See Data types in Excel Data Models for details.
Other ways to create relationships might be more than intuitive, especially if you lot are not sure which columns to use. Meet Create a relationship in Diagram View in Power Pivot.

Example: Relating fourth dimension intelligence data to airline flight data

You can learn well-nigh both table relationships and time intelligence using free information on the Microsoft Azure Marketplace. Some of these datasets are very large, requiring a fast internet connection to complete the data download in a reasonable menses of time.

Showtime Ability Pivot in Microsoft Excel add-in and open the Ability Pivot window.
Click Get External Data > From Data Service > From Microsoft Azure Marketplace. The Microsoft Azure Marketplace home page opens in the Tabular array Import Magician.
Under Price, click Costless.
Under Category, click Science & Statistics.
Find DateStream and click Subscribe.
Enter your Microsoft business relationship and click Sign in. A preview of the data should appear in the window.
Scroll to the bottom and click Select Query.
Click Side by side.
Choose BasicCalendarUS and so click Finish to import the information. Over a fast internet connexion, import should accept nigh a minute. When finished, you should meet a condition report of 73,414 rows transferred. Click Close.
Click Get External Data > From Information Service > From Microsoft Azure Marketplace to import a second dataset.
Nether Blazon, click Information.
Under Price, click Gratuitous.
Find US Air Carrier Flying Delays and click Select.
Coil to the bottom and click Select Query.
Click Adjacent.
Click Finish to import the data. Over a fast internet connection, this can take 15 minutes to import. When finished, you should run across a condition report of 2,427,284 rows transferred. Click Close. Y'all should now have two tables in the data model. To relate them, nosotros'll demand compatible columns in each table.
Discover that the DateKey in BasicCalendarUS is in the format 1/1/2012 12:00:00 AM. The On_Time_Performance tabular array also has a datetime column, FlightDate, whose values are specified in the same format: 1/1/2012 12:00:00 AM. The two columns incorporate matching data, of the same data type, and at least ane of the columns (DateKey) contains only unique values. In the side by side several steps, you lot'll utilize these columns to chronicle the tables.
In the Power Pin window, click PivotTable to create a PivotTable in a new or existing worksheet.
In the Field Listing, expand On_Time_Performance and click ArrDelayMinutes to add it to the Values surface area. In the PivotTable, y'all should run into the full amount of fourth dimension flights were delayed, as measured in minutes.
Aggrandize BasicCalendarUS and click MonthInCalendar to add it to the Rows area.
Notice that the PivotTable now lists months, but the sum total of minutes is the same for every month. Repeating, identical values indicate a relationship is needed.
In the Field List, in "Relationships between tables may exist needed", click Create.
In Related Tabular array, select On_Time_Performance and in Related Column (Principal) choose FlightDate.
In Tabular array, select BasicCalendarUS and in Column (Foreign) choose DateKey. Click OK to create the relationship.
Notice that the sum of minutes delayed at present varies for each month.
In BasicCalendarUS and elevate YearKey to the Rows area, above MonthInCalendar.

Y'all can at present slice inflow delays by yr and month, or other values in the agenda.

Tips: Past default, months are listed in alphabetical order. Using the Power Pivot add-in, yous can change the sort so that months appear in chronological gild.

Brand sure that the BasicCalendarUS table is open in the Power Pivot window.
On the Home tabular array, click Sort by Cavalcade.
In Sort, choose MonthInCalendar
In Past, choose MonthOfYear.

The PivotTable now sorts each month-yr combination (October 2011, November 2011) by the month number inside a year (10, 11). Changing the sort gild is piece of cake considering the DateStream feed provides all of the necessary columns to make this scenario work. If you're using a different time intelligence table, your step will exist different.

"Relationships between tables may be needed"

As you lot add fields to a PivotTable, you'll exist informed if a tabular array relationship is required to brand sense of the fields you selected in the PivotTable.

Create button appears when relationship is needed

Although Excel can tell you lot when a human relationship is needed, it can't tell you which tables and columns to employ, or whether a table relationship is even possible. Endeavor following these steps to get the answers you need.

Step 1: Determine which tables to specify in the relationship

If your model contains only a few tables, it might be immediately obvious which ones y'all need to apply. But for larger models, you could probably use some help. One approach is to use Diagram View in the Ability Pin add-in. Diagram View provides a visual representation of all the tables in the Data Model. Using Diagram View, y'all can quickly determine which tables are dissever from the residual of the model.

Diagram view showing disconnected tables

Note: It's possible to create ambiguous relationships that are invalid when used in a PivotTable or Ability View report. Suppose all of your tables are related in some way to other tables in the model, merely when yous try to combine fields from different tables, you get the "Relationships between tables may be needed" message. The about probable crusade is that you lot've run across a many-to-many human relationship. If you follow the chain of tabular array relationships that connect to the tables you lot want to use, yous volition probably observe that you lot accept 2 or more ane-to-many tabular array relationships. There is no piece of cake workaround that works for every situation, but you might try creating calculated columns to consolidate the columns you want to use into 1 tabular array.

Pace 2: Find columns that can be used to create a path from one table to the next

After you've identified which tabular array is disconnected from the rest of the model, review its columns to decide whether another column, elsewhere in the model, contains matching values.

For example, suppose you have a model that contains product sales past territory, and that you subsequently import demographic data to find out if there is correlation betwixt sales and demographic trends in each territory. Because the demographic data comes from a dissimilar data source, its tables are initially isolated from the rest of the model. To integrate the demographic data with the residual of your model, you'll need to observe a column in one of the demographic tables that corresponds to 1 you're already using. For example if the demographic data is organized by region, and your sales information specifies which region the sale occurred, y'all could relate the two datasets by finding a common cavalcade, such equally a State, Zip code, or Region, to provide the lookup.

Besides matching values, at that place are a few additional requirements for creating a relationship:

Data values in the lookup cavalcade must be unique. In other words, the cavalcade can't comprise duplicates. In a Data Model, nulls and empty strings are equivalent to a blank, which is a singled-out data value. This means that you tin't have multiple nulls in the lookup column.
Information types of both the source cavalcade and lookup column must be uniform. For more information about data types, see Data types in Information Models.

To learn more about tabular array relationships, run into Relationships between tables in a Information Model.

Top of Page