Excel 2007 Protecting Data From Being Viewed


Can anyone recommend options on how to protect a sheet of data from being viewed by anyone except a central admin that is more secure than just hiding the sheets in the advanced settings?  For example, I have a sheet that will contain sensitive data for multiple departments and within the report there are dynamic charts that will allow comparisons between departments on specific factors.  I cannot have the end-users look at the raw data tab and see everything so I need to find a way to protect it while at the same time allowing pivot tables that are feeding my charts to use the data.  

Any suggestions?  VBA coding is an option if anyone has ideas.

Who is Participating?
Rob HensonFinance AnalystCommented:
Sheets within a workbook can be set as Very Hidden, they then don't show in the Unhide Sheet window. This has to be done through the VB Editor, Properties Pane.

If a Pivot table is set on this data, a double click on the Pivot Table will extract a copy of the relevant data lines unless the setting within Table Options is disabled.

Although this would get the result you require, anyone with a bit of Excel knowledge would be able to get round it.

You could enable password protection but again this is also not fool proof.

If the data is held in a separate file in a directory which has access rights only to certain people, the tables in your file will only be updated when accessed by somebody with access to the raw data.

Hope this helps.

Rob H
Please take a look at this article:


Rory ArchibaldCommented:
Don't put the data in the workbook - extract it from a separate data source. Any data in the workbook can be got at.
EscanabaAuthor Commented:
Thanks to all for your suggestions.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.