In 2016, the Electronic Frontier Foundation created some enhancements over the original Diceware Passphrase list by creating the EFF Dice-Generated Passphrases list. This post is my effort to use Microsoft Excel or Google Sheets to semi-randomly generate random passwords by using the EFF’s Long Wordlist [.txt].
Please note, while I am an IT professional, I’m not a cryptographer or mathematician. The passwords generated via these Microsoft Excel formulas are semi-random. They represent a good-enough approach to creating passwords users can memorize. Use passed.pw or LastPass’ Password Generator for longer passwords that contain more entropy. As always, use a good password manager and enable multi-factor authentication when available.
TL/DR: Links to the live Microsoft Excel and Google Sheets worksheets
If you just want some semi-randomly generated passphrases based on the EFF Large Wordlist, select either of these read-only publicly-accessible links.
Results
This is what you’ll find: 10- to 16-character easy-to-remember semi-random passphrases. Refresh your browser (F5) to generate a new round of passphrases.
Screenshot
A screenshot of the EFF Dice-Generated Passphrases via Microsoft Excel.
Example
Here is a static example of the semi-random passphrases these worksheets randomly create.
| 14Good | 14Better | 15Good | 15Better | 16Good | 16Better |
|---|---|---|---|---|---|
| DarnLungScuff1 | stoic-UNWIND-8 | GoryKudosStove0 | tidbit-FOOTSIE-4 | GiftRelicHuddle6 | baggie-WOMANLY-1 |
| IckyLionCacti1 | pagan-PRECUT-9 | EpicAlbumVerse4 | stingy-ARMBAND-8 | ShunWharfBootie3 | varied-SCALLOP-8 |
| GrubLifeSkies8 | humid-STINKY-2 | MoveClassPurge3 | sudoku-HAGGLER-0 | IconGauzeEclair5 | lively-SHELTER-8 |
| RushWindZesty7 | shape-JAILER-9 | OvenAptlyFruit5 | tattoo-SLOUCHY-9 | DawnVersePurple6 | affirm-CONDONE-4 |
| RiftLateTweak7 | mummy-CLAMMY-9 | JeepPhonyFetal5 | drudge-CLOTHES-0 | OmitBakedStress9 | idiocy-SWOONED-5 |
| TallBlipUsher2 | crock-CUDDLE-0 | WishCacheChest1 | tinker-PROWLER-2 | VoidAtlasGravel1 | panama-IMPLANT-6 |
| BathHaltKooky8 | shock-DELETE-8 | HateBootsPesky0 | violet-RUBDOWN-9 | DialEruptTaking2 | deeply-SCROOGE-1 |
| JawsLarkSandy0 | grape-CIRCUS-2 | RakeEruptRigor0 | myself-SKYWARD-3 | DoleUsherTissue0 | idiocy-MUSTANG-6 |
| DarnLeftChaos6 | visor-WANTED-6 | HateDingoIdiom4 | saloon-CLATTER-4 | LurkTiaraHarbor2 | awaken-DENSITY-8 |
| HurtEbayYield0 | couch-GANDER-4 | GulpBunnyDecaf9 | unholy-PROWESS-5 | OozyJuicyGrudge1 | specks-OUTLOOK-5 |
How it works
The essential components to using Microsoft Excel or Google Sheets to generate semi-random passphrases are:
- A hidden sheet named DataEFFLong1 that contains the EFF’s Long Wordlist [.txt], sorted by the number of characters within each word
- A hidden sheet named DataASCII that contains the ASCII Printable Characters, with their Decimal index/values
- A visible sheet named GeneratedPasswords that uses Excel’s INDEX function and RANDBETWEEN function
You will find a few more hidden sheets of data (like city names or shorter versions of the EFF dice words) that you may use if you want to customize your own formulas. Please, do make a copy of either worksheet and modify to meet your needs.
Formulas
My files generate 10- to 16-character passphrases. The 10Good column contains a 10-character passphrase of lowercase, uppercase and numbers. The 10Better column simply adds punctuation. Please look at the examples above to get a good idea.
Good Formula
This Excel formula does not include punctuation.
=INDEX(DataEFFLong1!$F$84:$F$550,RANDBETWEEN(1,ROWS(DataEFFLong1!$F$84:$F$550)),1) &INDEX(DataEFFLong1!$F$551:$F$1478,RANDBETWEEN(1,ROWS(DataEFFLong1!$F$551:$F$1478)),1) &CHAR(RANDBETWEEN(48,57))
There are two INDEX functions that use the RANDBETWEEN function to select a random EFF word from a range of words that are pre-sorted by how many characters are in each word; followed by a CHAR function that uses the RANDBETWEEN function to select a random number.
Better Formula
This Excel formula adds punctuation. In cell B14, I use this formula to select a semi-random delimiter from the ASCII table (characters , – . /).
=CHAR(RANDBETWEEN(44,47))
Then I simply include this semi-randomly selected punctuation into the formula.
=INDEX(DataEFFLong1!$E$2:$E$83,RANDBETWEEN(1,ROWS(DataEFFLong1!$E$2:$E$83)),1) &$B$14 &INDEX(DataEFFLong1!$D$84:$D$550,RANDBETWEEN(1,ROWS(DataEFFLong1!$D$84:$D$550)),1) &$B$14 &CHAR(RANDBETWEEN(48,57))
The result is that the user must only memorize a single punctuation value and number — along with the EFF dice words — to memorize their password.
Again, these passphrases have less entropy than passed.pw randomly-generated passwords like “U7p2uk>R,v)]HTRc”; but they are easier to memorize and should be sufficient when also protected by multi-factor authentication.
Google Sheets formulas
The formulas that Google Sheets’ uses to produce the same results as Microsoft Excel are different, but the concept is the same. Google uses ARRAY_CONSTRAIN and ARRAYFORMULA to wrap the original Excel formulas.
=ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(DataEFFLong1!$F$84:$F$550,RANDBETWEEN(1,ROWS(DataEFFLong1!$F$84:$F$550)),1) &INDEX(DataEFFLong1!$F$551:$F$1478,RANDBETWEEN(1,ROWS(DataEFFLong1!$F$551:$F$1478)),1) &CHAR(RANDBETWEEN(48,57))), 1, 1)
Closing
Feel free to use these worksheets as is. Optionally, add your own data sources and modify the formulas to better meet your needs.
