EFF Dice-Generated Passphrases via Microsoft Excel or Google Sheets

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.

EFF Dice-Generated Passphrase via Microsoft Excel
EFF Dice-Generated Passphrase via Microsoft Excel

Example

Here is a static example of the semi-random passphrases these worksheets randomly create.

14Good14Better15Good15Better16Good16Better
DarnLungScuff1stoic-UNWIND-8GoryKudosStove0tidbit-FOOTSIE-4GiftRelicHuddle6baggie-WOMANLY-1
IckyLionCacti1pagan-PRECUT-9EpicAlbumVerse4stingy-ARMBAND-8ShunWharfBootie3varied-SCALLOP-8
GrubLifeSkies8humid-STINKY-2MoveClassPurge3sudoku-HAGGLER-0IconGauzeEclair5lively-SHELTER-8
RushWindZesty7shape-JAILER-9OvenAptlyFruit5tattoo-SLOUCHY-9DawnVersePurple6affirm-CONDONE-4
RiftLateTweak7mummy-CLAMMY-9JeepPhonyFetal5drudge-CLOTHES-0OmitBakedStress9idiocy-SWOONED-5
TallBlipUsher2crock-CUDDLE-0WishCacheChest1tinker-PROWLER-2VoidAtlasGravel1panama-IMPLANT-6
BathHaltKooky8shock-DELETE-8HateBootsPesky0violet-RUBDOWN-9DialEruptTaking2deeply-SCROOGE-1
JawsLarkSandy0grape-CIRCUS-2RakeEruptRigor0myself-SKYWARD-3DoleUsherTissue0idiocy-MUSTANG-6
DarnLeftChaos6visor-WANTED-6HateDingoIdiom4saloon-CLATTER-4LurkTiaraHarbor2awaken-DENSITY-8
HurtEbayYield0couch-GANDER-4GulpBunnyDecaf9unholy-PROWESS-5OozyJuicyGrudge1specks-OUTLOOK-5

How it works

The essential components to using Microsoft Excel or Google Sheets to generate semi-random passphrases are:

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.

Leave a comment

Your email address will not be published. Required fields are marked *