PivotTable Questions - Showing Imbedded Source Data & Hiding/Protecting Columns (even in Drilldown)

[mikey777]

Hi, I'm new to Excel (2010) PivotTables and need some help.

I inherited a file which has multiple worksheets, some containing PivotTables. The PivotTable has a great feature to Drilldown and see the details. The source data seems to be included in the sheet but I cannot locate it. The external reference is no longer available when I try to change the pivot table source.

Also, the data contains sensitive information (salary) and I want to hide & protect it from view. I need to keep the sum of salaries intact in the pivot but I don't want the drill down to show individual salary. Is there a way to do this?

Please help! Thank you.

 

[Jerry Sullivan]

Hi mikey777,

Here's a good article from DataPig.com that explains how the PivotCache works.
Cut the Size of Your Pivot Table Workbooks in Half » Bacon Bits:

If you want to keep the sensitive data confidential, you should not let it be saved in the PivotCache.
Your safest option is to copy-paste the data.
You can save the PivotTable without embedding the data- but that's takes away the functionality of a PivotTable, so IMO, you are better off copy-pasting to avoid the risk of accidently having the embed data box checked.

If you want the functionality of a PivotTable to be analyze data at aggregated levels (let's say by Department or Location), then you should do that aggregation prior to creating your PivotTable.

 

[mikey777]

Thank you very much Jerry!