Change the source data for a PivotTable

1. Select the PivotTable report.

2. On the Options tab, in the Data group, select​​​​​​​ Change Data Source, and then select Change Data Source.

   The Change PivotTable Data source dialog box is displayed.

3. Do one of the following:

   • To use a different Excel table or cell range, click Select a table or range, and then enter the first cell in the Table/Range text box.

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

4. To use a different connection, select Use an external data source, and then select Choose Connection.

   The Existing Connections dialog box is displayed.

5. In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).

6. Select a connection from the Select a Connection list box, and then select Open.

   Note: If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer category, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.

   For more information, see Manage connections to data in a workbook.

7. Select OK.

If your connection is not listed in the Existing Connections dialog box, select Browse for More, and then look for the data source you want to connect to in the Select Data Source dialog box. Select New Source if appropriate, and follow the steps in the Data Connection Wizard, and then return to the Select Data Source dialog box.

If your PivotTable is based on a connection to a range or table in the Data Model, you can choose another Data Model table or connection on the Tables tab. However, if your PivotTable is based on the Workbook Data Model, you can’t change the data source.

1. Pick the connection you want, and select Open.

2. Select Only Create Connection.

   

3. Select Properties, and select the Definition tab.

   

4. If your .odc connection file has been moved, browse to its new location in the Connection file box.

5. If you need to change values in the Connection string box, contact your database administrator.

For more information about what data sources are supported, see Import and shape data in Excel for Mac (Power Query).

1. Select the PivotTable report.

2. On the Analyze tab, in the Data group, select​​​​​​​ Change Data Source, and then select Change Data Source.

   The Change PivotTable Data Source dialog box is displayed.

3. Do one of the following:

   • To change the data source of a PivotTable to a different Excel table or a cell range, click​​​​​​​ Select a table or range, and then enter the first cell in the Table/Range text box, and select OK

     

   • To use a different connection, do the following:

     1. Select Use an external data source, and then select Choose Connection.

        

        The Existing Connections dialog box is displayed.

     2. In the Show drop-down list at the top of the dialog box, select the category of connections for which you want to choose a connection or select All Existing Connections (which is the default).

     3. Select a connection from the Select a Connection list box, and then select Open. What if your connection is not listed?

        Note: If you choose a connection from the Connections in this Workbook category, you will be reusing or sharing an existing connection. If you choose a connection from the Connection files on the network or Connection files on this computer category, the connection file is copied into the workbook as a new workbook connection, and then used as the new connection for the PivotTable report.

        For more information, see Manage connections to data in a workbook.

     4. Select OK.

If your connection is not listed in the Existing Connections dialog box, select Browse for More, and then look for the data source you want to connect to in the Select Data Source dialog box. Select New Source if appropriate, and follow the steps in the Data Connection Wizard, and then return to the Select Data Source dialog box.

If your PivotTable is based on a connection to a range or table in the Data Model, you can choose another Data Model table or connection on the Tables tab. However, if your PivotTable is based on the Workbook Data Model, you can’t change the data source.

1. Pick the connection you want, and select Open.

2. Select Only Create Connection.

   

3. Select Properties, and select the Definition tab.

   

4. If your .odc connection file has been moved, browse to its new location in the Connection file box.

5. If you need to change values in the Connection string box, contact your database administrator.