I have decided to tackle an advanced Excel question this week.  People want to know how to keep others from making changes in some cells of a spreadsheet.  They also wish to know how to allow edits of other cells.  This is a two step process.  I will be using Excel 2007 for this explanation.

You need to understand that all cells in Excel are “locked” by default.

Locked means once you “protect” the sheet (second step) you will not be able to edit the cells.  So you will not be able to write anything in them.  In a locked cell any existing formulas will recalculate if necessary; however, you cannot edit it…once the sheet is protected.

In cell A1, type “Protected Sheet” and press enter.  This will be our title for the spreadsheet.

Next, you need to “unlock” any cells that you want people to be able to edit after you have protected it.  For this example, I will say that I only want a person to be able to type in cell A5. Start by selecting A5.  Now, from the Home tab and the Cells group click on Format and look down the list near the bottom.  You will notice an icon of a lock labeled, “Lock cell“.  Here the lock will look “pushed in” or highlighted.  That look indicates that the cell is now locked.  Click the “Lock cell” link and the cell will be unlocked.  Make the above choices again and you will see that the lock now looks like the other icons.

If you try to type in cell A5 now it will work just as it always has; you may type in it to your heart’s content.  To make the locking/protection feature active you now must protect the sheet.

Go back to the same menu item in Excel; Home tab and the Cells group click on Format and look down the list near the bottom this time for “Protect sheet“.  Once that item is clicked you will have a couple of options.  First, most everything in the locked cells will be halted other than recalculating values in those cells.  If you look down the list of unchecked items you will see features that you can allow to take place in the locked cells.  I would leave them set as is until you experiment some.

If you enter a password make sure you don’t forget it or you will have one heck of a time making any future changes to the spreadsheet.  You will be asked to repeat the password to make sure you did not make a typo.  If you are just trying this out skip the password and try protecting the sheet.

I feel I should mention a couple of things about passwords.  They can be a maximum of 255 characters including spaces and symbols.  They are case sensitive; meaning “dog”, “Dog” and “dOG” are different passwords.  This is also called “protection” not “security”.  If you have top-secret government information in your spreadsheet, do not depend on this procedure.  Excel passwords can be broken so this is just to keep the nosy person in line.

After protection, if you try to type in any cell that you did not “unlock” you will get a warning indicating that you don’t have permission for this.

To reverse the locking/protection turn around the procedures above.

Tags: cell, excel, lock, microsoft, protection, spreadsheet, unlock