By David M.
How To Generate Random Passwords
We cover how to create a random password generator in Excel. We will start by entering our password inputs, in other words the characters we want our password to include, we will then perform intermediary calculations to retrieve the random characters adjusted for their text case and finally, we will use the CONCATENATE function to bring these characters together and return our password.
Our password will consist of 45 inputs including letters, numbers and symbols. To start let’s input all the letters in the alphabet. Let’s now add the numbers 1 to 9. Finally, we can add some symbols to make our password even stronger.
Next, we move onto our intermediary calculations. Firstly, we want to obtain a random number from 1 to 45 which we will use later to match with the applicable characters. To do this, we can use the RANDBETWEEN function. To prevent these random numbers from updating as we edit other cells, we can copy them and paste only their values using Paste Special.
We also want to randomly assign certain letters a lower case and others an upper case. To do this, we can use the RAND function. In other words if a random number between 0 and 1 is greater than 0.5, then we want it to return “lower”, if not then return “upper”. We can once again paste only the values to prevent these numbers from updating.
Next, we will use the VLOOKUP function to find which characters match the random numbers. We first input the value we are looking up, which is the random number, we then select the inputs table, adding absolute references to ensure this table remains static as we bring it across, we’re looking to retrieve the value in the 2nd column so we type 2, and finally we can click false.
Finally, we will adjust these characters based on whether they are upper or lower case. In other words, if the text case is upper then we will apply the UPPER function, if not then we will apply the LOWER function to retrieve the lower case.
Now, that we have all the characters adjusted for the text case, we can enter the password sheet and use the CONCATENATE function to bring the characters together and return the random password as a whole. To do this quickly, press down the CTRL key while selecting all of the characters.
