MS Excel Locking Made Easy: Lock Cells & Unlock Sheets

“HELP!  Sometimes when I am answering an Excel RFP, a cell suddenly becomes locked, making it impossible to edit it afterward.  What causes this?”  Having an Excel RFP suddenly lock answer cells is exasperating to say the least.  But there is a way to spot it and avoid the trap.

Excel RFP’s are quite often released with most or all the sheets protected, and cells that contain questions and instructions locked against editing.  This means that only the cells that are intended to contain the RFP responses can be edited.

However, there are situations in which unlocked, editable cells suddenly become locked, seemingly on their own, making further edits impossible.  Though this may seem like an intentional restriction placed by the RFP issuer, it is in fact an unintended consequence of a little known feature in Microsoft Excel.

Why does Excel change a cell from ‘unlocked’ to ‘locked’?

An Excel RFP worksheet that has been protected, may reset an unlocked cell to locked, after content has been pasted into it.   This happens when the content contains formatting, which can trigger Excel to reset its own formatting defaults.  If one of its formatting defaults is to formatting cells to ‘locked’, then this will be reapplied.

How do I know if ‘Locked’ is a default property of an Excel Workbook?

Add a new sheet to a workbook.  Select a cell at random and click on Home Tab, Format dropdown button.  Check the ‘Lock Cell’ button under ‘Protection’.

If ‘Lock Cell’ is turned on, then ‘locked’ is a default property:

How do I change Excel’s default formatting properties?

To change a workbook’s default formatting properties, go to the Home tab and click on ‘Cell Styles.’ A number of style options will appear: 

You will also see a button named ‘Normal.’  This is similar to the Normal template in Microsoft Word in that it controls how new worksheets are formatted.  Right Click on the ‘Normal’ button and select ‘Modify’ from the dropdown.

This will display a list of options.  As is typically the case, the checkbox for ‘Locked’ is checked:

If you uncheck this box, unlocked cells will NOT be prone to accidental reformatting back to locked and new workbooks, and sheets will be defaulted to ‘unlocked.’  

Note: You may NOT change the default properties if the sheet is protected.

How do I avoid triggering the self-locking in a protected Excel RFP?

Since you cannot change these settings in a protected worksheet, you need to take safeguards to prevent this from happening when you insert responses into a protected RFP.  Because the problem is triggered by pasting formatted content there are three ways to avoid the problem:

Why do Excel RFP’s get released with the self-locking property turned on, if it causes so many problems?

It is unlikely that most RFP issuers know about this problem with Excel’s default formatting properties.  It is a little known ‘feature’ of Excel.  In fact, some RFP issuers warn responders against pasting answers into the RFP spreadsheet.  This is most likely because they have heard of the problem but don’t know the cause (or they would likely simply uncheck the ‘locked’ formatting box before protecting the sheet.)