Link to YouTube video

How do I lock cells in Excel?

If you have created an Excel workbook with calculations in it that you don’t want your colleagues to be able to accidentally delete or overtype, you will need to lock the relevant cells.  You may be surprised to hear that by default, all the cells in Excel are locked by default!  But if that is the case, then why can they be edited?  That’s because it is a two stage process.

So let’s imagine you’ve made this sweet spreadsheet at work, and you want your colleagues to be able to update the cells A3 to B8 (the cells in white) but have the green cells locked down:

As I said, by default all the cells are actually locked already, what you need to do now is select the cells that you want to be unlocked, i.e. in this case cells A3 to B8.  Highlight and select this cell range, then right-click and select Format Cells…

Now when the Format Cells dialogue box appears, select the Protection tab, and un-tick the box that says “Locked” so now those cells are unlocked:

Now you have a sheet where all the cells are locked except the ones you want to be able to be edited.  Next, in Excel’s main ribbon, select the Review tab, and then Protect Sheet button:

Now when the Protect Sheet dialogue box appears, you just need to click OK without changing any of the other options:

You have the option of making the cells password protected, but I advise you don’t use this unless it is really important it is never edited.  If it is necessary for somebody to purposely edit your formulas at some point in the future, then without the password they will be stuck.  Leaving it without a password means that your users cannot accidentally edit or delete the cells, but if editing is necessary then they can unlock it without seeking special permission from you.

To unlock the cells again, all you need to do is go back to the Protect Sheet option again, but obviously now the button will be labelled “Unprotect Sheet”.

Leave a Reply

Your email address will not be published. Required fields are marked *