Change PivotTable source from external data source connection to table/range

Blue1971

New Member
Joined
May 19, 2020
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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.

enter image description here

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:

enter image description here


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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Any solution to this? I am in a similar situation and cannot change my data source from external to a range on another sheet.
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top