Excel 2013 makes it easy to import data into a worksheet from other database tables created with stand-alone database management systems (such as Microsoft Access), a process known as making an external data query.
To make an external data query to an Access database table, you click the From Access command button on the Ribbon’s Data tab or press Alt+AFA. Excel opens the Select Data Source dialog box where you select the name of the Access database and then click Open.
The Select Table dialog box appears from which you can select the data table that you want to import into the worksheet. After you select the data table and click OK in this dialog box, the Import Data dialog box appears.
The Import Data dialog box contains the following options:
Table to have the data in the Access data table imported into an Excel table in either the current or new worksheet — see Existing Worksheet and New Worksheet entries that follow
PivotTable Report to have the data in the Access data table imported into a new pivot table that you can construct with the Access data
PivotChart to have the data in the Access data table imported into a new pivot table with an embedded pivot chart that you can construct from the Access data
Only Create Connection to link to the data in the selected Access data table without bringing its data into the Excel worksheet
Existing Worksheet (default) to have the data in the Access data table imported into the current worksheet starting at the current cell address listed in the text box below
New Worksheet to have the data in the Access data table imported into a new sheet that’s added to the beginning of the workbook
You can see an Excel worksheet after importing the Invoices data table from the sample Northwind Access database as a new data table in Excel. After importing the data, you can then use the AutoFilter buttons attached to the various fields to sort and filter the data.
Excel keeps a list of all the external data queries you make so that you can reuse them to import updated data from another database or web page. To reuse a query, click the Existing Connections button on the Data tab (Alt+AX) to open the Existing Connections dialog box to access this list and then click the name of the query to repeat.
Databases created and maintained with Microsoft Access are not, of course, the only external data sources on which you can perform external data queries. To import data from other sources, you click the From Other Sources button on the Data tab or press Alt+AFO to open a drop-down menu with the following options:
From SQL Server to import data from an SQL Server table
From Analysis Services to import data from an SQL Server Analysis cube
From Windows Azure Marketplace to import data from any of the various marketplace service providers — note that you must provide the file location (usually a URL address) and your account key before you can import any marketplace data into Excel.
From OData Data Feed to import data from any database table following the Open Data Protocol (shortened to OData) — note that you must provide the file location (usually a URL address) and user ID and password to access the OData data feed before you can import any of its data into Excel
From XML Data Import to import data from an XML file that you open and map
From Data Connection Wizard to import data from a database table using the Data Connection Wizard and OLEDB (Object Linking and Embedding Database)
From Microsoft Query to import data from a database table using Microsoft Query and ODBC (Open Database Connectivity)
