Pivot blank when change data source

Question

can't update pivot data in excel with "change data source" feature.

Answer 1

Dear @FINENKO, SONNY,
Thank you for posting your question in the Microsoft Q&A forum.

Based on your description, I understand that it appears that the PivotTable becomes blank or does not update after you use the Change Data Source option in Excel. In other words, although the new data range contains data, the PivotTable is not able to refresh or display the information correctly after the change.

To better assistance, could you please confirm the following details:

• Is the new data a normal Excel range, an Excel Table, or data created by Power Query?
• Did you change or rename any column headers?
• Are there any blank rows, blank columns, or merged cells in the data?
• Is the data in the same workbook or a different workbook?

In the meantime, please try these steps:

1. Make sure every column has a header and none are blank.
2. Remove any empty rows or columns in the data.
3. Select the data and press Ctrl + T to turn it into a table.
4. Click inside the PivotTable and select Refresh.

If the PivotTable is still blank, please try creating a new PivotTable using the new data.

Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

I truly appreciate your patience and understanding. If you have any further questions or need further clarification, please feel free to reach out. I'm looking forward to hearing from you. 

Thank you for your cooperation.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If your PivotTable is showing blank data after changing the data source, there are a few potential reasons and solutions to consider:

1. Data Source Compatibility: Ensure that the new data source is compatible with the PivotTable. If the new data source does not have the same structure or data types as the original, it may cause issues.
2. Refreshing the PivotTable: After changing the data source, you may need to refresh the PivotTable. You can do this by right-clicking on the PivotTable and selecting "Refresh".
3. Check for Filters: If there are filters applied to the PivotTable, they may be excluding all data. Check the filter settings to ensure that they are set to display the desired data.
4. Connection Issues: If the PivotTable is connected to an external data source, ensure that the connection is still valid and that you have access to the data.
5. PivotCache Issues: If the PivotTable uses a PivotCache that is not updated with the new data source, it may show blank. You can try changing the PivotCache using the ChangePivotCache method if you are using VBA.

If these steps do not resolve the issue, you may need to recreate the PivotTable with the new data source to ensure it is set up correctly.

---

References:

• PivotTable.ChangePivotCache(Object) Method
• PivotTable.ChangeConnection(WorkbookConnection) Method