Using Excel Data from Another Workbook

If the data you want to use in your PivotTable are in another workbook, the process is slightly different. You have to specify not only the range in which the data are located but also the name of the workbook they are in.
The easiest way to do this is to have both workbooks open; the one where you want to place the PivotTable should be the active one. Then follow these steps:

1. Click PivotTable on the Insert ribbon to display the Create PivotTable dialog box.
2. Make sure that the Select a Table or Range option is selected.
3. Click the Select button at the right end of the Table/Range field. The dialog box collapses to a single line.
4. Press Alt+Tab or click the Windows taskbar to activate the workbook that contains the data. The collapsed dialog box remains visible.
5. Select the data range for the PivotTable. Its address, including the workbook name, will be entered in the Create PivotTable dialog box.
6. Click the Select button in the dialog box to accept the selection and expand the dialog box.
7. Click OK to return to the original workbook with the PivotTable inserted.

When a PivotTable is linked to data in an external workbook, you can update it only if the linked data workbook is available. If this file has been moved, renamed, or deleted, you will not be able to update the PivotTable. Excel displays an error message when you attempt to do so. The original PivotTable data remains in place, however.

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website