Installing a password manager and loading all your passwords is a good and classy start, but the job does not end there. You are not fully protected until you replace each weak and reused password with a unique password that no one can guess and can not even remember. An attack with brute force passwords may say something like a password like Rover, but it will fail if the password is something like 1ywN @ WKglvp $ qC4N.
The question is, where do you get the random ones? Passwords Almost every password manager has its own random number generator, some of which are better than others. In most cases, however, the program uses a so-called pseudo-random algorithm. Theoretically, a hacker who knows the algorithm and has access to one of your generated passwords could replicate any subsequent generated passwords (but that would be rather difficult). If you are paranoid enough, you might want to create your own random generator. We can help you create it with Microsoft Excel.
Some security duties are undeniably beyond the do-it-yourself area. For example, you will not be building your own virtual private network or VPN. To build this small project, however, requires no advanced knowledge but only the ability to follow instructions. It does not use macros or fancy things, just ordinary Excel functions. Note that this project relies on Excel's pseudorandom algorithm. The difference here is that the bad guys can study the password generator in any publicly accessible password manager while they do not have access to your home-made password.
Creating the Password Generator Framework
First, we create the framework that will be created using our password generator, which frames labels and static components. Please put things exactly in the cells I have described for the formulas to work. If you work it, you can customize it to make it your own.
In cell B1
In cell C3, enter 16 or your preferred default password length. Enter "Yes" in the next four cells below. Now enter the entire capital letter in cell D4 and the entire lowercase letter in D5. Enter the 10 digits in D6 and the special characters you want to use in D7. Pro Tip: Put the 0 as last and not first in the list of digits. Otherwise it will be deleted by Excel. The finished framework should look something like this:
Add the formulas that generate passwords
To start We'll create a text string that contains all the characters you've selected. The formula for it looks a bit complicated, but it is really long. Enter this formula in cell D8:
= IF (C4 = "Yes", D4, "") & IF (C5 = "Yes", D5, "") & IF (C6 = "Yes", D6 , "") & IF (C7 = "Yes", D7, "")
The & operator merges text strings. This formula states that the character set is included for each of the four character sets if the adjacent cell contains Yes. However, if this cell contains anything other than yes (case insensitive), do not include it. Try it now; Change some of the Yes cells to No, No or Frog. The string of available characters adapts to what you did.
Next, the formula for generating a random password appears. Begin in cell C10 with this preliminary version:
= MID (D8, RANDBETWEEN (1, LEN (D8)), 1)
I bring these from the inside out. The LEN function returns the length of the passed value, in this case the length of the string of available characters. Not unexpectedly, when you call RANDBETWEEN, a random number between the two numbers you specify is returned, in this case one and the length. The MID function returns a piece of the input string, starting with the first number you pass, and continues for the specified number of characters, in this case only one character. So this formula returns a random character from the available character set. Pressing F9 instructs Excel to recalculate all functions. Try it a few times and watch the random change of character.
Of course, that's just a sign, not an entire password. The next step is a bit tedious, but not really difficult. Click on the toolbar to edit the last entry, append & to the end, select everything except the equals sign, and press Ctrl + C to copy it to the clipboard. Suppose we want a maximum password length of 40 characters. So press Ctrl + V 39 times. Delete the last ampersand, press Enter, and you're done.
Well, you're almost done . As written, this password generator always generates 40-digit passwords. We have to reduce the output to the specified length. Save your work at this point as you edit this miles-long formula. You do not want to delete it by mistake!
Select the formula that generates the password and click the formula bar immediately after the equal sign. Pro Tip: Pressing Ctrl + Alt + U at this point enlarges the formula bar. Enter LEFT followed by an open parenthesis. Scroll to the end of the formula and enter a comma, C3 and a closing parenthesis. Bravo! The formula now truncates the password to the length you have selected.
Fine tune password generator
The password generator is fully functional at this point. If you're happy with that, it's great: you've made it! If you are interested, you can improve the look and functionality in a number of ways. Right-click the D at the top of column D and choose Hide from the menu. Now you do not have to see the font lists and intermediate calculations anymore.
Typically, you want to set upper and lower bounds for length in a password generator. If you enter only one number in the Length field, the formula will fail. We can fix that. Click cell C3 that defines the length, click Data on the ribbon and select Data validation. If you do not see the Data Validation label, continue to stretch your spreadsheet.
In the pop-up that appears, click the pull-down menu under Allow and select Whole number. Uncheck the Ignore Blanks check box and set the minimum to 8 and the maximum to 40. If the screenshot looks like this, click the next tab, Input Message. Enter "Enter a length of 8 to 40" as the input message. Copy this text to the clipboard and paste it into the error message field on the Error Warning tab. Then click OK. If you now click on the cell length, a prompt will be displayed to enter the correct length, and if you make a mistake, you will receive an informative error message.
Ready for a last change? Enter "Yes" in cell E1 and "No" just below it. In cell C4, click the cell to the right of the uppercase caption. Click Data again on the Ribbon and select Data Review. From the drop-down menu, select List, uncheck the Ignore blanks check box, click in the Source box and highlight cells E1 and E2. On the Input Message tab, enter "Yes or No" as the message. On the Error Warning page, enter "Yes or No" as the error message. Click OK to finish. Copy this cell into the three cells below.
That was & # 39; s! Now these four cells accept only yes or no as values. Better yet, everyone has now purchased a drop-down list where you can select one of these values. 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 entering the data validation system.
At this point, you may want to get creative and add formatting to make your password generator less industrial. Choose fonts that you like, add colors, and adjust the settings until you look good.
Finally, let's lock you down so you do not accidentally delete a formula by entering data into the wrong cell. Select cells C3 through C7 (which is the length cell plus the four yes / no cells), right-click and select Format Cells. Click the "Protection" tab and uncheck the "Locked" check box. Then click on "OK". On the ribbon, click Review, and then click Protect Sheet. Just click OK to apply the settings in the dialog box that appears. They do not try to protect the sheet with a password, but only fumble. Save the glorious result!
Create a Password Generator in Google Sheets
That was before Google Sheets. Maybe even before Google existed! But I know that a lot of people swear on Google Sheets, so I've taken care to support this project.
I followed my own instructions to create the password generator in sheets, and found that everything was alright on the formula that indicated a random character. Everything worked, but pressing F9 could not be updated with a new random character. While searching for Google, I noticed that you need to press F5 to force an update to update the entire page or change the value of any cell to force an update. It's awkward, but doable. I changed the prompt to "F5 to regenerate".
Instead of recreating the giant formula that does full password generation, I copied it from my Excel spreadsheet. Hallelujah! It worked well! I'm not going to go into detail here, but I've been able to recreate the rules for data validation and hide the unwanted columns as well. If you use sheets instead of Excel, this project may still work for you.
You Did It
Whether you accepted the bare-bones version or applied the failed optimizations, You now have a password generator that you wrote yourself. Although Excel uses a pseudo-random number generator. However, you can add your own randomness by repeatedly tapping F9 before accepting the generated password. And while a hacker may reverse engineer the password generator in a password management product used by millions, your one-time utility is simply not on the radar. You did it!