The main benefit of connecting to external data is that you can periodically analyze this data in Microsoft Office Excel without repeatedly copying the data, which is an operation that can be time-consuming and error-prone. After connecting to external data, you can also automatically refresh (or update) your Excel workbooks from the original data source whenever the data source is updated with new information.

Security Connections to external data may be disabled on your computer. To connect to data when you open a workbook, you must enable data connections by using the Trust Center bar, or by putting the workbook in a trusted location. For more information, see , , and .

  1. On the Data tab, in the Get External Data group, click Existing Connections.

    The Existing Connections dialog box is displayed.

  2. In the Show drop-down list at the top of the dialog box, do one of the following:
    • To display all connections, click All Connections. This the default.
    • To display only the recently used list of connections, click Connections in this Workbook.

      This list is created from connections that you have already defined, that you have created by using the Select Data Source dialog box of the Data Connection Wizard, or that you have previously selected as a connection from this dialog box.

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

      This list is created from the My Data Sources folder that is usually stored in the My Documents folder.

    • To display only the connections that are available from a connection file that is accessible from the network, click Connection files on the network.

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

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

    Note If you choose a connection from the Connection files on the network or Connection files on this computer categories, the connection file is copied into the workbook as a new workbook connection, and then it is used as the new connection information.

  3. Select the connection that you want, and then click Open.

    The Import Data dialog box is displayed.

  4. Under Select how you want to view this data in your workbook do one of the following:
    • To create a table for simple sorting and filtering, click Table.
    • To create a PivotTable report for summarizing large amounts of data by aggregating and subtotaling the data, click PivotTable Report.
    • To create a PivotTable report, and PivotChart report for visually summarizing data, click PivotChart and PivotTable Report.
    • To store the selected connection in the workbook for later use, click Only Create Connection.

      Use this option to store the selected connection in the workbook for later use. For example, if you are connecting to an Online Analytical Processing (OLAP) cube data source and you intend to convert PivotTable cells to worksheet formulas by using the Convert to Formulas command (On the Options tab, in the Tools group, click OLAP tools), you might use this option because you don't need to save the PivotTable report.

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

  5. Under Where do you want to put the data?, do one of the following:
    • To place the PivotTable or PivotChart report in an existing worksheet, select Existing worksheet, and then type the first cell in the range of cells where you want to locate the PivotTable report.

      Alternatively, click Collapse Dialog button image to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog button image.

  6. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.
  7. Optionally, you can change connection properties by clicking Properties, by making your changes in the Connection Properties, External Data Range, or XML Map Properties dialog boxes, and then by clicking OK.


See also: