In Excel 2016, I have several PivotTables (and corresponding PivotCharts) that use external data sources. The data sources were created via Microsoft Query (an Excel mechanism) and have complex SQL logic that queries an Oracle database.
For certain PivotTables, I want to change the data source from using an external data source called ChartsConn to using an Excel Table.
Reason:
The ChartsConn external data source connection is redundant and should be removed — so that future SQL changes in connections only need to be done in one place. I already have a connection that is exactly the same called ListConn; I want to use it instead.
If I try to change the PivotTable data source to ListConn, it doesn’t work the way I want it to. Excel makes a copy of the ListConn connection called ListConn1, which isn’t what I want.
So, alternatively, I want to connect to an existing Excel Table (in a different sheet) that already uses ListConn. If I connect to that Excel Table, then I believe Excel will do it without creating a new connection like ListConn1. It will re-use the existing ListConn connection, as expected.
Problem:
When I try to change the PivotTable data source (Analyze > Change Data Source), the option to choose an Excel Table is disabled:
Question:
Is there a way to change a PivotTable’s source from an external data source connection to an Excel Table?
If not, it seems like the only other option is to recreate my multiple PivotTables and PivotCharts from scratch. The only difference is I’ll use the Excel Table as the source — by clicking in the Excel Table and clicking Create Pivot Table.
That would work, but would take a lot of effort since there are multiple PivotTables and corresponding PivotCharts, with very specific configurations.
