How to Quickly Generate Random Passwords in Excel

This guide will explain how you can use Excel functions to quickly generate random passwords in Excel.

The formula will use the CHAR and RANDBETWEEN functions to create a string of randomly chosen letters, numbers, and special characters.

Because of the hacking risks, it is recommended to use passwords that have a combination of uppercase and lowercase letters, numbers, and special characters. For example, the password ‘dQw4w9WgXcQ!’ is more secure than ‘qwerty123’. 

Let’s take a look at a use case where you might have to generate several secure passwords at once.

Suppose you have a folder of .zip files that you would like to send out via email. You plan on giving each of these files a randomly generated password before sending.

We can use Excel to quickly generate a list of random passwords of a specified length. For example, we could indicate that we want the first 5 characters to be letters, the next two to be numeric, and the last character being either a ‘!’ or a ‘?’.

The trick to create the randomized string is to build it one character at a time. The CHAR function and RANDBETWEEN function will help us randomly pick letters, numbers, and symbols. We can similarly use the same technique to create random PIN codes of arbitrary length.

Since we now know when it’s useful to create random passwords in Excel, let’s take a look at a sample spreadsheet that uses a formula to generate them.

A Real Example of Quickly Generate Random Password in Excel

Let’s take a look at a real example of a spreadsheet that can quickly generate a secure password in Microsoft Excel

In the example below, we have three columns with different types of generated passwords. The first column creates a five character long string composed of only letters. The second column generates alphanumeric passwords. The last column introduces special characters such as ‘+’ and ‘*’ to add even more security.

To get the values in Column A, we just had to use the following formula:

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(65,90))

The CHAR function accepts an integer value as an argument and outputs the corresponding Unicode character. For example, the uppercase letters from ‘A’ to ‘Z’ are in the range 65 to 90. This means that =CHAR(65) will return an ‘A’. 

We use the RANDBETWEEN function to choose a random integer in a specified range. For example, the formula =CHAR(RANDBETWEEN(97, 122) can return any lowercase letter. 

To create our final password, we simply concatenate multiple instances of the CHAR function. One limitation to this method is that the string will always be a fixed length.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try setting up your own password generator in Excel, read the next section to follow our step-by-step guide.

How to Quickly Generate Random Password in Excel

This section will guide you through each step needed to create a random password generator in a spreadsheet. You’ll learn how we can use the CHAR and RANDBETWEEN functions to create a string composed of randomly selected characters automatically.

1. First, let’s select the cell that will hold our generated password. In this example, we’ll be outputting our password in cell B1.
   
2. Next, we simply type the equal sign ‘=‘ to begin the function, followed by ‘CHAR(‘.
   
3. Since the CHAR function should have a random integer as an argument, we’ll have to use the RANDBETWEEN function. For this example, we want to first output a random uppercase letter. To pick uppercase characters, we’ll use the range 65 to 90.
   
4. Next, we’ll repeat the same process and concatenate both results using the ampersand symbol ‘&’.
   
5. We can use different ranges to create a mix of lowercase letters, uppercase letters and numbers.
   
   

Frequently Asked Questions (FAQ)

1. How do I know what numbers to use for a particular character?
   Excel has a built-in function called UNICODE that can return the number that corresponds to a specified character. For example, the formula =UNICODE(“!”) returns the value 33. If we use the formula =CHAR(33), we will output an exclamation point.
   
2. Why does my formula return a value error?
   If your formula returns a #VALUE! error, you may have an invalid number as an argument to the CHAR function. The CHAR function accepts integers between 1 and 255.

This article should be all you need to know to start generating your own random passwords in Excel. This step-by-step guide shows how easy it is to concatenate randomly selected characters to create a fully random string. 

The CHAR and RANDBETWEEN functions are just one example of a clever way to use two functions together to perform a useful task. With so many other Excel functions out there, you can surely find a few that suit your use case. 

Are you interested in learning more about what Excel can do for you? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us. 

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe