Unlocking a Password Protected Excel Sheet

It happens every now and then: 

Someone has a really neat (or broken) formula in an Excel document they share with you, but because the sheet has been locked you cannot see the code.

After a bit of digging on the web, this seems like the best solution.

Press Alt+F11

Then enter:

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

Run the code and the sheet will be unlocked!

Excel Voodoo: Adding Every Nth Row/Column Together [With Breakdown]

Ever wanted to add every Nth column together in Excel?

=SUMPRODUCT($D$10:$D$249*(MOD(ROW($D$10:$D$249),3)=1))

Easy! but what is going on here? I'll post the technical explanation below, but all you need to know is the last two numbers are the key to the whole thing!

Changing the 3

Fill in the blank: "I want to add every ____th cell together." Whatever is in that blank, that should be the number you use instead of 3.

Changing the 1

The 3 divides the cells into blocks of 3, but then you have to tell Excel which number to look at in each block. There is a lot of technical work behind this (see below) but in the interest of keeping life simple, most small formulas can be guess-and-checked.

The number replacing 1 will always be less than the number replacing 3. Start with 0 and look at the result. Is that number close to what you think the result should be?

The alternative (more reliable) method is to take the first cell the formula is searching for (in this case D10) and divide that number (10) by the number replacing 3. Now time for some long division! 3x3=9, so when dividing by 10 there is a remainder of 1. If you wanted the formula to start on D10 the last number in the formula should be 1. Starting on D11 should be 2, and D12 should be 0 (Because there is not a remainder!). Sounds crazy right? That's because this is pretty much Excel Voodoo as far as the average Joe is concerned.

“the row function of coarse returns the row # of a given cell reference.
D10, row = 10...
Simple enough.

By putting it in sumproduct ROW(D10:D249) creates an array of row #s
{10,11,12,13...etc...,249}

Mod returns the remainder after a devision.
Examples,
MOD(10,3) = 1 because..
3 can be devided into 10 3 times
3X3 = 9
10-9 = 1

MOD(11,3) = 2
3 can be devided into 11 3 times
3X3 = 9
11-9 = 2

MOD(12,3) = 0
3 can be devided into 12 4 times
3X4 = 12
12-12 = 0

so MOD({10,11,12,13...etc...,249},3) becomes an array of the results of each mod.
{1,2,0,1,2,0,1,2,0...etc}

So now you have
=SUMPRODUCT($D$10:$D$249*({1,2,0,1,2,0,1,2,0...etc}=1))

Then it tests if each result of the mod = 1, and returns TRUE or False for each
=SUMPRODUCT($D$10:$D$249*({True,false,false,true,false,false,etc..}))

Then multiplying that array of true/false results by the numbers in D10:D249 coerces the true/false to 1/0 (True = 1, False = 0)
=SUMPRODUCT($D$10:$D$249*({1,0,0,1,0,0,1,0,0,etc}))

Then finally the sumproduct does what it does, and sums the result of the array of multiplications between each value in D10:D249 and the corresponding 1 or 0 in the array.”

— http://www.mrexcel.com/forum/excel-questions/621629-sum-every-nth-row.html

The Legal Survival Guide

A few months back I had 2 great ideas:

First was for a "Better Call Saul" remake using my boss to create "Better Call Paul"

Next was the "Legal Survival Guide" where we take popular videos of people having a quick brush with law enforcement and explain what the law behind the encounter is. 

Episode 1 exploded, getting hundreds of views every day. I was amazed and so was everyone else. Our stagnant 26 subscribers increased by 3-5 or more daily!

Episode 2 never got the traction I was hoping for, but did come with a snazzy new call to action at the end for the legal protection plan. A little thing I whipped up in Adobe After Effects.

The Moving Infographic

Not a bad first try!

While surfing the web for inspiration I discovered that most moving infographics have a definitive starting and ending point. On most of the graphics out there you can clearly see a frame where the entire thing "resets" to the starting position and plays again.

One of my main goals with this graphic was to break that mold and get something that had continuous flow to it. The timeline feature in Photoshop had the flexibility I needed. First I did sync everything to the same frames, then when I liked the animation of each row I staggered them out.

Check out the final result below. If you like it go ahead and pin it on Pinterest here.

The effect of running the man over was achieved with the "puppet warp" tool in Photoshop. I thought it would be funny to have him lay 2-D against the ground on the final frame. A bit of Paper Mario-style humor.

Right after I finished the graphic, Justin Beiber got cited for a real speed contest and swearing at the police in Florida. I quickly adapted the graphic to match the news story.