Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
Change the source data for a PivotTable

After you create a PivotTable, you can change the range of its source data. For example, you can expand the source data to include more rows of data. However, if the source data has been changed substantially—such as having more or fewer columns, consider creating a new PivotTable.

You can change the data source of a PivotTable to a different Excel table or a cell range, or change to a different external data source.

  1. Click the PivotTable report.

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

    The Change PivotTable Data Source dialog box is displayed.

  3. Do one of the following:

    To use a different connection

    1. Click select a Use an external data source, and then click Choose Connection.

      Change PivotTable Data Source dialog box

      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 click 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. Click OK.

    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 click OK.

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

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.

Tables tab in the Existing Connections dialog box

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

  2. Click Only Create Connection.

    Import Data Only Create Connection

  3. Click Properties, and click the Definition tab.

    Connection Properties

  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.

  1. Click the PivotTable report.

  2. On the Options tab, in the Data group, click Change Data Source, and then click 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, 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.

  4. To use a different connection, click select a Use an external data source, and then click 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 click 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. Click OK.

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

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.

Tables tab in the Existing Connections dialog box

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

  2. Click Only Create Connection.

    Import Data Only Create Connection

  3. Click Properties, and click the Definition tab.

    Connection Properties

  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. Click the PivotTable report.

  2. On the Analyze tab, in the Data group, click Change Data Source, and then click 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 click OK

      Change PivotTable Data Source dialog box

    • To use a different connection, do the following:

      1. Click select a Use an external data source, and then click Choose Connection.

        Change PivotTable Data Source dialog box

        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 click 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. Click OK.

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

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.

Tables tab in the Existing Connections dialog box

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

  2. Click Only Create Connection.

    Import Data Only Create Connection

  3. Click Properties, and click the Definition tab.

    Connection Properties

  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.

  1. Tap anywhere in the PivotTable to show the PivotTable tab on the ribbon.  

  2. Tap Change Data Source to display the change data source side pane. 

  3. Do one of the following: 

  • Type in the desired Table/Range in the Source box. 

  • Tap on the Source box, and then select the desired Table/Range on the grid.

4.   Tap Apply.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Create a PivotTable with an external data source

Create a PivotTable connected to Power BI Datasets

Manage connections to data in a workbook

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×