ExcelInsider Team
After creating a pivot table, we often have to add more rows in the future. But a pivot table won’t automatically pick up the new data source. In this article, we will learn how to change pivot table data source in excel so that you can analyze your data further.
➤ Select any row from your pivot table
➤ From the PivotTable Analyze tab, go to Data>Change Data Source
➤ Select the new source of data and hit OK.
If that was not clear enough for you, we have the whole process explained below, along with an advanced method of using VBA to update the pivot table data source. Therefore, stick around if you are unable to change the pivot table data source.
Changing Data Source from the Analyze Tab
In this dataset, we have some transaction data of customers. When the pivot table was originally created, the dataset was not converted to a table. As a result, when more transactions were added, the pivot table did not update by itself. Now, we have to change the data source manually.
Here is how to change the data source:
➤ Go to the pivot table and select any row.
➤ From the PivotTable Analyze tab, select Change Data Source.
➤ If you have an external data source, select that option. For this dataset, we will choose Select a table or range and select all the rows we need to include in the pivot table.
➤ Press OK and check the new pivot table.
Changing Data Source Using VBA
If you are comfortable using a little bit of coding, this method is for you. We are going to use some VBA code in order to update the data source of the pivot table. Follow the steps below:
➤ While in the pivot table datasheet, press Alt+F11. A new window will open.
➤ Go to Insert > Module. In the code editor, write this code:
Sub ChangePivotTableDataSource()
Dim pt As PivotTable
Set pt = ThisWorkbook.Sheets("PivotTableSheet").PivotTables("PivotTable1")
pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:="SourceSheet!A1:E13")
End Sub➤ Change PivotTableSheet to the sheet where your pivot table exists, PivotTable1 to your pivot table name, and SourceSheet!A1:E13 to your new data source.
➤ Go to Run > Run Sub/UserForm or press F5.
➤ Come back to the pivot table sheet and see the result.
Creating a Table to Add New Data Automatically
While we have changed the data source because new rows were added, we wouldn’t have to do that if we had created a table before making the pivot table. Here is how you can convert your dataset to a table so that the pivot table automatically updates itself with new data.
➤ Select your dataset.
➤ Press Ctrl+T
➤ Double-check whether the selected dataset is okay or not, and then press OK.
➤ Now you can create a pivot table from this table, and we will not need to change the data source again and again.
Updating a Pivot Table after Changing Data Source
After changing the data a bit, it is important to update the pivot table in excel. The data can be updated without changing the data source again and again. You may choose to do it automatically or manually, depending on your task. Here is how to do it:
Refreshing Manually
➤ Select any cell in the pivot table.
➤ Right-click on that cell and click Refresh.
Refreshing Automatically
➤ From the PivotTable Analyze tab, go to Options.
➤ From the new windows, go to the Data tab, and check the “Refresh data when opening the file” box.
➤ Press OK. From now on, each time you open the file, the data in the pivot table will be refreshed.
Frequently Asked Questions
What is the shortcut key for change data source in PivotTable?
There is no shortcut key for changing the data source in a Pivot Table. However, you can use a sequence of keys to do so. Here is the sequence:
Alt > JT > I > D.
Then you can select the data source from the dialog box.
How do I link a PivotTable to a data source?
From Insert > PivotTable, select External Data Source. Now choose the connections and pick the ones you want.
How do I add another field in a PivotTable?
From the Fields panel on the right, check the boxes of the fields you want to add to the pivot table.
How do I get pivot source data?
When you try to change the data source, excel will automatically show where the source data was extracted from.
Can a PivotTable have 2 data sources?
Yes. To use two data sources, you need to create a relationship between them before making the pivot table.
Wrapping Up
In this article, we have learned how to change pivot table data source in excel. The workbook used to accomplish the task can be downloaded for your convenience. Bookmark the site for more excel tutorials and leave a comment with your suggestions.
