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:


 Find us on Facebook      Follow us on Twitter



Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.