You know the drill. Don’t use simple passwords like your birthday or your pet iguana’s name. Don’t use the same password everywhere. Instead, create a long, random, unique password for every website. Don’t try this without help—get a password manager. Once you have all your passwords safely stashed in the manager, replace any weak ones or dupes with unique passwords nobody could guess—and not even you could remember. A hacker might guess a simple-minded password like Fido or crack it by brute force, but nobody could guess something like P5$e?KqA+unh$RhPTlp1, and brute-forcing it would take impossibly long.
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 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. You're not likely to construct your own globe-spanning network of VPN servers, for example. You’re not going to cobble up a handmade antivirus utility. 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, albeit some you may not have seen before. 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.
One warning. You do need a current Excel version to make this project work. Office 2019 or later will do the job, as will the latest Office 365. If you're not sure, Microsoft explains how to get the latest version.
1. 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 as I describe below so the formulas will work. Once you have it working, you can tweak it to make it totally your own.
(Credit: Microsoft/PCMag)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 above.
In cell C3, enter 20 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:
(Credit: Microsoft/PCMag)2. Add the Formulas That Generate Passwords
With the scaffold in place, it's time to add the formulas. 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, Nay, or Meh. The string of available characters changes to match what you did.
3. Use Some Excel Magic
When I first created this spreadsheet, I used a simple-minded technique to create a random password. I started with a formula that returns one random character drawn from that string living in cell D8:
=MID(D8,RANDBETWEEN(1,LEN(D8)),1)
This tells Excel to generate a random number from one to the length of the string and return one character at that location. I glued together 40 repetitions of that formula to make a string of 40 random characters. And I used the LEFT() function to chop the result down to the desired length. It wasn’t pretty. The final formula weighed in at 1,329 characters!
Thanks to a little Excel magic I've learned since then, my current formula to generate a random password is much smaller, just 78 characters:
=TEXTJOIN("",TRUE,MID(D8,RANDBETWEEN(SIGN(ROW(INDIRECT("1:"&C3))),LEN(D8)),1))
If you just want to make use of this formula without worrying about how it works, no problem! Copy it into cell C10. You’ll see a random password, one that changes when you press F9 to recalculate, just like magic.
4. Learn Some Excel Magic
What’s that, you say? Do you want to know why that crazy formula works? I can help with that. But first, a confession. I don’t know if this is the best way to do what I did. I just tinkered until I made it work. There may be a more elegant solution.
First of all, please meet the TEXTJOIN() function. This little-known gem takes an array of values as input and combines them. It's a fairly recent addition, requiring at least Office 2019 or Microsoft 365. There are two other inputs before the array: a delimiter to separate the values and a true/false choice that tells it whether to ignore empty values. Try it. In a new sheet, type some names in column A, rows 1 to 5. Enter this formula in cell B1:
=TEXTJOIN("***", TRUE, A1:A5)
Cell B1 displays the names from A1 to A5 as a single string of text, with three asterisks between each. Here’s another example.
=TEXTJOIN(" and ",TRUE,ROW(A1:A9))
Before you copy this function into a worksheet, can you guess what it does? A1:A9 is an array, and the ROW() function returns an array of results, the numbers from 1 to 9. TEXTJOIN glues them together, with “ and “ between. You can use this technique to process lists of numbers in other ways.
(Credit: Microsoft/PCMag)We don’t need a list of numbers, though. We just need Excel to pull out a random character a given number of times. Again, this formula plucks out one random character:
=MID(D8,RANDBETWEEN(1,LEN(D8)),1)
To make Excel process that function, say, eight times, we need to replace the digit 1 that defines the low end of the random range with a formula that returns an array of eight 1s. The SIGN() function returns 1 for any positive number, so we'll use that to change the array of ascending numbers into an array of 1s. We then use TEXTJOIN() to combine the results. This formula returns an eight-character password made of random characters from the string found in D8:
=TEXTJOIN("",TRUE,MID(D8,RANDBETWEEN(SIGN(ROW(1:8)),LEN(D8)),1))
Almost there! What we need instead of the range 1:8 is a range from 1 to whatever value is in C3, the desired length. The handy INDIRECT() function does the job. It takes a text string that describes a cell or location and returns that cell or location. With that, the formula is complete. Here it is again:
=TEXTJOIN("",TRUE,MID(D8,RANDBETWEEN(SIGN(ROW(INDIRECT("1:"&C3))),LEN(D8)),1))
But wait, there's more we can do! Let's revisit the formula that glues together the chosen character lists. It was a little boring, repeating four near-identical IF statements. TEXTJOIN() can help here as well. Just replace the formula in D8 with this:
=TEXTJOIN("",TRUE,IF(C4:C7="Yes",D4:D7,""))
Here again the initial pair of quotes means no delimiter between the joined text elements and TRUE means ignore empty text. The IF statement checks the cells from C4 to C7 and, if the value is "Yes", returns the corresponding cell in D4 to D7.
5. Fine-Tuning Your 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, click to open Data Tools, and select Data Validation.
(Credit: Microsoft/PCMag)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, say, 64. When it looks like the screenshot here, click the next tab, Input Message. As the Input Message, type "Enter a length from 8 to 64". Copy that text to the clipboard and paste 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 a valid length, and if you make a mistake, you get an informative error message.
Ready for one final tweak? 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, uncheck Ignore blank, click in the Source box, and enter "Yes,No" without the quotes. On the Input Message tab, uncheck the Show input message box at the top. 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 drop-down list letting you choose one of those values.
At this point, you may want to get creative and add formatting to make your password generator look less industrial. Choose fonts you like, add color, and adjust it until your new tool looks great to you.
(Credit: Microsoft/PCMag)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 uncheck 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 fumbling fingers. Save the glorious result!
6. Make a Password Generator in Google Sheets
I'm an Excel whiz, and I have been since before Google Sheets existed. Maybe even since before Google existed! But I know 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 doable. I changed the prompt to say, "Press F5 to regenerate".
I did encounter one other difference when I copied the all-important function that actually creates the password. It didn’t work. It just returned a single random character. For Sheets to handle the array calculations involved, I had to explicitly pass the existing formula to the ArrayFormula() function. In Sheets, then, the formula that generates a random password becomes:
=ArrayFormula(TEXTJOIN("",TRUE,MID(D8,RANDBETWEEN(SIGN(ROW(INDIRECT("1:"&C3))),LEN(D8)),1)))
(Credit: Microsoft/PCMag)If you chose to employ the alternate TEXTJOIN() formula in cell D8, the one that glues together the selected character sets, you'll need to use ArrayFormula() around that one as well.
I won't go into detail here, but I managed to re-create 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 of this spreadsheet or went on to apply the fancy tweaks, you now have a password generator you wrote yourself. True, Excel does use a pseudo-random number generator, but you can add your own randomness by rolling a die and tapping F9 that many times 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!
For more password tips, read 3 Simple Tricks for Remembering Strong Passwords.