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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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