Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lock Pivot Tables? (Excel 2002/XP)

    I have created a pivot table with sensitive data. I would like to be able to create a version of the table and provide it to someone with limited (or no) ability to modify the data being displayed in the table. Is there a way to lock fields so they cannot be changed by the viewer?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    Do you mean that the user shouldn't be able to change the underlying data on which the pivot table is based? Or that the user shouldn't be able to change the pivot table itself?

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    Does Tools|Protection|Protect Sheet do what you want?
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    If you mean that you have, for example, departmental salaries in a pivot table and you're currently displaying one department, and you'd like to send that out to the department but prevent them from being able to see other departmental information, this can be done by double clicking the total in the bottom right and creating a new pivot table based off only that department's data. I believe this is how a company I used to work for handled such situations.

  5. #5
    New Lounger
    Join Date
    Sep 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    I don't want them to be able to alter the pivot table itself to display data other than what I have filtered for them to see.

  6. #6
    New Lounger
    Join Date
    Sep 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    Not as far as I can tell. It just prevents displaying the underlying data by clicking on a cell.

  7. #7
    New Lounger
    Join Date
    Sep 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    Thanks. I will try this. This would seem to do what I want. Will let you know if it works.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    You could copy the pivot table to the clipboard, then Edit | Paste Special..., Values into a new workbook. You can follow it by Edit | Paste Special..., Formats in the same location to preserve formatting. You can then lock the worksheet in the new workbook if you wish.

  9. #9
    New Lounger
    Join Date
    Sep 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    Thanks, I think that would work but would require that it be done over again every time the master table is updated. The method proposed by BAN will, I think, be harder to build in the first instance but would allow for automatic refresh of the data (I think).

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    You can use the "camera tool" and take a "picture" of the pivot table. (John Walkenbach has some infoon the camera tool.)

    The picture is "live" and will update as the pivot is updated, but since it is picture it prevents viewing any of the underlying data. the actual pivot table could be created on a hidden sheet and only allow the user to view the "picture"

    Steve

  11. #11
    New Lounger
    Join Date
    Sep 2004
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lock Pivot Tables? (Excel 2002/XP)

    Apparently there are at least three ways of doing this that I was unable to figure out! Thanks for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •