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.

To change the data source of a PivotTable if it's a range of cells or an Excel table, do the following:

  1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

PivotTable Tools

  1. Click Analyze > Change Data Source.

Change Data Source button on the Analyze tab of the PivotTable Tools

  1. In the Table/Range box, enter the range you want to use.

Change PivotTable Data Source dialog box

 Tip    Leave the dialog box open, and then select the table or range on your worksheet. If the data you want to include is on a different worksheet, click that worksheet, and then select the table or range.

Change to a different external data source

To base your PivotTable on a different external source, it might be best to create a new PivotTable.

If the location of your external data source changed—for example, your SQL Server database name is the same, but it's been moved to a different server, or your Access database has been moved to another network share—you can change your current connection.

  1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

PivotTable Tools

  1. Click Analyze > Change Data Source.

Change Data Source button on the Analyze tab of the PivotTable Tools

  1. Click Choose Connection.

Change PivotTable Data Source dialog box

  1. In the Show box, keep All Connections selected, or pick the connection category that has the data source you want to connect to.

Existing Connections dialog box

If your new location is not listed, 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

  1. Click Properties, and click the Definition tab.

Connection Properties

  1. If your .odc connection file has been moved, browse to its new location in the Connection file box.
  2. If you need to change values in the Connection string box, contact your database administrator.

More about PivotTables

 
 
Applies to:
Excel 2013