Using Data from Other Sources

• Link your PivotTable to the external data without importing them into Excel.
• Import the external data into Excel and then treat them as an Excel list.

This section covers the procedures for linking to external data. Importing data is discussed in “Using Other External Data Sources.”
To create a PivotTable that is linked to external data:

1. Display the Create PivotTable dialog box.
2. Select the Use an External Data Source option.
3. Click the Choose Connection button. Excel displays the Existing Connections dialog box, shown in Figure 2-4.

Figure 2-4: Selecting an external data source for a PivotTable.

The information shown in the Existing Connections dialog box depends on how your specific system has been set up—it is very unlikely that your Existing Connections dialog box contains the same elements as Figure 2-4. You can use the drop-down Show list at the top of the dialog box to control which data connections are displayed: those defined in the current workbook, those available on the network, and those available on your computer.
At this point you can do one of two things:

• Click the desired data connection; then click Open.
• Click Browse to locate a data connection that is not listed.

The next steps that you follow depend on the specific data connection you have selected. Some data connections are ready for immediate use in a PivotTable, whereas others require additional input from you before they can be used.
Note, however, that Excel offers another way to create a PivotTable that is based on external data. The end result is the same, but you may prefer using this method in some cases. On the Data ribbon there is a section labeled Get External Data. This command is usually used for importing data into a workbook but can also be used to create a PivotTable:

1. Click the Existing Connections button to see a list of existing connection, as shown earlier.
2. Click OK and continue the process until the Import Data dialog box is displayed, as shown in Figure 2-5.
3. Select either the PivotTable Report or the PivotChart and PivotTable Report option.
4. Specify whether the PivotTable should go in the current worksheet or a new worksheet.
5. Click OK.

Figure 2-5: Importing data linked to a PivotTable.

Leave a Reply