If you have a pivot table that contains sales data for several regions, can you send each manager a copy of the pivot table with their region selected in the Report Filter, and prevent them from seeing data for other regions?
The short answer is , “No!”
If you send a pivot table, with the source data saved with the file, anyone with access to the workbook can see all the source data. Even if you delete the source data sheet, it can often be recreated.
You could turn off the option to save the source data with the file, but that will prevent the region managers from refreshing or modifying the pivot table.
Worksheet Protection
One option that you might try is to protect the pivot table worksheet without allowing pivot table use. That would prevent users from selecting a different region in the Report Filter.
But, as you probably know, it’s fairly easy to circumvent Excel’s worksheet security features, and remove the worksheet protection. Once the sheet is unprotected, a different region can be selected from the Report Filter, to see a summary. Or you could double-click a cell in the Values area, and use the Show Details feature to re-create the source data.
Separate Data Sources
If you don’t want users to view the underlying data, don’t include the data in the pivot table source.
To protect the data, you could create a separate data source for each region, and base its pivot table on that source, with each pivot table in a separate, password-protected workbook. That would provide each region with a fully functioning pivot table, while maintaining each region’s privacy.
Paste As Values
As another option, if users need to see the results, but they don’t need to change the pivot table layout, you can create a static copy of the pivot table for each region.
- Select a region name from the report filter.
- Select the pivot table, right-click a selected cell, and then click Copy.
- In another workbook, right-click a cell where you would like to create a copy of the pivot table.
- In the context menu, click Paste Special.
- Click Values, and then click OK.
- Format the copied pivot table.
If this is something that you do frequently, you could create a macro to show each region , then copy and paste as values in a new workbook.
_____________________
Since I needed the table to refresh and did not want the restrictions of protecting the worksheet, I resolved this through removing the Contextual Tab and eliminating the right-click. I don’t know if this is an ideal solution.
Hi there,
Is there a way to create a pivot table as a template so whenever I alter the data this template is automatically updated in the format I have set up previously
I’ve been told just to refresh the table but I’ve tried this in many ways and for some reason it didn’t work.
I am completely new to this and have to create something in the next day or so.
I hope you can help me.
Kind regard.