Excel for Mac External Data Connections Have Been Disabled

Question

I am getting this unwanted security warning. How do I stop it?

Excel for Mac 16.20 (181202) under Mojave 10.14.1. Everything is current

Answer 1

HI again,

Please keep in mind that I do not work for Microsoft. Apparently you told Gloria about the copy/paste of the pivottable, but I am not privy to that information. Unless you share all the information here in the forum, I can't possibly know about it. It is difficult for everyone when a Microsoft agent takes a conversation private and doesn't fill in the gaps in the forum.

I am able to reproduce the problem you described by using the default paste options (Use destination Theme) when I paste a PivotTable from one workbook to another.

It appears to me that when you paste a PivotTable this way, even if you don't choose the Link Cells option, the result is an OLE (Object Linking and Embedding) object. Like you, I could not find any way to break the link to the source file.

There are two kinds of "expected behavior" that I think come into play here.

1. Does Mac Excel work the same as Windows Excel in this regard (it does)
2. Does the way Excel works match what a user would expect (it does not)

Since the way PivotTables work has not changed in decades, I suspect Microsoft would be hesitant to make changes to anything as complicated as PivotTables. Even though it doesn't make sense the way it is presented to the user, my guess is that if they did anything, Microsoft would simply tell users that the PivotTable is linked and leave it at that.

I did find thisset of instructions:

Break links of Pivot Tables

If the data source of Pivot Tables is in another workbook you can break this link too. Therefore, follow these steps:

1. Find out if the data source of your Pivot Table is located on another workbook as described in this article.
2. If the Pivot Table links to another workbook you have two options:
   1. Set another data source within your current workbook.
   2. Remove the Pivot functionality and copy and paste the complete Pivot Table as values.

Neither option 1 or 2 sounds particularly useful, though.

There is a feature request you can vote for here:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/17178389-break-link-from-source-data-to-pivot-table

Short answer seems to be:

You can't break the link.