Password Protecting Specific Columns in a Worksheet

by Allen Wyatt
(last updated March 10, 2018)

6

Mary Lou wonders if there is a way she can password protect certain columns in a shared workbook. The workbook has only a single worksheet, and she needs to protect columns E and J so they cannot be changed, unless the user knows a particular password.

The traditional way to approach this challenge is to follow these steps:

  1. Select all the cells in your worksheet. (Pressing Ctrl+A will do the trick.)
  2. Press Ctrl+Shift+F. Excel displays the Format Cells dialog box.
  3. Make sure the Protection tab is displayed. (See Figure 1.)
  4. Figure 1. The Protection tab of the Format Cells dialog box.

  5. Make sure the Locked check box is cleared. (You may need to click it a few times to get it actually cleared.) This removes protection from all the cells in the worksheet.
  6. Click OK to close the Format Cells dialog box.
  7. Select column E.
  8. Press Ctrl+Shift+F. Excel again displays the Format Cells dialog box.
  9. Again make sure the Protection tab is displayed.
  10. Make sure the Locked check box is selected. This protects the cells that are currently selected.
  11. Click OK to close the Format Cells dialog box.
  12. Select column J and repeat steps 7 through 10.
  13. Display the Review tab of the ribbon.
  14. Click the Protect Sheet tool. Excel displays the Protect Sheet dialog box. (See Figure 2.)
  15. Figure 2. The Protect Sheet dialog box.

  16. Make sure the top check box (Protect Worksheet and Contents of Locked Cells) is selected.
  17. Enter a password in the appropriate place.
  18. Use the check boxes to fine tune how you want protection applied.
  19. Click OK. You are again asked to enter the password.
  20. Enter the password you used in step 15 a second time. Your worksheet is now protected.

The result of going through all these steps is that the cells in columns E and J cannot be changed. If someone knows the password you used in step 15, however, they can unprotect the worksheet (the proper control is on the Review tab of the ribbon) and make any changes they want. If someone does make changes in this way, they will need to reapply the protection (steps 12 through 17) prior to saving the workbook. If they don't, then the next time the workbook is open, the worksheet remains unprotected and anyone can change the contents of columns E and J.

As I said, the above represents the traditional way to approach the problem. There are non-traditional ways you can use, as well. For instance, you might rethink how your data is put together and, perhaps, move the contents of columns E and J to another worksheet or even to another workbook. You can then protect that information and simply reference it within the current worksheet.

A third approach is to use a tool introduced with the release of Excel 2010. This new tool, which allows you to password-protect ranges of cells, requires a variation on the steps presented earlier in this tip. The steps will be many, but it does provide for a more flexible way of dealing with the protection issue. Here is the variation that includes use of the new tool:

  1. Select all the cells in your worksheet. (Pressing Ctrl+A will do the trick.)
  2. Press Ctrl+Shift+F. Excel displays the Format Cells dialog box.
  3. Make sure the Protection tab is displayed.
  4. Make sure the Locked check box is cleared. (You may need to click it a few times to get it actually cleared.) This removes protection from all the cells in the worksheet.
  5. Click OK to close the Format Cells dialog box.
  6. Select column E.
  7. Press Ctrl+Shift+F. Excel again displays the Format Cells dialog box.
  8. Again make sure the Protection tab is displayed.
  9. Make sure the Locked check box is selected. This protects the cells that are currently selected.
  10. Click OK to close the Format Cells dialog box.
  11. Select column J and repeat steps 7 through 10.
  12. Display the Review tab of the ribbon.
  13. Click Allow Users to Edit Ranges. Excel displays the Allow Users to Edit Ranges dialog box. (See Figure 3.)
  14. Figure 3. The Allow Users to Edit Ranges dialog box.

  15. Click the New button. Excel displays the New Range dialog box. (See Figure 4.)
  16. Figure 4. The New Range dialog box.

  17. In the Title box, enter the name you want to use for this range. (The title isn't particularly important. Use something that is meaningful to you.)
  18. In the Refers to Cells box, enter the following: =$E:$E,$J:$J
  19. In the Range Password box, enter the password you want to give to those who should be able to edit columns E and J.
  20. Click on OK. You are again asked to enter the password.
  21. Enter the password you used in step 17 a second time. The range now appears in the Allow Users to Edit Ranges dialog box.
  22. Click OK to close the Allow Users to Edit Ranges dialog box.
  23. Click the Protect Sheet tool. Excel displays the Protect Sheet dialog box.
  24. Make sure the top check box (Protect Worksheet and Contents of Locked Cells) is selected.
  25. Enter a password in the appropriate place. This should not be the same password you used in step 17 and step 19; it should be different.
  26. Use the check boxes to fine tune how you want protection applied.
  27. Click OK. You are again asked to enter the password.
  28. Enter the password you used in step 23 a second time. Your worksheet is now protected.

The advantage to this approach is that you have two levels of passwords—one for the range (columns E and J) and one for the worksheet as a whole. As someone is using a worksheet protected in this manner, when they try to modify a cell in columns E or J, they are asked for a password. If they supply the correct password (this is the one that you specified in steps 17 and 19), then they can edit anything they want in columns E and J. When the workbook is saved, exited, and reopened, then the protection is automatically reset and columns E and J can again only be edited if the password is known—there is no need for the user to explicitly "reprotect" the worksheet before saving. Plus, you never have to give out the password that you used to protect the entire worksheet.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8388) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Flipping a Drawing Object

Place a drawing object in your document, and it doesn't have to stay that way. You can flip a drawing object in either of ...

Discover More

Adding Caption Labels

When using the captioning capabilities of Word, you aren't limited to the three default caption labels provided in the ...

Discover More

Using Object Anchors

An object anchor is used to signify the point at which an object is inserted into a document. If you want to see these ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Visually Showing a Protection Status

Need to know if a worksheet or workbook is currently protected? Excel provides some tell-tale signs, but here are some ...

Discover More

Locking Worksheet Names

Want to stop other people from changing the names of your worksheets? You can provide the desired safeguard by using the ...

Discover More

Forcing a Worksheet to be Protected Again

Excel allows you to protect your worksheets so they can only be changed as you want to have happen. If you unprotect a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four more than 2?

2018-06-10 10:45:41

Robert Cline

I tried to follow the instructions to protect specific cells. Something got corrupted and my modules got protected. None of the passwords I would have used work. Is there a way I can removed passwords from this workbook?

Thank you.

Robert


2018-03-12 01:50:11

David Gray

Thanks, Allen. I didn't notice that,, since the steps were so similar.


2018-03-11 09:14:55

Allen

David,

It's name is "Allow Users to Edit Ranges." See steps 13 through 20 in the second set of steps.

-Allen


2018-03-10 21:35:38

David Gray

The tip mentions a new tool that came with Excel 2010. What, pray tell, is its name?

For Bruno, short of writing a macro, I am unaware of any way to apply protection settings to two or more sheets in one go.

For Walter, yes, a determined hacker can break the Excel password protection, without venturing into the Dark Web. I have in my possession one of several such modules that are readily available. For information that is truly private, it has no business in a spreadsheet. Information that needs that level of protection would need to be encrypted in ways that would be nontrivial to use with Excel. Theoretically, a VBA module that used the CryptoAPI libraries to encrypt the data could be used to encrypt data within a specific range. However, the workbook wound need a way to decrypt it every time it is needed to calculate a worksheet. Storing the key required to decrypt the data in the worksheet would defeat the purpose of encrypting the sensitive data.

That said, I have a couple of workbooks that contain sensitive information, but the entire workbooks are encrypted, the key is known only to me, and it isn't written down anywhere. When I die, that data dies with me, which is my intention.


2018-03-10 16:03:24

walter

How secure is this protection?

Is this mostly to protect the integrity of the worksheet from inadvertent keyboard / mousing errors?

I'm told that a determined hacker can easily overcome Excel's password protection.

If so, then you should never use these techniques to protect really private data.

Is there a simple way to use encryption to protect Ranges?


2018-03-10 11:45:50

Bruno

Hi, I have a workbook in which I want to protect the same cell range on several worksheets. Do I need to apply the protection sheet by sheet (there can be up to 10 sheets I need to protect) or is there a way to do this in one go?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.