And now, all the cells in this worksheet are protected except the selected ones. Protecting your Excel worksheet prevents others from making changes to the original data. If someone knows any other way to protect cells in Excel, your comments will be truly appreciated. This is important - if someone tries to change a locked cell, Excel will provide a helpful message which tells them that the cell is protected but also how to remove the protection. Very involved worksheets can include a combination of formulas and information that is very complex, and even slight changes to certain cells could lead to disastrous occurrences for the rest of the data on the sheet. It is recommended to protect a sheet with a different password than you used to unlock the range s. For the locking of the cells to take effect, you must also protect the worksheet.
By default, Excel locks all the cells in a protected worksheet and then you can specify which cells you want to unlock for editing if any. I use this data in some formulae and give output. If you're allowing a user to enter a value, be sure you consider how large a value you want to allow the spreadsheet to display. I would advise you to consider saving this file as a Template. If you have finalized such a complex worksheet and want to ensure that particularly important data is not altered, then you need to learn how to lock a cell in Excel 2010. Next, you'll have to select and unlock the cells you want to allow the user to edit those without formulas.
So to override the default and unlock cells, highlight the entire worksheet by clicking the upper left corner box where column headings and row headings met. The other option is I have to allow users to select locked cells in protect sheet to enable adding new input. Or, simply type the user name as it is stored on your domain, and click the Check Names button to verify the name. The selected cells are locked and protected from any changes, while all other cells in the worksheet are editable. If you want to allow others to add or change data in only certain parts of the worksheet, you can unprotect specific ranges of cells.
Make sure you check out all the honorable mention below which include many additional tips from our members. Next, click on the Error Alert tab. And type the password and confirm it, see screenshot: 6. In this example, we've setup the cells to allow a positive number. However, in some cases you may not want to go that far and lock the entire sheet.
To test your settings, go through the motions of locking the spreadsheet, but leave the password field blank. You will be prompted to enter the password optional and select the actions you want to allow users to perform. If you want only to unlock input cells without changing cell formatting, uncheck all boxes on the Style dialog window other than the Protection box. SpecialCells xlCellTypeFormulas If Not rng Is Nothing Then rng. I thank you for reading and hope to see you on our blog next week.
To Be Appreciated By Your Boss And Colleagues So That Get A Promotion And Salary Increase. In the Format Cells dialogue box, choose Protection. However, if you have more than one column of data, the above approach is not the best way to go. Locking cells in Excel has no effect until you protect the worksheet. This makes it easy for users to get to areas that they need to enter data.
About the Author Alan Sembera began writing for local newspapers in Texas and Louisiana. Highlight the Protection submenu 6. You can find the detailed instructions with screenshots in this tutorial:. Then tick the Locked box. . Right click the sheet tab, select View Code from the right-clicking menu. Or register at it's free and post a message in the Excel forum.
If you don't have much experience with Excel conditional formatting, you may find the following step-by-step instructions helpful:. These are the cells you want to allow users to edit when the worksheet is protected. Select cells, ranges, columns or rows you want to protect. Don't forget your password because there is no way to retrieve it. This can be upon entry or upon save, but I need to have the remaining blank rows to be usable whenever the sheet is opened. You can enter a password or leave it blank to test it out.
SpecialCells xlCellTypeConstants If Not rng Is Nothing Then rng. And a new Design tab will be displayed, and then click Selection Unlock to unlock the certain selected cells, see screenshots: 4. I would be most grateful. But first, an overview: Protection in Excel works on three levels: 1. You can modify them as you need.
Your first step is to build the spreadsheet the way you want it to with the formatting and formulas necessary to calculate what you need. How to lock cells in Excel other than input cells When you've put a lot of effort in creating a sophisticated form or calculation sheet in Excel, you will definitely want to protect your work and prevent users from tampering with your formulas or changing data that shouldn't be changed. What if you only want to protect a single, or several single cells, in an entire Excel worksheet and allow users to edit or input data in the rest of the sheet? Having said that, there are a number of tools available to crack Excel and Word generated passwords. An additional feature found in the Protection tab of the Format Cells menu choice is a check-box for Hidden. If you want to allow users to edit specific cells or ranges on a password-protected sheet, check out the following section. This function will take effect only if the worksheet has been protected, and it covers all cells in the protected worksheet or workbook. This can be confusing, but Microsoft designed it this way, and we have to play by their rules : On the Review tab, in the Changes group, click the Protect Sheet button.