Excel Zoom

I’ve gotten a few requests for a way to help transition to Excel 2007 from earlier versions of Excel.  As anyone who first tries using Excel 2007 will notice, the traditional File, Edit, View, etc. menus are no longer available.  Instead there is a ribbon, which acts like the old menus.

To help Excel 2007 newbies make the transition, I’ve designed a quick reference mapping of the Excel 2003 menus to the Excel 2007 ribbon on a convenient mouse pad.  For example, if you previously went to Format | Cells to access the format cells menu, you would go to Home Tab | Cells Group | Format in Excel 2007.

If you think it would help, check it out at CafePress.com by clicking on the image below.

Excel 2007 Mousepad

Let me preface this article by saying that this will not help you recover lost data, or gain access to protected data that you otherwise wouldn’t have access to.  What it will do is allow you to unlock a password protected worksheet in Excel, so that you can edit it as necessary.  Simply put, if you don’t already have access to the worksheet, this macro will not help you.

Now that the disclaimer is out of the way, let’s figure out why people password protect worksheets to begin with.  By protecting a worksheet and the contents of locked cells, users can keep themselves or other users from:

• accidentally (or intentionally) 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 (i.e. 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.


Sub PasswordRecovery()
'
'MACROS BY EXCELZOOM.COM
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


Recently I found myself deciding if I should sell some stock in order to provide a little extra cash to my bank account.  I purchased shares of the company’s stock four times in various quantities, and at varying prices.  Due to the downturn in the stock market recently the prices dropped steadily, so I was able to purchase some of the shares at a low price.  The company has done a little better in recent months and has produced a gain for some of the shares I bought at the lower price.

The question I want to solve is: if I sell all the shares that have produced a gain, how many of the shares should I sell at a loss in order to offset the gains, and the resulting taxes?

To do this, I set up my spreadsheet like the one below:

Note: these are not the actual amounts I used in my own personal situation.  They are only provided for illustration purposes.

Download the example file here.

In cell G8 (labeled “Total Losses” in cell F8), I have the following formula: =(G10*C3)-(G10*E3), which I want to equal the total gains amount, of $1,936.10.  Right now it is showing a zero value because I haven’t determined how many shares need to be sold at a loss to equal the gains.

To do this, select cell G8, then click Tools | Goal Seek.  In the box that pops up, enter the following:

• Set cell: Keep as G8.  If it isn’t G8, enter G8 here.
• To value: Enter 1,936.10, as this is the value we wish to set the losses to.
• By changing cell: Enter cell G9, as you want to determine how many shares need to be sold in order to break even.
• Click OK

The result will tell you that you should sell 43.54 shares in order to break even.

For more information on Excel’s Goal Seek feature, take a look at the post Goal Seek to Solve a Problem.