Software

Lock IT Down: Generate random passwords in Excel

See how you can use a downloadable Excel spreadsheet for creating random passwords.

For IT pros, security is an ever-present concern in the enterprise. For others, including young and inexperienced users, security is often an afterthought.

For example, a network specialist I know recently mentioned problems he has experienced with users who choose their own passwords. He mentioned users who select their favorite sports team, a password that can be easily guessed. The alternative, assigning unique passwords for hundreds of users, was a daunting task.

Fortunately, Microsoft Excel offers a solution. By combining a series of worksheet functions, you can generate a random, eight-character password with one keystroke. Even better, the password generator lets you combine uppercase and lowercase letters with numbers to make the password even more difficult to guess.

Download the password generator
Click here to download a copy of the password generator spreadsheet. After opening the spreadsheet, simply press the [F9] key to generate a random password that can be assigned to users.

As you can see, Excel makes it easy to generate random passwords on the fly. Of course, convincing your users to adopt them instead of something more easily cracked is your next task. And beware, some users may want to write down their random password and keep it near their PC, a strategy that defeats the purpose.

How the password generator works
The following information about how I designed the spreadsheet should be helpful if you want to alter the design to generate passwords that are even more “random.” Here is the method I used to combine several Excel functions (see Figure A) to build the password generator.

Figure A

Function Purpose
RAND() Generate a random number within a given range
IF() Test the generated number to determine the character type
CHAR() Returns the character with the ASCII number provided in the argument
CONCATENATE() Combines the characters in eight cells into an eight-character password

 The heart of the random password generator is the RAND() function. By itself, the function returns a decimal value between 0 and 1. However, if you multiply this number by a particular value, you can generate a number within a specified range. For this spreadsheet, I wanted an equal chance of generating an uppercase letter, a lowercase letter, and a number.

To begin, I want to generate a number between one and 15 using this code:
=RAND()*(15-1)+1

Next, I used nested IF functions to provide three possibilities: If the number is between one and five, we’ll refer to cell I100, where we’ll generate the value for a numeric character. If it’s greater than five, we’ll use another IF function to see if we’ll use an uppercase (6-10) letter by referring to the value in cell G100 or a lowercase (11-15) letter by using the value in cell H100. With the RAND statement in cell F100, the function in cell E100 looks like this:
=IF(F100>5,IF(F100>10,G100,H100),I100)

Next up are a trio of RAND() functions to generate the ASCII values of numbers and letters. By consulting an ASCII table (click here to download an ACSII table from CNET's Download.com), we know that the codes for numbers are between 48 and 57, lowercase letters are between 97 and 122, and uppercase letters are between 65 and 90. The functions to generate these number ranges are, respectively:
=RAND()*(57-48)+48
=RAND()*(122-97)+97
=RAND()*(90-65)+65

Now that our IF statement in cell E100 returns a number from one of these three ranges, it’s time to use that value to generate an ASCII character. In cell A100, we place a CHAR() function with the cell address of our IF statement in the parentheses:
=CHAR(E100)

Cell A100 will now contain a number from zero to nine, a lowercase letter, or an uppercase letter. If you simply fill the IF, CHAR*(), and RAND() statements down seven rows, you’ll get a matrix of cells in the range A100:A107 that will form your password.

Now all we need is a CONCATENATE() function in cell A110 to assemble the password from those eight cells. It looks like this:
=CONCATENATE(A100,A101,A102,A103,A104,A105,A106,A107,A108,A109)

Putting it all together
Here’s how to combine the functions into a spreadsheet. Place the RAND() functions into cells F100:F107 to obscure the spreadsheet’s workings. A message to the user at the top of the screen and a simple cell reference (=A110) provides the eight-character password. Figure B shows the final spreadsheet. (We’ve hidden the intervening blank rows so you can see all the active cells.)

To use the spreadsheet to generate random passwords, all you have to do is press F9, which causes Excel to recalculate the random numbers. If you wanted to weigh the possibility of uppercase or lowercase letters or numbers more heavily, you can adjust the values in the nested IF functions or alter the values to eliminate one type of character altogether.

Figure B
Press [F9] to generate an eight-character password.


Download this handy spreadsheet
Click here to download a copy of Gregory Harris' password generation spreadsheet. You will need a file unzip utility such as PKZIP or WinZip to extract the download file. You will also need Microsoft Excel 2000 to view the spreadsheet.

 
3 comments
randaza1
randaza1

can you please send me the file for the excel password generator. tony (shift+2) kbaam com Thanks Tony

larryj
larryj

Aug 15 2001... That is quite a while. Yes I still have this file and I am still sending it out as needed. E-Mail me as listed above. Thanks and have a good day!

larryj
larryj

Even after all these years YES I still have it and send it as needed. I still have the file in case anyone is still looking for this. E-mail me at larryj(shift-2)csmdedu Subject: "excel password generator" Thanks and you are welcome!!

Editor's Picks