Hidden Tips for Hiding Data

BY J. CARLTON COLLINS

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

 

FEATURE

Tackling TCJA changes this tax season

Return preparers must be ready for how the Tax Cuts and Jobs Act has modified many common features of individual and business returns.

PODCAST

Why CPAs can’t wait on automation tools

What do accounting firms waiting on others to develop AI, automation, and data analytics tools have in common with a baseball fan sitting in a stadium filling with water at an exponential rate? The answer could determine your firm’s fate.