Pivot Table External Data Connection is greyed out

[dummy777]

I have an excel with pivot table and the data source is only a table/range within that worksheet. I searched that I can use other sources as my data source. However upon checking and try to change the data source from "select a table or range" to "use an external data source" it is greyed out and won't allow me to change it to external data source. Is there a way to update it or I need to create a new worksheet/pivot to use the external data source option?

My second question is that, when I do that does the refresh needs a few minutes and would automatically updated even if the "external source data" is closed? Also can I use it multiple times? Then is it ok if there are many users will refresh it? tia.

[noboffinme]

Hi dummy777,

I'd suggest you decide whether you want the connection to be either a table within the workbook OR an external source - I assume that's why it would be greyed out.

Can you explain what you're trying to do so the forum can get help you?

[dummy777]

I wanted to edit the pivot so that I won't create a new one with same parameters and only change the source from table/range to external source. And it seems like I need to do it from scratch if I wanted to use external source. Thank you.

If that's the case, does the data refresh the pivot table even I the external source is closed?

[noboffinme]

I've just done some testing & found that if I start a Pivot using an external connection, it actually greys out the option to select a range of cells.
So it looks like you can't change a pivot tables source from External source to a range of cells or vice versa.

Re your question about refreshing an external source, the pivot, once refreshed by you will only update what it has access to, so therefore if it's disconnected, it will probably throw an error about the source not being available.

[dummy777]

Thank you very much!