CODEWITHSUNDEEP
excelms-accesspivotpivot-table
Tourtelot
Tourtelot

Reputation: 137

Changing data source for PivotTables

I currently have three tabs in an excel workbook. Sheet 1 and Sheet 2 both contain pivot tables, based on the range of data in Sheet 3. The data set has grew to over the 1 million row limit. To account for this I have moved the data into Access.

I have deleted the old Sheet 3 and added Sheet 4 where I make a external connection to the Access DB and also add the data to the Data Model. I can see the connection listed in Data > Connections.

When I click into the PivotTable and Analyze > Change Data Source. I am unable to change from "Select a table or range" to "Use an external data source".

Any ideas?

Upvotes: 1

Views: 1062

Answers (1)

June7
June7

Reputation: 21379

Because that pivot table is set up for table/range it can only change to another table/range. Have to build a new pivot table based on connection. Then for that pivot table "Use an external data source" option will be available but not "Select a table or range" option.

Upvotes: 1

Related Questions