Ways to connect (import) external data to an Excel workbook

The primary benefit of Microsoft Office Excel's connection to external data is that it can be analyzed regularly in Excel without duplicating the data, which is time-consuming and error-prone. After you connect to external data, you can also automatically refresh (or update) an Excel workbook from the original data source, regardless of whether the data source was updated with new information.

security Your computer may disable the connection to external data. To connect to data when you open a workbook, you must enable the data connection by using the Trust Center bar or by placing the workbook in a trusted location.

2. On the Data tab, in the Get External Data Group, click Existing Connection.

   The Existing Connection dialog box appears.

4. In the display Drop-down list at the top of the dialog box, do one of the following:
   • To display all connections, click All Connections. This is the default option.
   • To display only the list of recently used connections, click Connections in thisworkbook.

     This list is created from a connection that has already been defined, a connection that was created using the Data Connection Wizard's Select Data Source dialog box, or a connection that was previously selected in the dialog box.

   • To display only the connections that are available on your computer, click Connection files for this computer.

     This list is created from the my Data Sources folder, which is typically stored in My Documents .

   • To display only the available connections on the network for which you can access the connection files, click Connection files for your network.

     This list was created from the Excelservices Data Connection Library (DCL) on the Microsoft Office SharePoint Server 2007 Web site. DCL is a document library on the Microsoft office SharePoint Services 2007 Web site that contains a collection of Office Data Connection (ODC) files (. odc). DCL is typically set up by the site administrator, and the site administrator can also configure the SharePoint site to display an ODC file in this DCL in the External Connections dialog box. For more information, see Office SharePoint Server 2007 Central Administration Help.

     If you don't see the connection you want, you can create the connection by clicking Browse more to display the Select Data Source dialog box, and then clicking New Source to start the Data Connection Wizard.

   Note If you select a connection from the network connection files or connection files on this computer category, the connection file is copied to the workbook as a new workbook connection and will be used as the new connection information.

6. Select the connection you want, and then click Open.

   The Import Data dialog box appears.

8. Under Select how this data is displayed in the workbook, do one of the following:
   • To create a table for simple sorting and filtering, click Tables.
   • To create a PivotTable report that summarizes large amounts of data through aggregation and aggregate data, click PivotTable.
   • To create a PivotTable and PivotChart report that summarizes the data visually, click PivotChart andPivotTable report.
   • To store the selected connection in the workbook for future use, click Create Connection only.

     Use this option to store the selected connection in a workbook for later use. For example, if you are connecting to an online analytical processing (OLAP) cube data source, and you intend to use the Convert to Formula command (on the Options tab, in the Tools Group, click OLAP Tools to convert a PivotTable cell to a worksheet formula, you can use this option because you do not have to save the PivotTable report.

   Note These options are not available for all types of data connections, including text, Web queries, and XML.

10. Under drop location for data , do one of the following:
    • To place a PivotTable or PivotChart report on an existing worksheet, select Existing worksheet, and then type the first cell in the range of cells where you want to place the PivotTable report.

      You can also click the Compress dialog box To temporarily hide the dialog box, select the cell on the worksheet, and then press Expand Dialog.

12. To place the PivotTable report on a new worksheet and start at cell A1, click NewSheet.
14. Alternatively, you can change the connection properties by clicking Properties, making changes in the connection properties,external data range , or XML Mapping Properties dialog box, and then clicking OK .