Excel 2007 Protecting Data From Being Viewed

Posted on 2011-04-27
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

    Please take a look at this article:

    LVL 85

    Assisted Solution

    by:Rory Archibald
    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 31

    Accepted Solution

    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
    LVL 1

    Author Closing Comment

    Thanks to all for your suggestions.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    Vb script to unzip a files and rename the files 12 49
    Vb  script to restart the services 13 48
    Excel 2007 problem 2 21
    Posting V12 2 16
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now