Sharing spreadsheets with colleagues can sometimes lead to important formulas being accidentally overwritten. Locking the workbook is one solution, but this means that no one can work on any part of spreadsheet without the password.
A better solution might be to selectively lock your spreadsheet, leaving some cells open and editable without a password. Sounds complicated right? In fact, it’s one of the simplest things you can do in Excel!
Step 1: First we need to select the cell or range of cells which we DON’T want to be protected. In the example below we chose a range of cells which all contained formulas. This is perhaps counter-intuitive, but you’ll see why in a moment!
Step 2: Next we right-click the mouse and choose Format Cells, then select the Protection tab. You will see two tick boxes marked Locked & Hidden.
Step 3: Now untick the box marked Locked and then click Close.
Step 4: Next select the Review tab of the ribbon menu and click the Protect Sheet button.
Step 5: Enter a password, click OK and then confirm your password.
By default, all cells in Excel are marked as protected. This means that when we apply a password, the entire worksheet is locked. What we have done is tell Excel that there are some cells which we don’t want to protect – simple!
I hope this is might save you the hassle of repairing damaged formulas and data. If you find it useful, do please share with your colleagues!