By protecting a worksheet and the contents of locked cells, users can keep themselves or other users from:

  • accidentally removing formulas, or other contents of locked cells
  • adding or deleting rows and columns
  • changing cell, column or row formats
  • sorting data
  • using AutoFilter or PivotTable reports
  • editing objects or scenarios

Protecting a worksheet will not keep users from

  • editing any unlocked cells in the worksheet
  • viewing all data in the worksheet, regardless of if it is in a locked cell or not

Being able to unlock a password protected worksheet is useful, when

  • you have forgotten the password on your own worksheet
  • a co-worker, or other user has password protected a worksheet that you now need to edit, and they are not available to unlock the sheet for you
  • you have a need to perform an analysis of the data in a password protected worksheet, but are unable to do so due to the locked cells
  • you would like to sort/filter the data in a password protected worksheet, or create a PivotTable report from the data

Since Microsoft Excel is not a “secure” program, it is very easy to unlock the password of a password protected worksheet.  This is because there are several different combinations of passwords that Excel will accept to unlock the worksheet.  For example, a worksheet with the password “treehouse”, can also be unlocked with the password “AAAABAABBBB/”.  The opposite is also true, protecting with the password “AAAABAABBBB/” can be unlocked with the password “treehouse”).  Go ahead; try locking a worksheet with the password treehouse, and use AAAABAABBBB/ to unlock it.

The macro code below will “unlock” one worksheet at a time, using the method used above.  It won’t provide you with the actual password someone typed in, but rather a random sequence of letters or symbols that will work to unlock the worksheet.