Excel 2007 Protecting Data From Being Viewed

Posted on 2011-04-27
Medium Priority
Last Modified: 2012-05-11

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.

Question by:Escanaba
LVL 33

Assisted Solution

jppinto earned 400 total points
ID: 35475401
Please take a look at this article:


LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 total points
ID: 35475855
Don't put the data in the workbook - extract it from a separate data source. Any data in the workbook can be got at.
LVL 34

Accepted Solution

Rob Henson earned 1200 total points
ID: 35476100
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

Author Closing Comment

ID: 35476351
Thanks to all for your suggestions.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question