Spreadsheets and Microsoft Excel in particular are great tools for any kind of numerical analysis, but they’re good for handling and storing other data as well. I seem to recall a survey a few years ago that Excel was the #1 database in the world with Access, Oracle and SQL Server lagging very far behind. Of course, it all depends on your definition of a database but the point is made.

Excel has useful features for developing forms and hiding information so that it’s easy to create mini apps which take user entered information, combine with data stored in the spreadsheet and provide an answer. Some of the spreadsheets are very sophisticated and Excel offers a “protect” feature that locks down a sheet (or workbooks) and prevents unwanted meddling or fiddling with the data. The protect feature even lets the owner set a password so that the more determined meddler can be thwarted and confidential data kept confidential.

Except it doesn’t. Any protected Excel spreadsheet can be unprotected in three steps. Here’s how.

With the Excel spreadsheet open,

1. Press Alt + F11 (or go to View Code in the Developer’s Tab)
2. In the window that appears, paste in this code (courtesy of University of Wisconsin-Green Bay)

   Sub PasswordBreaker()
       'Breaks worksheet password protection.
       Dim i As Integer, j As Integer, k As Integer
       Dim l As Integer, m As Integer, n As Integer
       Dim i1 As Integer, i2 As Integer, i3 As Integer
       Dim i4 As Integer, i5 As Integer, i6 As Integer
       On Error Resume Next
       For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
       For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
       For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
       For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
       ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
           Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
           Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
       If ActiveSheet.ProtectContents = False Then
           MsgBox "One usable password is " & Chr(i) & Chr(j) & _
               Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
               Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
            Exit Sub
       End If
       Next: Next: Next: Next: Next: Next
       Next: Next: Next: Next: Next: Next
   End Sub

3. Press F5 (or click Run) and wait a minute or so…..hey presto, spreadsheet unprotected.

On my modest PC it takes about 80 seconds to crack the password and it seems to come up with a password such as AABBAAABBB^ which isn’t the original password but nevertheless works. Spreadsheet is now unprotected. Try it for yourself.

Shocked? Surprised? Worried about a .xls that you sent last week with confidential data in it? I’m sure lots of people would be very worried if they knew how easy it was to unprotect a sheet.

To be fair to Microsoft, the help page says, “IMPORTANT  Worksheet and workbook element protection should not be confused with workbook-level password security. Element protection cannot protect a workbook from users who have malicious intent. For optimal security, you should help protect your whole workbook file by using a password.” Personally, I think setting a password sets unrealistic expectations about the level of protection; in some ways it would be better if there was no password option as there would be no expectation.

Overall, it’s best to think of protecting an Excel spreadsheet as a way of making the spreadsheet more convenient to use and don’t ever think of protecting an Excel spreadsheet as a way to hide secret information.

Did you like this article. Did it Save you Time… Throw us a bone support this sites Podcast Sponsor or share the codes on Twitter, Facebook , Google Plus