Learning Vocabulary with a Spreadsheet

I’ve spent a lot of time and energy over the last 15 years or so studying various languages.  Welsh in particular, but others as well.

I’ve experimented with a number of methods for developing vocab.  Usually this involved either physical flash cards, or a flashcard-like system like AnkiWeb (which I highly recommend).

In the last year or so, I’ve developed a method of my own, which uses a spreadsheet.  I use Google Sheets, but it would work just as well with OpenOffice or LibreOffice, or Microsoft Excel.  All but Excel are free.

There are three components to this method: building the spreadsheet, day-to-day use, and daily routine.

Building the Spreadsheet

Create a spreadsheet, with two tabs – one called Words, and one called Quiz.

On Words, you want four headings: the name of your target language, the name of your own language, “#”, and “R”.

Screenshot of the Words tab of a spreadsheet as described, with the middle cropped out so top and bottom fit in a reasonable-sized image.

On Quiz, you want A1 to show you the max value of the Words tab’s column C.  In Google Sheets, this looks like:

=max(Words!C2:C10000)

In A2 through A11, you want a formula like this:

=IF(Words!C2=A$1,Words!A2,””)

The references to C2 and C3 should change, row by row, and the reference to A1 should not.  (Hence the dollar sign.)

Now your spreadsheet is ready.

Day-to-Day Use

Keep the spreadsheet handy whenever you’re working with your target language – whether you’re doing actual language lessons, or just reading a newspaper in that language.  Because I’m using Google Sheets, I can just keep a shortcut in my bookmarks bar.

The spreadsheet comes into play any time you encounter a word that you feel the need to look up.

Screencap from the online magazine Golwg360, article “Lleoli drama ddirgelwch newydd yn Sir Gaerfyrddin”, retrieved 2017-04-14

Any time you feel the need to look a word up (whether translating to or from your target language), check the spreadsheet’s Words tab.

If the word is already in your spreadsheet, increment the value in the “#” column (column C).

As an example: I'd forgotten the word "cychwyn", but I see here it's already in my spreadsheet. So I increase the value in the # column, from 3 to 4.
I’d forgotten the word “cychwyn”, but it’s already in my spreadsheet. So I increase the value in the # column, from 3 to 4.

If the word isn’t already in your spreadsheet, add it, and set the “#” column (column C) to 1.  In the “R” column (column D), insert your spreadsheet’s “random number between 0 and 1” function.  In most spreadsheet programs, that’s usually something like “=rand()”.

Example: The word "dirgelwch" (appearing in the article as "ddirgelwch") was not in my spreadsheet. So I add it, set the "#" to 1, and the "R" to "=rand()".
The word “dirgelwch” (appearing in the article as “ddirgelwch”) was not in my spreadsheet. So I look it up in my Welsh dictionary, add it here, set the “#” to 1, and the “R” to “=rand()”.

Repeat this action any time you feel the need to look a word up.

Daily Routine

Okay, so you’ve got your spreadsheet.  You’re recording every time you encounter a word that hasn’t quite stuck in your brain yet.  What are you doing with that information?

Once a day, you do these steps:

First, on the Words tab, select the entire spreadsheet, and sort it – set “#” (descending) as the top-level sorting criterion, and “R” a a secondary criterion.

Image of the sorting screen as it appears in Google Sheets.
This is how the “sort” step looks in Google Sheets.

The words with the highest scores should now appear at the top of the Words tab.

Screen cap of the top of the Words tab of my spreadsheet, with the highest-scoring words at the top.

Where there’s a tie, the order will be randomized (thanks to us including the “R” column as a secondary sorting criterion).

Then, switch over to the Quiz tab.  If all is working correctly, you should see the highest score that any of your words has, and a list of words with that score – up to a maximum of ten of them.

Screenshot of the "Quiz" tab in Google Sheets, showing the top score of 8, and a list of ten Welsh words that have this score. Their English meanings are not shown.

Then, run down this list, and just out loud, say what you think the words mean.  Quiz yourself, using the best of your recollection.  Guess if you have to.

Then, switch back to the Words tab, and check how many you got right.

If you got all of them right: decrement the score for one of them.  Just one of them.

If you got any of them wrong: increment the scores of the ones you got wrong.  Don’t decrement any of them.

As an added aid, after I do the quiz, I take one of these top words, and stick it on a chalkboard in my home.

Photo of a chalkboard on a wall, displaying the words "cynllun - plan, design, pattern".

For the next 24 hours, as I walk around my home, this reminder will be in my field of view.

A few points:

  • The more vocab you’ve already mastered, the less time-consuming this method is, naturally.
  • I’m not shy about including weird, irregular, conjugated forms, or idiomatic fixed phrases, and making them their own line items.  I have phrases like “Dere i ni” (“Let’s, let us…”), and I have an entry for the verb “gadael” (“leave”) and a separate entry for its slightly strange past-tense form “gadawais i” (“I left”).
  • Contrariwise, if I find myself needing to look up one word, and a different word using the same root is already in the word list… I might just increment the one that’s already there, rather than adding a separate entry.  If I encounter “cyhoeddus” (“public”), and I find it’s not in my word list but “cyhoeddi” (“publish”) is… I’ll just increment “cyhoeddi”.  The issue there, after all, isn’t that I was baffled by that adjectival -us at the end, but that I’d forgotten what the “cyhoedd-” root meant.

And That’s It

As much as possible, I do this every day.  It takes five minutes, it targets the words that combine “it comes up often” with “I have difficulty remembering it”, and I find it works really well.