I have a pivot table that uses employee salary data as the underlying
data.
I want to send around the pivot table to departments to show them some
summarised data, but due the sensitivity on the underlying information
want to ensure they can't get to the data.

I have tried unchecking the "Enable drill to detail" and even deleting
the Data Sheet, however have noticed that if someone goes and checks
the option again they have access to the underlying information.

Anyway to create the pivot once and detach from the underlying data ?

Unless you indicate otherwise to Excel, it creates a hidden copy of the
source data (the Pivot Cache) when you build a Pivot Table. That's why you
need to Refresh the Pivot Table data after you change the source data....So
Excel can refresh the Pivot Cache and display the changes.

Try this:
When creating or editing the Pivot Table..

On Step 3 of 3:
Click the [Options] button
UNcheck: Save data with table layout
UNcheck: Enable drill to details
Click the [OK] button
(Now there will be no pivot cache associated with the Pivot Table)

Then delete the sheet in the workbook that contained the sensitive data and
save the file.

TEST to make sure I didn't miss anything.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

I have a pivot table that uses employee salary data as the underlying
data.
I want to send around the pivot table to departments to show them some
summarised data, but due the sensitivity on the underlying information
want to ensure they can't get to the data.

I have tried unchecking the "Enable drill to detail" and even deleting
the Data Sheet, however have noticed that if someone goes and checks
the option again they have access to the underlying information.

Anyway to create the pivot once and detach from the underlying data ?

One more thing....If you're working with an existing file, make sure the
users cannot access the source file...Otherwise, they can just refresh the
Pivot Table to get the data. (Excel will remember the most inconvenient
things, sometimes.) Another option is to MOVE the original file to the stored
link will be invalid.

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Unless you indicate otherwise to Excel, it creates a hidden copy of the
source data (the Pivot Cache) when you build a Pivot Table. That's why you
need to Refresh the Pivot Table data after you change the source data....So
Excel can refresh the Pivot Cache and display the changes.

Try this:
When creating or editing the Pivot Table..

On Step 3 of 3:
Click the [Options] button
UNcheck: Save data with table layout
UNcheck: Enable drill to details
Click the [OK] button
(Now there will be no pivot cache associated with the Pivot Table)

Then delete the sheet in the workbook that contained the sensitive data and
save the file.

TEST to make sure I didn't miss anything.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

I have a pivot table that uses employee salary data as the underlying
data.
I want to send around the pivot table to departments to show them some
summarised data, but due the sensitivity on the underlying information
want to ensure they can't get to the data.

I have tried unchecking the "Enable drill to detail" and even deleting
the Data Sheet, however have noticed that if someone goes and checks
the option again they have access to the underlying information.

Anyway to create the pivot once and detach from the underlying data ?