How do you protect cells on a spreadsheet so that people can only select certain cells? How do you protect cells within a spreadsheet so other people can’t delete your formulas? Gain control of your spreadsheets! The best way is to protect your cells by unlocking them. Check out how to first unlock cells and then protect the sheet with a password. Then, users only have the ability so select the cells that you decide.
Take this Employee Mileage Reimbursement Form, for example. We only want staff members to be able to enter their name, month, commute miles, and other specifics about the expense submission. We don’t want them to be able to select cells that include formulas…only constant values that pertain to them. Once protected, the best practice is to save this document as a template.
1. First, select the ranges of cells that you would like users to be able to select and enter data. Hold down the Ctrl key to select non-contiguous ranges as seen in the screenshot below.
2. Right click on any area that is selected and choose Format Cells.
3. Click on the Protection tab within the Format Cells dialog box. You’ll see that, by default, all cells are locked. Uncheck the Locked checkbox and click OK. Now, the selected cells will be Unlocked and all other cells will be Locked. Locking cells or hiding formulas has no effect until you protect the worksheet.
4. On the Review tab, select Protect Sheet.
5. Be sure that Protect worksheet and contents of locked cells in checked and only check the Select unlocked cells box. Any boxes that are check are things that users are allowed to do. By checking Select unlocked cells, nothing else on the protected sheet can be selected and there is no way to have important formulas or other structural items to be deleted. Create a password if you would like and click OK.
6. To unprotect the sheet, click on Unprotect Sheet on the Review tab. Type in your password and the sheet is back to original permissions.
Did you find this helpful? Would you like to learn more about Excel? Watch the video that has been downlaoded over 1 million times "10 Microsoft Excel Aha! Features."
