How to Change Data Range in Pivot Table

A Pivot Table is a special tool provided in MS Excel that allows the user to compute and study the data. The tool makes it easier to observe comparisons or trends in the data that can be used for prediction. The working of the pivot table and the steps to perform a given task can different which depend on the platform in which you were executing the Excel.

Modifying the Source Data For the Pivot Table

Once you have made a Pivot Table in your Excel spreadsheet, you can edit the source data range for your Pivot Table.

For instance, you can increase the source of the data. This allows the user to add additional rows of data. This increases the number of records without changing the table itself.

Note: It is advised that if the user wants to alter and increase the volume of data substantially, that is, adding several columns in the table, it would be better if the user creates a new pivot table.

To change the range of the data, the user can change the source of the Pivot table's data to a different table or cell range. We can also change the entire external data source.

Changing the Source Data of the Pivot table

The steps to change the pivot table's data source will differ for different operating systems.

In Windows:

Select the Pivot Table report in the spreadsheet.
In the report, open the Analyze tab in the Data Group. There you will find the option to Change the Data Source. Click the Change Data Source option.
It will open a dialog box to Change Pivot Table Data Source.

Once you have opened the Dialog box, you can perform either one of the following steps:

Using a Different Connection

Click on Use an external Data Source from the list of options, and then use the option to Choose connection.
This will open a box that will contain all the existing connections.
From the drop-down menu containing the list of connections, select the category of the connections you want to select. You can either choose a particular category or select all the existing Connections. This is also the default option.
Once you have selected the category, the next time is to Select a connection from the Select a Connection list box. Then click Open. This will establish a connection.
Click on OK.

Note: When choosing the category, if you select a connection from the Connections in the workbook, you will have to either reuse or share an existing connection, but if the connection is chosen under the Connection files on the network or the connection from the Computer. It will copy the file into the workbook as the new connection for the Pivot table.

Changing the Data source of the Pivot Table to another Excel Table

From the dialog box, select the Select a Table or Range. It will open a text box in the box and enter the table or the range you want to add as the data source for the table. Then Click on OK.
Open the Pivot table report in the spreadsheet.
In the report, open the Analyze tab in the Data Group. There you will find the option to Change the Data Source. Click the Change Data Source option.
It will open a dialog box to Change Pivot Table Data Source.
After opening the Dialog box, you can perform the following steps to change the Data source.
You can either perform the following steps to choose a different connection.
Click on the Collapse dialog. This will hide the dialog box. Now click on the first cell on the worksheet. You can click on Expand Dialog. This will open the dialog box again.
If you want to change the connection, select Use an External Data Source, then choose the connection from the list.
This will open a dialog box with enlisting Existing Connections.
From the drop-down menu containing the list of connections, select the category of the connections you want to select. You can either choose a particular category or select all the existing Connections. This is also the default option.
Once you have selected the category, the next time is to Select a connection from the Select a Connection list box. Then click Open. This will establish a connection.

Note: When choosing the category, if you select a connection from the Connections in the workbook, you will have to either reuse or share an existing connection, but if the connection is chosen under the Connection files on the network or the connection from the Computer. It will copy the file into the workbook as the new connection for the Pivot table.

Click on OK.

In Web Application

Select the Pivot Table report in the spreadsheet.
In the report, open the Analyze tab in the Data Group. There you will find the option to Change the Data Source. Click the Change Data Source option.
It will open a dialog box to Change Pivot Table Data Source.

Once you have opened the Dialog box, you can perform either one of the following steps:

Using a Different Connection

Click on Use an external Data Source from the list of options, and then use the option to Choose connection.
This will open a box that will contain all the existing connections.
From the drop-down menu containing the list of connections, select the category of the connections you want to select. You can either choose a particular category or select all the existing Connections. This is also the default option.
Once you have selected the category, the next time is to Select a connection from the Select a Connection list box. Then click Open. This will establish a connection.

Note: When choosing the category, if you select a connection from the Connections in the workbook, you will have to either reuse or share an existing connection, but if the connection is chosen under the Connection files on the network or the connection from the Computer. It will copy the file into the workbook as the new connection for the Pivot table.

Click on OK.

In Mac OS

Select the Pivot Table report in the spreadsheet.
In the report, open the Analyze tab in the Data Group. There you will find the option to Change the Data Source. Click the Change Data Source option.
It will open a dialog box to Change Pivot Table Data Source.

Once you have opened the Dialog box, you can perform either one of the following steps:

Using a Different Connection

Click on Use an external Data Source from the list of options, and then use the option to Choose connection.
This will open a box that will contain all the existing connections.
From the drop-down menu containing the list of connections, select the category of the connections you want to select. You can either choose a particular category or select all the existing Connections. This is also the default option.
Once you have selected the category, the next time is to Select a connection from the Select a Connection list box. Then click Open. This will establish a connection.

Note: When choosing the category, if you select a connection from the Connections in the workbook, you will have to either reuse or share an existing connection, but if the connection is chosen under the Connection files on the network or the connection from the Computer. It will copy the file into the workbook as the new connection for the Pivot table.

Click on OK.

If the connection is not Listed

If the connection you want to set up is not listed in the Existing Connections dialog box when selecting the connection. Click on Browse for More, then browse to the location of the data source you want to add as a connection to the list. Open the Select Data Source dialog box. Then select New Source for the connection.
After performing the above steps in the Connection Wizard, close the Wizard and click on the Select Data Source dialog box.
Check the report for your Pivot table, and if the table is based on a connection to a range or table in the data model, then you can change to another Data Model. You can also select another connection from the Tables tab. The pivot table's data source cannot be changed if the Pivot table is based on the Workbook model.
Select the connection that you want to list in the Existing connection.
After selecting the connection, click on Open.
It will open a list of options you can perform on the connection.
Select Only Create Connection.
In the properties, select the Definition tab from the menu.
It can move the .odc connection file. If so, change the location to the Connection file box.
The database administrator can modify the values in the Connection string box.