How to create a random secure password in Excel

Managing and coming up with strong passwords can be challenging. Let us show you how you can create your own custom password generator in Excel. You can download our sample workbook below.

Syntax

Random Character Generation

=RANDBETWEEN(0,9) (Numbers)

=CHAR(RANDBETWEEN(65,90)) (Uppercase Letter)

=CHAR(RANDBETWEEN(97,122)) (Lowercase Letter)

=CHAR(RANDBETWEEN(33,47)) (Symbol)

Character Order

=RANDBETWEEN( 1, count of list items ) + ROW() / very large number that has more digits than the number of list items)

=RANK( generated random number, list of generated random numbers)

=INDEX( original list, rank of generated random number)

Combining Characters

=CONCATENATE(all random generated characters,…)

Steps

1. Type in =RANDBETWEEN(0,9) to generate a random number
2. Type in =CHAR(RANDBETWEEN(65,90)) to generate an uppercase letter
3. Type in =CHAR(RANDBETWEEN(97,122)) to generate a lowercase letter
4. Type in =CHAR(RANDBETWEEN(33,47)) to generate a special character
5. Copy all formulas to new cells to generate different characters for a 8-character length password (i.e. D3:D10)
6. Select the cell from the column that is right next to list (i.e. E3)
7. Type in =RANDBETWEEN(1,8)+ROW()/10000 to generate random unique numbers. Make sure the second argument of RANDBETWEEN is greater than or equal to the number of list items and the last number that divides the ROW function has more digits than the number of list items.
8. Copy down the formula for the rest of the rows (i.e. E3:E10)
9. Select the cell from the column that is right next to it (i.e. F3)
10. Type in =RANK(E3,$E$3:$E$10) to return a list of ranks. Make sure the first argument is relative and the second is absolute that.
11. Copy down the formula for the rest of the rows (i.e. F3:F10)
12. Select a new cell that is to be the first cell of the shuffled list (i.e. G3)
13. Type in =INDEX($D$3:$D$10,F3) to get the values from the original list in random order. Make sure the first argument is absolute and covers the original list.
14. Copy down the formula for the rest of the rows (i.e. G3:G10)
15. Type in or select each cell for the CONCATENATE function to combine everything and create password =CONCATENATE(G3,G4,G5,G6,G7,G8,G9,G10)

How

Generating Random Characters

You can generate random characters by combining the CHAR and RANDBETWEEN functions. The CHAR function returns a string character from an ANSI character code. Although you can give it numbers between 1 and 255 to get a character, not every character is suitable for passwords such as space or new line characters. Please refer to the tables below for more details.

Uppercase letters: A: 65, B: 66 … Y: 89, Z: 90

Lowercase letters: a: 97, b: 98 …y: 121, z: 122

Special Characters:

A secure password will contain numbers, in addition to letters and symbols. Although numbers have character codes and can be called with the CHAR function, the RANDBETWEEN function can already do this on its own. This formula returns a random generated number between the minimum and maximum limits. This means that we can generate code numbers to use them with the CHAR function. For example =CHAR(RANDBETWEEN(65,90)) returns an uppercase letter.

Shuffling Characters

The next step after generating the characters is to shuffle their order. If we combine them directly, all passwords would have the same pattern and be more predictable. To shuffle the list of generated characters, follow these steps:

1. Generate random unique numbers using the RANDBETWEEN and the ROW functions: =RANDBETWEEN(1,8)+ROW()/10000
2. Use the RANK function to get the ranks of each generated number =RANK(E3,$E$3:$E$10)
3. Finally sort the new values in a new order using the INDEX function =INDEX($D$3:$D$10,F3)

For more information about shuffling process, please see: How to shuffle a list

One formula to link them all

After the shuffling step, all we need to do is combining the characters in single cell. You can use the CONCATENATE function or ampersand character (&) to do this.

=CONCATENATE(G3,G4,G5,G6,G7,G8,G9,G10)

or

=G3&G4&G5&G6&G7&G8&G9&G10