Hidden Tips for Hiding Data
Q: I have a worksheet that has columns hidden, and the worksheet is protected by a password. I found that if I highlight the columns and copy to a new worksheet, I can unhide the hidden columns, and the data is available to the user. How do I protect my information and avoid embarrassment with hidden columns?
A: You have stumbled onto a little-known issue. Excel 2003, 2007 and 2010 allow users to select locked and hidden columns in a protected worksheet that are sandwiched between unlocked columns. Because the locked and hidden columns can be selected, they can then be copied and pasted into a new worksheet to reveal confidential content.
The most obvious answer to protecting sensitive data is to delete the sensitive data before sharing the worksheet. However, I assume that you prefer to keep the worksheet intact; therefore, below are suggestions for working around this problem:
1. Start by unlocking all cells in your worksheet. (To do this in Excel 2003, 2007 and 2010, select the entire worksheet by right-clicking in the upper-left corner of the worksheet area, select Format Cells, click the Protection tab, and uncheck the box labeled Locked.)
2. Next, lock the cells in the columns you wish to hide. (To do this in Excel 2003, 2007 and 2010, select the columns to be hidden and right-click in the column label area of the selected range, select Format Cells, click the Protection tab, and check the box labeled Locked.)
3. Finally, turn on Worksheet Protection, making sure to uncheck both the Select locked cells and Select unlocked cells boxes, as follows:
a. Display the Protect Sheet dialog box as follows:
i. In Excel 2003, select Tools, Protection, Protect Sheet from the main menu.
ii. In Excel 2007 and 2010, select Protect Sheet from the Review Ribbon.
b. Next, uncheck the boxes labeled Select locked cells and Select unlocked cells, type in a password in the Password to unprotect sheet box, click OK, type in the password again to verify, and click OK (see screenshot on the next page).
This will prevent users from selecting the locked and hidden columns; accordingly, those locked and hidden columns are no longer available to be copied and pasted into another worksheet to reveal their contents.
4. Please note that this procedure has a flaw. It also prohibits the user from selecting unlocked cells in the worksheet that are not hidden. Therefore, you may want to make the following slight modification to the procedures described above:
a. Position your hidden column in column A (and, if applicable, position any additional hidden columns next to column A). This will prevent users from selecting hidden columns sandwiched between nonhidden columns.
b. When protecting your worksheet, leave a check mark in the box labeled Select unlocked cells. This will still prohibit users from selecting locked and hidden columns, but it will allow the user to access all of the unlocked cells in the remainder of the worksheet.
More from the JofA:
