How to Change Data Source in Pivot Table (3 Easy Ways)

Pivot Tables are a very useful feature in Excel to analyze and summarize data.

Sometimes we need to change the data sources of our Pivot Tables as we have added new data values to the data sources, or we want to analyze a new data set in the same Pivot Table.

So, it is important to know how to change the data source in the Pivot Table.

In this article, I’ll show you how to change data sources in Pivot Table and some useful tips to get our Pivot Table dynamically updated for the changing data sources.

Table of Contents

Expand The Existing Data Source in Pivot Table

We create Pivot Tables from source data. We periodically add new data rows to these source data.

These additional data rows, however, will not be added to our Pivot Table. Then, we have to expand the data source of our Pivot Table.

Below I have created a Pivot Table to find the number of students for each course.

Pivot table created using the source data on the left

Now, I want to know whether I have correctly captured source data for the Pivot Table. Then I need to update the source data correctly. To do that, I have to follow the steps below.

Select any cell of the Pivot Table to activate the “PivotTable Analyze” tab. Then, select that tab.

Click the “Change Data Source” icon. We can find this icon in the “Data” group.

Then, Excel will open the “Change PivotTable Data Source” dialog box.

Some Excel users like to use keyboard shortcuts for their Excel work. If we want to open the “Change PivotTable Data Source” dialog box using the keyboard shortcut, we can use the below shortcut. Remember to select any cell on the Pivot Table before using the following shortcut – ALT + J + T + I + D

We can find the existing range or the existing table of source data for the selected Pivot Table in the “Table/Range” box of the “Change PivotTable Data Source” dialog box.

Existing data source range of the pivot table

We can see the existing range or the existing table of source data for the selected Pivot Table with a moving border too.

Existing data source needs to be updated

So, in this case, the source data is selected up to row number 10. But, we need to add row number 11 also as source data of the Pivot Table.

We can press the Control + Shift + Down arrow keys and expand the source data. Another method to expand the data source is to manually replace row and column names.

Change the data source in the table range field

Click the “OK” button of the “Change PivotTable Data Source” dialog box.

Now, the Pivot Table is updated with the full source data.

Pro Tip – Convert Source Data to Excel Table

When we add new data to source data as a new row, we have to always update the data source as above. I know it is not easy when we frequently add data rows to our source data.

There is a simple technique that we can use to overcome the above problem.

That is to convert the source data to an Excel Data table. To do that, select any cell in the source data and press “Control + T”. This will open the Excel Table dialog box, where you can confirm the range and click OK

When you do this, the source data will now be an Excel Table, and we can refer to the table name as the source data or the Pivot Table.

When you add a new data row below the table, that row will automatically become a part of the existing table. So, when we refresh the Pivot Table, it will update based on the entire source data table.

Also read: How to Clear Pivot Table Cache?

Change Data Source in Pivot Table

In the above example, I showed you how to expand the source data to include some additional records that may have been added.

But sometimes, you need to change the entire data source for a Pivot Table.

For example, if you want to analyze a different set of data in the same Pivot Table, you have to change the source data.

I received an Excel file with a Pivot Table, which analyzes the number of students for each course.

When I check the source data using the ALT + J + T + I + D Excel shortcut, it shows that the Pivot Table is constructed using the data in the “Last Year” tab.

Data source used to create the pivot table

Now, I want to update the Pivot Table based on the data in the “Current Year” tab.

In other words, I want to change the source data of the Pivot Table to the “Current Year” tab.

Then, I need to follow the steps below.

Select and delete the existing range given in the “Change PivotTable Data Source” dialog box.

Select the new PivotTable data source. In this case, I click on the “Current Year” tab and select the new data range. So, I am selecting the below range.

'Current Year'!$A$2:$B$9

Change the range that you want to use for the pivot table

Click the “OK” button.

Now, the Pivot Table is updated based on the data in the “Current Year” tab.

Pivot table has now updated with new data

Also read: How to Move Pivot Table in Excel?

Rename Range/Table Name to Change Data Source in Pivot Table

It is common practice to create Excel named ranges and Excel Tables. It helps to identify the ranges or tables easily.

These named ranges and Excel Tables are very useful when we change data sources in Pivot Tables too.

I received an Excel file with a Pivot Table, which analyzes the number of students for each course.

When I check the source data using the ALT + J + T + I + D Excel shortcut, it shows that the Pivot Table is constructed using a range called “Last_Year”.

Pivot table data source uses a name range

Now, I want to update the Pivot Table based on the range called “Current Year” (which refers to the latest data in the Current Year worksheet tab)

This time I do not need to manually go and select the “Current Year” range.

I can follow the below simple steps to change the data source of the Pivot Table.

Select and delete the existing named range from the Table/Range box of the “Change PivotTable Data Source” dialog box. So, I am deleting the “Last_Year” from the Table/ Range: box.

Now, type the new named range in that empty box. So, I am typing “Current_Year” in that box.

Finally, click the “OK” button in the “Change PivotTable Data Source” dialog box.

This would change the data source of the Pivot Table to the current year (you can see the updated Pivot Table below).

We can use the same steps to change the data source from one Excel Table to another.

Also read: How to Sort a Pivot Table in Excel?

Change External Data Source in Pivot Table

Sometimes we create Pivot Tables using external data sources such as Microsoft Access, Online Analytical Processing (OLAP) cube file, or Microsoft SQL Server database.

Even in that situation, we may want to change the data source of the Pivot Table to a different external data source.

Then, we have to follow the below steps.

Select any cell of the Pivot Table to activate the “PivotTable Analyze” tab and select that tab.

Click the “Change Data Source” icon. We can find this icon in the “Data” group.

Then, Excel will open the “Change PivotTable Data Source” dialog box.

Select the option of “Use an external data source” from the “Change PivotTable Data Source” dialog box.

Select the use an external data source option

Click the “Choose Connection….” tab.

Select the category of connections from the dropdown list.
From the list box labeled “Select a Connection,” choose a connection.
Then click the Open button.
Click the “OK” button.

Now you know all the different ways to change data sources in the Pivot Table. Convert data sources to Excel Tables to dynamically update the data sources of Pivot Tables.

Also read: How to Delete a Pivot Table in Excel?

Tips to Create Source Data for Pivot Tables

Creating source data for pivot tables involves preparing and organizing your data effectively. Here are some tips to help you do that:

Consistent Data Structure: Ensure your data is in a tabular format. That is, all rows should have the same columns.

Clear and Unique Headers: Each column should have a unique header. This is because each header will turn into a field name in your pivot table.

Avoid Blank Cells: Try to eliminate blank cells in your data. Pivot tables can handle blanks, but they can often lead to confusing results.

Consistent Data Types: Make sure each column contains the same type of data. For example, don’t mix dates and text in the same column.

Avoid Duplicates: If possible, avoid duplicate rows in your data. While duplicates won’t necessarily cause errors, they can skew your data.

Raw Data Only: Your pivot table source data should be raw data, not calculations. If you need to include calculations, try to do that within the pivot table itself.

Avoid Merged Cells: PivotTables do not work well with data in merged cells. It’s best to unmerge cells and ensure data is in individual cells.

Avoid Extra Spaces: Be careful about extra spaces at the start or end of your text strings. This could cause items that should be grouped together to be treated as separate items.

Include a Totals Row or Column: If possible, include a total row or column in your data. While not necessary, it can be a good way to verify that your pivot table is calculating correctly.

No Subtotals: If your data set already includes subtotals, remove them. The pivot table will provide these.

Remember that data preparation can sometimes take longer than building the actual pivot table. The cleaner and more consistent your data, the easier it will be to create your pivot table.

Other Excel articles you may also like: