• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide and Lock a Column

Satyaprakash

New Member
Hi,

I have an excel sheet which I have to share with some people. However I want to hide a column because I do not want anyone to see it. Hide, not delete. Is it possible to hide a column and lock it so that only one with the password can see it.

Regards,
Learner
 
  1. Select the column you want to protect.
  2. Choose Cells from the Format menu. Excel displays the Format Cells dialog box.
  3. Make sure the Protection tab is displayed. (See Figure 1.)
    T2781F1.jpg


    Figure 1. The Protection tab of the Format Cells dialog box.
  4. Make sure both the Locked and Hidden check boxes are selected. (It is the Hidden check box that controls whether the cell contents are visible in the formula bar or not.)
  5. Click OK to dismiss the dialog box.
  6. With the column still selected, choose Format | Column | Hide. Excel hides the column.
  7. Choose Tools | Protection | Protect Sheet. Excel displays the Protect Sheet dialog box. (See Figure 2.)
    T2781F2.jpg


    Figure 2. The Protect Sheet dialog box.
  8. Enter a password to be used in protecting the worksheet.
  9. Using the check boxes, select what type of protection you want applied to the worksheet. At a minimum you should choose Select Locked Cells and Select Unlocked Cells. (These two options are selected by default in a worksheet.)
  10. Click OK to dismiss the dialog box. You are prompted to reenter your password (the one from step 8), which you should do.
At this point someone cannot view what is in the hidden column, even if they use F5 to jump to one of the cells in the column; it still won't appear in the formula bar. There is one caveat to all this. If you have some cells in the worksheet (or workbook) that are unlocked, so that the contents of the cell can be changed, it is still possible to see what is in individual cells of the column. How? Two methods, really:

  • In the unlocked cell, enter a formula that references a locked and hidden cell. For instance, if column E is locked and hidden, entering the formula =E3 will display, in the unlocked cell, the value in cell E3.
  • Copy the contents of the locked and hidden cell and paste it in the unlocked cell. Use F5 to jump to the locked and hidden cell, then press Ctrl+C, move to the unlocked cell, and press Ctrl+V. The contents of the locked and hidden cell are pasted in the unlocked cell.
The bottom line is that it is virtually impossible to 100% protect the contents of the column so that they cannot be viewed. Using the protection features of Excel makes it more difficult, but a determined user may be able to still view the contents in the described manner.
 
The thing is I want people to work on this sheet but do not want them to see what is in the hidden column. When I follow the steps above, I am not able to use filters. Is there any work around for this. (In the protect sheet box I checked everything except select locked cells)
 
The bottom line is that it is virtually impossible to 100% protect the contents of the column so that they cannot be viewed. Using the protection features of Excel makes it more difficult, but a determined user may be able to still view the contents in the described manner.

Excel was never designed for multi users, it's protection is about as much use as an ashtray on a motorcycle.

Far better to import data in to Access and set up a form for user input.


.
 
Back
Top