How to Change the Data Source for Pivot Table in Excel (Range)

In this post, we will discuss changing or locating the data source for a given Pivot Table. This is extremely helpful when your data constantly changes.

What is the Source Data in Pivot Tables?

Source data in a Pivot Table is the original data that you want to analyze and summarize using the Pivot Table. This could be anything from sales data, performance ratings, survey results, or other types of information that you have collected and organized in Excel. The source data could be a range of cells within an Excel worksheet, a named range, an Excel table, or data imported from an external source using power query.

Steps to Change Data Source in Pivot Table

1. To start, click on a cell of the pivot table, or you can also select the entire pivot. This will activate the "Analyze" or "PivotTable Analyze" tab on the ribbon.
   
2. After that, in the Data group, there is a drop-down list named "Change Data Source." Click Change Data Source to open the dialog box.
   
3. In the "Change PivotTable Data Source" dialog box, you will be able to select a new data source for your Pivot Table. This could be another range of cells in your current worksheet, or a different worksheet altogether. Navigate to the desired data source and select it.
   
4. Here in this example, the current data source for the PivotTable is Sheet1!$A$1:$F$8 which means that the rows A9 till A13 are not included in the PivotTable.
5. Now either select the complete data i.e. Sheet1!$A$1:$F$13 or use the table name range to update your PivotTable/data source. In the end, click ok to update the data source.

Keyboard Shortcut to Change Data Source in a Pivot Table

There’s no direct keyboard shortcut to change the pivot table data source.

However, you can use the shortcut below to open the data source dialog box and then change the reference from there.

You can also use the shortcut key Alt ⇢ J ⇢ T ⇢ I ⇢ D.

You can use the following steps:

• Click anywhere inside the Pivot Table to bring up the PivotTable Tools options. Press Alt + JT + R + R on your keyboard. This will open the “Change PivotTable Data Source” dialog box.
• Now, you can select a new data source for your Pivot Table. Use the arrow keys to navigate to the desired data source and select it.
• Press Enter to confirm your selection. The data within your Pivot Table will be updated to reflect the new data source.

Change to a Dynamic Data Source

By doing this, there will be no need to update your source data again again and again. Just refreshing the Pivot Table will update the data range.

1. Convert your data range into a Table by selecting your data and then choosing the “Table” option from the “Insert” tab in the Excel toolbar.
2. Once your data is converted into a Table, any changes you make to your data (like adding new rows or columns) will automatically be included in the Table range.
3. You can now use this Table as the data source for your Pivot Table. When you create your Pivot Table, select the Table you just created instead of choosing a static range as source data.
4. Now, whenever you add, remove, or change data in your Table, you can refresh your Pivot Table to update it with the new data. There is no need to adjust the data source each time your data changes manually.
5. To refresh the PivotTable, select any cell in the Table, go to “Analyze” on the Excel toolbar, and select “Refresh.”

Note – This method requires that your original source data is set up in a way that can be converted into an Excel Table. This means that your data should have a clear header row and consistent data types in each column.

This method also helps you change the data source to a different Excel file (new data). All you need to do is to convert the data into Excel table and then change the data source from the dialog box.