by Allen Wyatt
(last updated December 11, 2018)
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:
Figure 1. The Protection tab of the Format Cells dialog box.
Figure 2. The Protect Sheet dialog box.
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:
Figure 3. The Allow Users to Edit Ranges dialog box.
Figure 4. The New Range dialog box.
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, 2016, 2019, and Excel in Office 365.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
If you share a workbook with others in your office, you will probably want to make sure that some of the worksheets don't ...Discover More
Want to hide certain columns within a worksheet so the contents are not visible to others? The answer lies in formatting ...Discover More
You can protect various parts of your worksheets by using the tools built into Excel. One thing you can protect is the ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.