Install a password manager and load it with all your passwords is a good and noble start, but the job does not end there. You are not completely protected until you replace all weak and reusable passwords with a unique password that no customer could guess, and you could not even remember. A brutally powerful password cracking attack may designate a password like Rover, but it will fail when the password is something like 1ywN @ WKglvp $ qC4N.
The question is where do you get these random passwords? Only each password manager comes with its own random password generator, some of which are better than others. In most cases, however, the program uses what is called a pseudo-random algorithm. In theory, an attacker who knows the algorithm and has access to one of your generated passwords, replicates all subsequent generated passwords (although it would be quite difficult). If you are paranoid enough, you may want to create your own random password generator. We can help you build it using Microsoft Excel.
Certain security information is unthinkable beyond what makes it self-true, true. You will not construct your own virtual private network, or VPN, for example. But the construction of this small project requires no advanced knowledge, only the ability to follow the instructions. It does not use macros or good things, just common Excel features. Note that this project is necessarily based on Excel's pseudo-random algorithm. The difference here is that the bad guys can study the password generator in any publicly available password manager, while they do not have access to your home built.
Create Password Generator Position
First let's create the construction site that frames our password generator, labels, and static components. Please put things in exactly the cells I describe so the formulas will work. Once you have it, you can tweak it to make it all your own.
In cell B1, enter "Random Password Generator" or whatever title you want for your project. Start in cell B3 and go down by typing the labels "Length", "Large Letter", "Easy Letter", "Numbers" and "Special Characters". Skip cell B8, type "Press F9 to Regenerate" in B9 and "PASSWORD" in B10. It should look like the picture below.
In cell C3, enter 16 or desired default password length. Type "Yes" in the following four cells below it. Enter the full letter alphabet in cell D4 and the entire letter in D5. Enter the 10 digits in D6 and the special characters you want to use in D7. Pro tip: Put 0 at the latest, not first, in the list of numbers, or Excel will eliminate it. The completed status should look like this:
Add form to generate password
To begin with, we must build a text string containing all the characters we have chosen to use. The formula to make it look a little complicated, but it's only a long time. Enter this formula in cell D8:
= IF (C4 = "Yes", D4, "") and IF (C5 = "Yes", D5, "") and IF (C6 = "Yes", D6, " ") & IF (C7 =" Yes ", D7," ")
& the operator glues 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 something other than Yes (whether large or small), do not include it. Try it now; change some of the Yes cells to No or No or Frog. The string of available characters changes to match what you did.
Next, the formula will create a random password. In cell C10 begins with this preliminary version:
= MID (D8, RANDBETWEEN (1, LEN (D8)), 1)
I will break it from the bottom from the inside. The LEN function returns the length of the value you pass it, in this case the length of the string of available characters. RANDBETWEEN does not surprisingly return a random number between the two digits you give it, in this case one and the length. And the MID function returns a bit of the input string beginning with the first number you send it and continues for the number of characters you enter, in this case, only one. Here's how this formula returns a random sign from the available set of characters. By pressing F9, Excel tells you to recalculate all functions; try it a couple of times and look at the random change in character.
It is of course only one character, not an entire password. The next step is a bit tough, but not really difficult. Click on the function bar to edit the last entry, add a & character to the end, mark everything except the character and press Ctrl + C to copy it to the clipboard. Let's say we want a maximum password length of 40 characters. Then press Ctrl + V 39 times. Remove the last ampersand, press Enter, and you're done.
Well you are ] almost made. As written, this password generator always creates 40 character passwords. We must trim down production to the specified length. Save your work right now, to edit the milestone formula; you would not want to remove it from an accident!
Select the formula that generates the password and click on the form field immediately after the same character. Pro Tips: By pressing Ctrl + Alt + U at this point, the form field is enlarged. Write LEFT followed by an open parenthesis. Scroll to the end of the formula and type a comma, C3 and a close parenthesis. Bravo! The formula now trims the password to the length you chose.
Refine the password generator
The password generator is completely functional at this point. If you're happy with it, it's fine: You've done it! But if you're interested, you can improve its look and functionality in several ways. To begin with, right-click D at the top of column D and choose Hide from the menu. Now you do not need to see the signature lists and the metrics.
Usually you want to set the upper and lower limits for the length of a password generator. Additionally, if you enter something other than a number in the field length, the formula fails. We can fix that. Click cell C3, which defines the length, click Data in the band and select Data validation. If you do not see the Data Validation label, extend the spreadsheet wider.
In the resulting popup, click the drop-down menu under Allow and select Entire Number. Deselect the Ignore check box, and set Minimum to 8 and Max to 40. When it looks like the screen here, click the next tab, Input message. Enter "Enter a length from 8 to 40" as Input message. Copy the text to the clipboard and pass it to the Error Message field in the Error Message tab, and then click OK. Now that you click the length cell, you will be prompted to enter the correct length, and if you make a mistake, you will receive an informative error message.
Ready for a last tweak? Type "Yes" in cell E1 and "No" just below it. Click in cell C4, the cell just to the right of the Large Letter label. Click Data again in the band and select Data validation. Choose List from the drop-down menu, deselect Ignore Empty, click in the source field, and select the cells E1 and E2. On the Input message tab, enter "Yes or No" as a message. On the Error message page, enter "Yes or No" as an error message. Click OK to complete. Copy this cell to the three cells below it.
That's it! Now the four cells only accept yes or no as the values. Better still, each now has a dropdown list that lets you choose one of these values. Oh, now that you're done right click on the big E at the top of column E and choose Hide so you do not see the cells that enter the data validation system.
At this time, you may want to be creative and add formatting so your password generator looks less industrial. Choose fonts that you like, add color, adjust things until it looks good.
Let's unlock it so you do not accidentally turn a formula by entering data into the wrong cell. Select cells C3 to C7 (it is the length cell plus the four yes / no cells), right-click and select Format cells. Click the Protection tab and deselect the Locked check box, and then click OK. Click Review in the tape and click Protect Sheet. Just click OK to accept the settings in the resulting dialog box; You are not trying to solve protect the sheet, just to protect it from fumble fingers. Save the glorious result!
Create a Google Sheets Password Generator
I'm an Excel whiz and have existed since Google Sheets existed. Maybe even before Google was! But I know that many people swear on Google Sheets, so I fired it to make sure it supports this project.
I followed my instructions to build the password generator in Ark, and found that everything worked jim-dandy, right up to the formula that shows a random sign. Everything worked, but pressing F9 failed to update with a new random character. Consulting Google, I found that to force an update, you must press F5, thereby updating the entire page, or changing the value of a cell. It's difficult, but possible. I changed the prompt to say "Press F5 to regenerate".
Instead of recreating the giant formula that performs full password generation, I copied it from my Excel spreadsheet. Hallelujah! It worked well! I will not go into detail here, but I managed to recreate data validation rules and hide the unwanted columns as well. If you use Sheet rather than Excel, this project may still work for you.
You did it!
You did it! ] Whether you accepted the bare bone version or continued to apply the nice tweaks, you now have a password generator that you wrote yourself. Certainly, Excel uses a pseudo-random number generator, but you can add your own randomness by pressing F9 several times before accepting the generated password. And while an attacker can work to convert the password generator into a password management product used by millions, your one-time tool is not just on the radar. You did it!