Regular readers of PCMag know that we strongly advise using a complex and unique password for every website, and that the only way to do this is with the help of a password manager. Loading that tool with all your passwords is a good start, but the job doesn’t stop there. You’re not fully protected until you replace every weak and reused password with a unique password that nobody could guess, and that not even you could remember. A brute-force password cracking attack might well hit on a password like Rover, but it will fail when the password is something like ApiDiS57IhY1M#t%qIEg.
Where Do You Get Your Passwords?
The question is where do you get those random passwords? Just about every password manager comes with its own random password generator, some of which are better than others. In most cases, though, the program uses what’s called a pseudo-random algorithm. In theory, a hacker who knows the algorithm and has access to one of your generated passwords could replicate all subsequent generated passwords (though it would be quite difficult). If you’re paranoid enough, you might want to create your own random password generator. We can help you build it using Microsoft Excel.
Some security tasks are undeniably beyond the do-it-yourself realm, true. You’re not going to construct your own virtual private network, or VPN, for example. But building this little project doesn’t require advanced knowledge, just the ability to follow instructions. It doesn’t use macros or fancy stuff, just ordinary Excel functions. Note that this project necessarily relies on Excel’s pseudo-random algorithm. The difference here is that the bad guys can study the password generator embedded in any publicly available password manager, while they have no access to your home-built one.
Create the Password Generator Scaffold
First, let’s create the scaffold that will frame our password generator, meaning the labels and static components. Please put things in exactly the cells I describe, so the formulas will work. Once you have it working, you can tweak it to make it totally your own.
In cell B1, enter “Random Password Generator”, or whatever title you wish for your project. Starting in cell B3 and going down, enter the labels “Length”, “Uppercase letters”, “Lowercase letters”, “Digits”, and “Special characters”. Skip cell B8, enter “Press F9 to Regenerate” in B9, and “PASSWORD” in B10. It should look like the image below.
In cell C3, enter 16, or your preferred default password length. Enter “Yes” in the next four cells below it. Now enter the whole uppercase alphabet in cell D4, and the whole lowercase alphabet in D5. Enter the 10 digits in D6 and whatever special characters you want to use in D7. Pro tip: Put the 0 last, not first, in the list of digits, or Excel will eliminate it. The completed scaffold should look something like this:
Add the Formulas That Generate Passwords
To start, we need to build a string of text that includes all the characters we’ve chosen to use. The formula to do that looks a little complicated, but really, it’s just long. Enter this formula in cell D8:
=IF(C4=”Yes”,D4,””) &IF(C5=”Yes”,D5,””) &IF(C6=”Yes”,D6,””) &IF(C7=”Yes”,D7,””)
The & operator glues together strings of text. What this formula says is, for each of the four character sets, if the adjacent cell contains Yes, include the character set. But if that cell contains anything but Yes (regardless of upper or lower case), don’t include it. Try it now; change some of the Yes cells to No, or Nay, or Frog. The string of available characters changes to match what you did.
Next comes the formula to generate a random password. In cell C10, start with this preliminary version:
=MID(D8,RANDBETWEEN(1,LEN(D8)),1)
I’ll break that one down from the inside out. The LEN function returns the length of whatever value you pass it, in this case the length of the string of available characters. Calling RANDBETWEEN not surprisingly returns a random number between the two numbers you give it, in this case one and the length. And the MID function returns a chunk of the input string starting at the first number you pass it and going on for the number of characters you specify, in this case just one. So, this formula returns one random character from the available set of characters. Pressing F9 tells Excel to recalculate all functions; try it a few times and watch the random character change.
Of course, that’s just one character, not a whole password. The next step is a bit toilsome, but not actually difficult. Click in the function bar to edit that last entry, append a & character to the end, highlight all of it except the equal sign, and press Ctrl+C to copy it to the clipboard. Let’s say we want a maximum password length of 40 characters. So, press Ctrl+V 39 times. Delete the final ampersand, press Enter, and you’re done.
Well, you’re almost done. As written, this password generator always creates 40-character passwords. We need to trim down its output to the specified length. Save your work at this point, because you’ll be editing that mile-long formula; you wouldn’t want to delete it by accident!
Select the formula that generates the password and click in the formula bar just after the equals sign. Pro tip: Pressing Ctrl+Alt+U at this point enlarges the formula bar. Type LEFT followed by an open parenthesis. Scroll to the very end of the formula and type a comma, C3, and a close parenthesis. Bravo! The formula now trims the password to the length you chose.
Fine-Tuning the Password Generator
The password generator is totally functional at this point. If you’re happy with it as is, great: You’ve done it! But if you’re interested, you can improve its appearance and functionality in several ways. For starters, right-click the D at the top of column D and choose Hide from the menu. Now you don’t have to see the character set lists and in-between calculations.
Typically, you want to set upper and lower limits for length in a password generator. In addition, if you enter anything but a number in the Length field the formula fails. We can fix that. Click cell C3, which defines the length, click Data in the ribbon, and select Data Validation. If you don’t see the Data Validation label, stretch your spreadsheet wider.
In the resulting popup, click the pulldown under Allow and choose Whole number. Uncheck the Ignore blank box, and set the Minimum to 8 and the Maximum to 40. When it looks like the screenshot here, click the next tab, Input Message. As the Input Message, type “Enter a length from 8 to 40”. Copy that text to the clipboard and past it into the Error message field of the Error Alert tab, then click OK. Now when you click the Length cell you get a prompt to enter the correct length, and if you make a mistake, you get an informative error message.
Ready for one final tweak? Enter “Yes” in cell E1 and “No” just below it. Click in cell C4, the cell just to the right of the label Uppercase letters. Once again click Data in the ribbon and select Data Validation. Choose List from the drop-down, un-check Ignore blank, click in the Source box, and highlight cells E1 and E2. On the Input Message tab, enter “Yes or No” as the message. On the Error Alert page, enter “Yes or No” as the error message. Click OK to finish. Copy this cell to the three cells below it.
That’s it! Now those four cells only accept Yes or No as values. Better still, each has now acquired a dropdown list letting you choose one of those values. Oh, now that you’ve finished, right click the big E at the top of column E and choose Hide, so you don’t see the cells that feed into the data validation system.
At this point you may want to get creative and add formatting to make your password generator look less industrial. Choose fonts that you like, add color, adjust things until it looks great to you.
Finally, let’s lock it down, so you don’t accidentally destroy a formula by entering data in the wrong cell. Highlight cells C3 to C7 (that’s the length cell plus the four yes/no cells), right-click, and choose Format Cells. Click the Protection tab and un-check the checkbox called Locked, then click OK. Click Review in the ribbon and click Protect Sheet. Just click OK to accept the settings in the resulting dialog; you’re not trying to password protect the sheet, merely to protect it against fumblefingers. Save the glorious result!
Make a Password Generator in Google Sheets
I’m an Excel whiz, and have been since before Google Sheets existed. Maybe even since before Google existed! But I know that many folks swear by Google Sheets, so I fired it up to make sure it supports this project.
I followed my own instructions to build the password generator in Sheets, and found everything worked jim-dandy, right up to the formula that displays one random character. Everything worked, but pressing F9 failed to refresh with a new random character. Consulting Google, I found that to force a refresh you must press F5, thereby updating the whole page, or change the value of any cell. It’s awkward but do-able. I changed the prompt to say “Press F5 to regenerate”.
Rather than recreate the gigantic formula that performs full password generation, I copied it from my Excel worksheet. Hallelujah! It worked! I won’t go into detail here, but I managed to recreate the data validation rules and hide the unwanted columns too. If you’re using Sheets rather than Excel, this project can still work for you.
You Did It!
Whether you accepted the bare-bones version or went on to apply the fancy tweaks, you now have a password generator that you wrote yourself. True, Excel does use a pseudo-random number generator, but you can add your own randomness by tapping F9 repeatedly before accepting the generated password. And while a hacker might work to reverse-engineer the password generator in a password management product used by millions, your one-off utility just isn’t on the radar. You did it!
