Quote Originally Posted by p45cal View Post
There are several things you can do:
Do not have the source data of the pivot in the file you send.

Do not have the source data of the pivot, even if it's in another file, reachable by the recipients (when the recipent opens the file he may well get a security notice that external data connections have been disabled but offers the choise to enable them, which may mean he can gain access to the data file, especially if it's on a network).
[The user will still be able to see the name of the file it wants to connect to if he tries to change the data source of the pivot table, but he may not see its path. And you can't delete it.]

One thing you can do to retain some functionality of the pivot table is to have a copy of the data for just the relevant department within the workbook you send; this is easy if you put a grand total somewhere in the pivot table and double-click on that grand total while the pivot is filtered for the relevant department, a new sheet will be added for whatever rows of source data are needed to produce that grand total. Now change the source of the pivot table to that new sheet's data (and hide it if you want).

Most important:
Un check the option in Pivot Table Options… on the Data tab, Save source data with file,

and for good measure you can also untick Enable show details and set Number of items to retain per field to None in the dropdown.

The safest way is to eliminate the pivot table altogether; copy and paste its values only.

Oh, and if your pivot is based on the data model, make sure there is no data model in the workbook you send.

Send a pdf file to them!!
That's very interesting and definitely ideas I will take on board in the future. The problem is the source data has to be in the file. With this in mind, is there no way of locking/disabling one filter in a pivot?