Excel 2016 makes it possible to query data lists (tables) stored in external databases to which you have access and then extract the data that interests you into your worksheet for further manipulation and analysis.
Excel 2016 also makes it easy to acquire data from a variety of different data sources, including Microsoft Access database files, web pages on the Internet, text files, and other data sources such as database tables on SQL Servers and Analysis Services, XML data files, and data tables from online connections to Microsoft Windows Azure DataMarket and OData Data feeds.
When importing data from such external sources into your Excel worksheets, you may well be dealing with data stored in multiple related tables all stored in the database (what is referred to in Excel 2016 as a Data Model). The relationship between different tables in the same database is based on a common field (column) that occurs in each related data table, which is officially known as a key field, but in Excel is generally as known as a lookup column.
When relating tables on a common key field, in at least one table, the records for that field must all be unique with no duplicates, such as Clients data table where the Customer ID field is unique and assigned only once (where it's known as the primary key). In the other related data table, the common field (known as the foreign key) may or may not be unique as in an Orders data table where entries in its Customer ID may not all be unique, as it's quite permissible (even desirable) to have the same client purchasing multiple products multiple times.
There's only other thing to keep in mind when working with related data tables and that is the type of relationship that exists between the two tables. There are two types of relationships supported in an Excel Data Model:
One-to-one relationship where the entries in both the primary and foreign key fields are totally unique such as a relationship between a Clients data list and Discount data list where the Customer ID field occurs only once in each table (as each client has only one discount percentage assigned)
One-to-many relationship where duplicate entries in the foreign key field are allowed and even expected as in a relationship between a Clients data list and an Orders data list where the Customer ID field may occur multiple times (as the client makes multiple purchases)
Most of the time Excel 2016 is able to figure out the relationship between the data tables you import. However, if Excel should ever get it wrong or your tables contain more than one common field that could possibly serve as the key, you can manually define the proper relationship. Simply select the Relationships button in the Data Tools group on the Ribbon's Data tab (Alt+AA) to open the Manage Relationships dialog box. There you click New to open the Create Relationship dialog box, where you define the common field in each of the two related data tables. After creating this relationship, you can use any of the fields in either of the two related tables in reports that you prepare or pivot tables that you create.
To import external data, you select the Get External Data command button on the Ribbon's Data tab (Alt+AZX). When you do this, Excel displays a menu with the following choices:
From Access to import database tables saved in Microsoft Access
From Web to perform a web page query to import data lists from web pages on the Internet
From Text to import data saved in a text file by defining how to parse its data into particular columns and rows of your worksheet
From Other Sources to open a drop-down menu that offers a variety of choices: From SQL Server, From Analysis Services, From Windows Azure Marketplace, From OData Data Feed, From XML Data Import, From Data Connection Wizard, and From Microsoft Query
Existing Connections to reuse a connection to a data service or data feed (using one of the import options, especially in the From Other Sources section) that you've already established either to retrieve more data or refresh previously imported data