An egg frying in a non-stick frying pan (image by freeimages.com). How do you ensure your vocabulary doesn't stick together?

Non-Stick Vocabulary : Separating Similar Words

We’ve all been there in the early stages of language learning. Somehow, certain words just seem to blend into each other. Does X mean Y or Z? I keep saying X for Y! And why do all those little words look so similar? You want your vocabulary to stick in your mind, not the individual items to stick together

These recall problems are pretty normal, particularly when you throw in the social pressure of speaking with others, which can even mess with your native language. With a foreign language, the problem is compounded by differences in phonemic salience – that is., which sounds count as important markers to distinguish one word from another. Something really subtle in your native language, like the difference between a hard stop and a palatalised counterpart, can completely change a meaning. Take the pair of words adabu (good manners) and ajabu (wonder, amazement) in Swahili. When I started Swahili classes, I could not separate them in my head for the life of me. It’s likely that my brain just found it tricky to meaningfully separate the sounds represented by d and j, as /d/ often morphs into /dj/ in my native dialect (try saying induce or and you).

Other times, words might get sticky because they share similar structures that co-trigger, like rhyming sequences. That would explain why I also found it tricky to separate the word asali (honey) from the previous two. Latching onto that aXa pattern, it somehow ended up occupying a very similar memory space to adabu and ajabu. And of course, it probably didn’t help that you spell all three with just five letters! It’s the cost-economising (read: lazy) part of the brain spotting patterns and making heuristic shortcuts – even when these are very unhelpful. Tsk. (Incidentally, the brilliant Daniel Kahneman writes about dodgy heuristics in Thinking, Fast and Slow, which is well worth a read if this piques your interest!)

Revisiting Vocabulary

Interestingly, it’s an effect that isn’t confined to brand new languages. It can even happen with old languages we’re dredging up from the past, or low-level maintaining.

Hebrew is one of those for me. It’s not quite a maintenance language; in fact, I can barely even count it as a fully-fledged language of mine. I barely reached A1 in the modern, spoken language, so it doesn’t take a lot of maintaining. I keep it in that list, chiefly, for reasons of nostalgia!

Anyway, a couple of years ago, I sought to do that minimal maintenance a bit more systematically. I grabbed my copy of Routledge’s Colloquial Hebrew, trawled the first six chapters for vocabulary, and dumped it into Anki. I set my Hebrew deck to drip through a single new card a day, and just let time do the rest.

Overall, it’s been a brilliant, low-key method for solidifying all that ultra-basic stuff. But, every now and again, I do struggle to recall certain words. And surprise, surprise, it’s usually those that look a little bit similar to others. It’s adabu-ajabu all over again!

Seeing it through Anki eyes gave me a new perspective on it, though. In test mode, mix-ups are largely artefacts of the isolated vocabulary item problem. It crops up time and time again in polyglot social media circles: don’t drill words, drill structures. Disembodied parts of speech have little salience on their own. Your brain needs something to hang them onto.

Damage Limitation

Of course, when all your Anki cards are done, you’re already in a bit of a bind over this. You could go back and update all your cards to be sentences (sourcing them from a bank like Tatoeba, for example). But that’s a lot of work.

Instead, you can embed mixological words in some kind of context on the fly. When cross-contamination occurs, think of a phrase – however short – to include the word in. Use alliteration, rhyme, any trick to make it stick. Say it out loud, enjoy the sound of it, visualise it. And try to recall that same phrase whenever the troublesome word pops up again. For my Swahili pair, I came up with:

  • mji wa ajabu (a wonderful town)
  • dada mwenye adabu (a good-mannered sister)

In both cases I’ve chosen a word repeating the troublesome letter (d/j) to highlight the problem sound. I won’t say I never mix them up now – but it has certainly helped.

From my novice Hebrew, another example shows that you can sometimes even combine them together. Take tsar (tight, narrow) and tsad (side). Smoosh them up into tsad tsar (narrow side), and they might just end up sorting each other out.

What words do you tend to mix up in your target language? And how do you go about fixing it? Let us know in the comments!

A big slice of cheese. And Anki moved mine! Picture from freeimages.com

Who Moved My Cheese? Anki media folder, Mac edition

It is actually a fair while since I last tinkered under the Anki hood. I haven’t needed to, to be honest. I’d set everything up just right for my current clutch of active and maintenance languages way back, and everything was chugging along nicely.

But polyglot dreams never sleep for long. The need for a fresh round of customising came from an exciting new side project, Croatian. Easy, I thought. I’ll rustle up some cute Croatian card layouts, complete with a cute wee flag.

The problem: everything had changed!

Frustratingly, it was no longer possible to access the media and backup folders from the app itself. There must be some rationale behind this, of course, and it’s not hard to reason why. Wrong moves when messing with app files can be dangerous for your precious vocab database.

But, for low stakes operations like simply dropping an image into the media folder, it seems reasonable to have access to it (with a helpful dose of caution!). Unfortunately, there’s a dearth of how-to out there right now. It took a bit of Googling and re-Googling to find the answer. But, finally, I sorted it.

And here’s how!

Anki media folder (Mac)

The Anki library folder now lives in the following place on a Mac:

/Users/[your_username]/Library/Application Support/Anki2/[your_anki_name]

However, the path is probably hidden to you from the Library level up. To get round that, bring up your username folder (Users/[your_username]) in Finder. Then,  hold down command (⌘), shift and the full stop (period, .) key to show hidden files and folders. You should now see a whole load of extra items, including the Library folder. Drill down from there along the above path, and you should end up in your Anki directory.

If it’s not there, then it’s also worth trying the ‘all users’ version of that path:

/Library/Application Support/Anki2/[your_anki_name]

Once you’ve located it and entered the (now) secret lair, it’s still collection.media we’re interested in as before. You can drop whatever you like in here, and refer to it in your card templates and other custom Anki items – just like in the old days!

Once you’re done, of course, you might well want to hit command (⌘), shift and the full stop again go hide all those oddly-named bits and pieces – until the next time!

 

A dark forest, a good setting for an Anki horror story, perhaps? Picture from freeimages.com

Coming Up Blank : An Anki Horror Story

I lived through an Anki horror story this week. 🧟‍♂️

There I was, skipping merrily through my list of vocabulary, words flying past at a rate of knots. This is going well, I thought, with naive overconfidence.

But then it hit me. I stopped fast in my tracks. Staring blankly at the word on the screen, nothing would rise from the depths of memory. A void. I was peering into the darkness, teetering on the brink. Brain, don’t fail me now.

Then, I scrambled to think back, at the edge of desperation, to the time when I first added that word to Anki. Where did I get it from? Could I just recall what chapter it was in, which website I found it from, where I heard it?

Suddenly, I could see the textbook page, the colour of the background, the shape of the word. Almost sobbing with relief, I realised the ordeal was over.

It had come back to me.

What a close one!

The Right Way To Anki

OK, flippancy aside – why was that a horror story, you ask? After all, my visual memory must be great.

The problem here is that I had fallen foul of the dastardly context effect, and the word was, in essence, tied very tightly to the circumstances I learnt it in. Having to dredge up the exact setting of a vocabulary item on a page to recall it isn’t very efficient in the flow of conversation in the target language.

I only had myself to blame, of course. In my haste to add the word to my Anki collection, I broke the golden rule: only include items in context. That means as few isolated words as possible, and more contextualising phrases and full sentences showing the word in use. Learning dictionary-style does not work (believe me – I learnt that the hard way!).

I’ve seen the results for myself; switching to a more phrase-based vocab drilling routine works wonders for your conversation skills. It’s the rationale behind platforms like Glossika, which you can replicate with your own DIY sentence-based vocab strategy. In short: it works.

So yes, of course I should have known better, guv’nor. But my Anki horror story was a timely reminder to get back on the right track (and we all need those now and again).

Don't hit the whisky when your language learning turns to comedy. Picture from FreeImages.com

Married and Drunk : Comedy Moments in Language Learning

Comedy moments in language learning are pretty much inevitable.

But they make learning fun, too. Unintentional double entrendre, accidential Freudian slips and downright nonsensical gibberish are some of my favourite things about language learning. For one thing, the salience of humour means that you never forget the vocabulary associated with these most unfortunate incidents.

Comedy Cornucopia

Lucky, then, that language provides an endless cornucopia of them. And sometimes it can be the strangest pairs of words that bear an uncanny, confusing resemblance to each other despite being poles apart semantically. A recent favourite duo is ua and -ua in Swahili – flower and kill, oddly enough.

And the language keeps on giving.

Just look at this trio from my recent lessons:

-olewa to marry (a man)
-lewa to be drunk
-elewa to understand

Surely this is a joke Swahili is playing on language learners. Just imagine the comedy misunderstandings! For instance, there is a tiny difference between:

  • ameolewa – s/he is  married
  • amelewa – s/he is  drunk

And…

  • ninaelewa – I understand
  • ninaolewa – I am getting married

That’s just asking for trouble (or laughs).

Keep It Together!

So how can we keep this sparring vocab items separate? As I’ve found with some dangerously close Greek words lately, sometimes it’s better not to. That is, to learn then in close proximity, embedded in a phrase or short story, so that they remain distinct in meaning.

For instance:

Amelewa kwa sababu ameolewa! S/he is drunk because s/he is married!
Nimelewa, lakini ninaelewa! I am drunk, but I understand!
Anaelewa, anaolewa? Does s/he understand s/he’s getting married?

These are pretty fun to learn. They’re less abstract – you can picture a silly story behind them. You can also practise them almost theatrically, reading them out with feeling. And hopefully, by doing so, you’re moving the comedy from your real-life interactions to humorous tableaux in your learning material. Phew.

It’s so much more effective that learning them as single, abstract and separate items on empty-looking vocab cards.

Shrinking violet? You are not alone as a shy linguist! Image of flowers from freeimages.com

He Killed Them with Flowers : Remembering Vocabulary Oddly

If you’ve been following my language learning journey, you’ll know what a keen mnemonic hunter I am. I experiment with all sorts of tricks for making vocab stick, all of it involving spotting patterns and making connections between words. Some of my favourite techniques include linkword, humour and rhyme.  In essence, anything that makes a word or phrase salient – giving it the weight to stand out – is a great memory device.

Death By Flowers

I was lucky this week then, as a pair of Swahili oddities fell into my lap. It’s an unusual correspondence between two quite different words:

  • ua (flower)
  • -ua (to kill)

First of all, it got me wondering whether they were actually from the same root, but through some twisted process of meaning change, they diverged. Maybe the original sense was ‘bloom’ and ‘kill’ was some metaphoric extension meaning “cause blood to ‘bloom’ (burst forth) from the body”.

I know, I know – what a weird imagination I have. That said, the idea can’t be that weird, as the Proto-Germanic for bleed is sometimes conjectured as arising through that very same metaphor.

Digging Up The Roots

But alas, in Swahili it was too fanciful by far. As it turns out, ua and -ua come from quite separate roots in Proto-Bantu:

Clearly a lot has happened to grind those words down to the same form over the centuries. But that leaves us with a correspondence that can help us tie the two together, and ultimately recall them perfectly. For my own mental image, I’ve constructed the phrase ‘aliwaua na maua‘ (he killed them with flowers), which neatly fulfils the bizarreness criteria for salient vocab memories. Oh – and it rhymes, too! I won’t forget either of those words in a hurry now.

The moral of the tale? Look out for oddities and weird coincidences in your target languages. They’re a gift for making lasting vocab memories.

Let's dally in the valley: rhyme can be a great aide memoire. Picture from freeimages.com.

A Rhyme to Remember : Wordplay Vocab Fun

I was really struggling to learn a new word lately. It was κοιλάδα (kiládha), or valley, in Greek. Nothing would make it stick. That is, until I realised the power of rhyme.

The word  has an obvious and natural rhyme in Greek: a much more foundational, essential word, namely Ελλάδα (Elládha), meaning Greece. Suddenly, I had a way to anchor the new word to the existing one in memory:

η κοιλάδα στην Ελλάδα
i kiládha stin Elládha
the valley in Greece

It creates such a musical phrase, and one that is so easy to picture in the mind, that suddenly, remembering it is no longer a bane. Finally, it stuck!

Rhyme is a brilliant aide memoire for words that stubbornly refuse to settle in your mental lexicon. Like other techniques such as rhythm, rhyming enlists sound effects and wordplay to add a memorable dimension to learning material.

So why is it so effective?

Rhyme and Reason

Rhyming is a triple whammy when it comes to language learning. First of all, the creation of a rhyme anchors one new word to another existing one, neural-networking on what you already know. But it also creates a story, a vivid mental picture that helps with recall (much like a beefed-up version of the Linkword system). That valley in Greece of mine is a really nice tableau to bolster the words with a visual cue.

But even more powerfully, rhyme circumvents the ‘words in isolation’ problem of learning new vocabulary. Instead of a lone word, we have added value in the grammatical context of the rhyming snippet, even if that is simply the odd article or conjunction as above. Every little helps. 

Like Lego, rhymes are extendable, too. You can expand the lexical scene by tagging on more and more rhyming words, with your memory the only limit. Another difficult-to-remember word for me in Greek, for example, is χιονοστιβάδα (chionostivádha), meaning avalanche. As another -άδα (-ádha) word, I can simply build it into my little poem:

η χιονοστιβάδα στην κοιλάδα στην ελλάδα
i chionostivádha stin kiládha stin elládha
the avalanche in the valley in Greece

Read phrases like this out loud, and the rhythmic dimension also becomes very clear – yet another support to bolster the memory.

Rhyming Grammar

In fact, learning whole snippets of language in rhyming couplets, rather than individual words, can support grammar acquisition. The following German pair serves as a great example of the dative case with feminine singular nouns:

  • an der Wand (on the wall)
  • in der Hand (in the hand)

You can build rhythmic rhymes like this into more extensive ‘mini poems’ to contain a range of vocab and grammar points. This can be a lot of fun: teaching German, I regularly worked the rhyming game into my lessons. In advance, I would put together a daft bit of verse containing the central words and structures for the current topic. Nothing too extensive – just a few lines of rhyming couplets. Perhaps something like this:

Ich habe einen grünen Hund, er ist ziemlich klug,
Er spricht mit Katzen jeden Tag, und fährt dann mit dem Zug.
I have a green dog, he’s pretty clever,
He talks to cats every day, and then takes the train.

Admittedly, that is a pretty nonsensical scene. And you have to think a little creatively to make this stuff scan! But it is worth the effort: in there, we have some animal vocab, a transport word, and a host of important grammatical points: adjective endings, verb conjugations and so on. Two lines, but packed with handy language learning gems.

These poetical delights would be on the whiteboard when my students entered the room. As the lesson kicked off, we would read through the lines together. Then, I would rub out a few random words, and we read again, reciting the missing words from memory. The process would repeat – rubbing out, reading, rubbing out, reading –  until nothing would be left on the board.

But – as if by magic! – the students could now recite the whole thing. At the end of the lesson, I would ask them to try again from memory once again, and, to their surprise, they could reproduce the whole thing. What a great confidence boost for kids who so often doubted their language learning abilities.

A Rhyme-Honoured Tradition

The power of rhyme is hardly a secret – it is a famously great technique for aiding memory. We have myriad oral traditions of epic poetry to prove the point. For millennia, stories have been passed from generation to generation through memorised verse; ancient texts such as Beowulf may have literary lives stretching back long before they were ever written down.

But you don’t need to be a literary genius to benefit personally – just a handful of words will suffice for some verse. And let’s face it: as beginners, we only have a handful of words to play with. But that makes more a greater creative challenge, right? 

And for when words fail, you can turn to online, multilingual rhyming dictionaries like the following:

The wordplays needn’t stop at rhyme, either. You can play around with other techniques, such as alliteration, to create more memorable vocabulary notes. Duolingo has recently introduced the phrase deiseil agus deònach (ready and willing) into its Gaelic course, for example. Doesn’t that trip off the tongue nicely?

Rhyme Stone Cowboy

So, a little rogue rhyming can go a long way to making tricky vocab stick. Next time you feel the uphill struggle, maybe try going for a ride in the kiládha stin Elládha

Searching by tag in Anki

Playing Anki Tag : From Plain Lists to Topic-Based Fun

Anki users, do you tag your cards?

If the answer is no, then perhaps you should think about adding this natty little superpower to your vocab decks. It’s not only a good habit, but it can turn plain old Anki lists into fun, interactive games like this. How? Read on!

Topical Application

To start with, tagging cards with keywords for topic names like colours, animals, or food, or parts of speech like verb, or noun, gives your data greater searchability. In the Anki browse window, you can then filter on these keywords using the tag: notation.

Straight away, this opens up the possibility to conduct a quick and easy language audit. For example, searching on tag:colours quickly shows if there are any gaps in your linguistic colour palette that need filling.

Filtering your vocab cards by tag in Anki.

Filtering your vocab cards by tag in Anki.

Now, wouldn’t it be nice if you could also test yourself specifically on those queries? Say, pull up all of your food and drink words and blitz them for a bit of extra practice?

Unfortunately, you can’t do that straight out of the box. Anki doesn’t provide a way to create a new or virtual vocab deck by tag. But you can easily export them to make thematic test-yourself activities on other platforms.

Playing Tag with Anki

It’s actually pretty simple to get sets of data out of Anki by tag. In the Browse window of the desktop app, start by tapping out a tag: query on your data as above. Then, highlight all of the matching entries that appear in the list (clicking on one entry and then hitting CTRL + A is the fastest way for me).

Selecting notes by tag in Anki

Selecting notes by tag in Anki

Next, head up to Notes in the menu, and select the Export Notes option.

Exporting selected notes in Anki

Exporting selected notes in Anki

As we’ll be using this data on any number of different platforms, simplicity is the order of the day. For that reason, Notes in Plain Text is the best format to choose for our data. Selected Notes should already be the active choice in the Include dropdown. Make sure to untick Include tags and Include HTML and media references to keep the data as plain as possible. Then, tap the Export button.

Exporting selected notes in Anki

Exporting selected notes in Anki

The result should be a .txt file containing a neatly formatted list of your thematic word list. Magic!

From Anki to Beyond

Now you’re ready to drop that into other edu-game services that have an import feature. Educandy and Quizlet are amongst the easiest, and a good place to start. With Educandy, you can simply upload the .txt file directly, and it handles the rest. With Quizlet, you have to open the .txt file, copy the text and paste it into a little box, but it’s still nice and simple.

Now, you have a whole suite of games you can play that focus entirely on your chosen topic. A brilliant way to granulate your Anki practice a bit – or simply create games for your friends (or students) to learn from too.

Anki vocabulary items imported into an Educandy game

Anki vocabulary items imported into an Educandy game

 

Anki vocabulary items imported into a Quizlet game

Anki vocabulary items imported into a Quizlet game

Sometimes you may need to do a little extra work on the other end. In Quizlet, for example, I needed to reverse the order of columns from term-description to description-term as the site default didn’t match my list. Fortunately, that’s just a single button-click on that platform. Phew!

Tag Tips

Anki tagging isn’t perfect, it must be said. Even the most avid taggers will point out that the app’s default tag management features are a bit basic. For some extra control over them, it’s well worth installing the free Search and Replace Tags add-on. There is also the premium add-on BetterTags, which adds some serious extreme tagging power to your app.

Both utilities are incredibly helpful if you end up with near-duplicate or misspelt tags to tidy up. For instance, I realised I had tagged cards variously as ‘animal’ and ‘animals’ over time. Easy to do if you add cards in tranches regularly, rather than all at once. But a nightmare if you are searching for the topic ‘animal’ and only half of your cards appear.

No problem: the two tags combined like a treat with the Search and Replace add-on.

Whether you’re brand new to tagging or have been tagging like a pro for years, it pays dividends to explore these import-export options with other sites. A bit of variety is never a bad thing!

Anki Stats : Review Graph

Language Learning by Numbers : Anki Stats

If you use Anki to drill vocabulary, it’s tempting to sit back and let the app do all the work. Feed in your phrases, and simply let the algorithms work their magic.

On the other hand, if you really want to know what’s going on, you can dabble in the dark art of Anki stats.

Let’s face it, statistics are not everyone’s cup of tea. I’d be surprised if even half of regular Anki users take a look in the stats tab. Confession: I completely ignored the section myself for years. But with the start of a language resuscitation project recently, that extra information has become meta gold: a way to learn about my learning, and have more control over it.

In short, Anki stats allow us to view the past and see the future.

Get Him to the Greek

Way back when I started learning Anki, Greek was an active project of mine. I eventually rested that to focus on other languages for a while, so tagged my Greek deck as a ‘rested’ language in Anki.

Fast forward to 2020, and my Greek has been resurrected from its lengthy slumber. Firstly, I switched my Greek deck back to active in the Anki options. But given the lack of engagement for so long, I also went for the nuclear option: I reset all my Greek cards. I would drag those words and phrases back to the land of the living (languages) by drilling them all afresh.

The thing is, those active settings are now shared with my other active learning projects in Anki. Anki sets a maximum daily new card limit, which my revival Greek now takes up since I drill it first. That’s the plan for now, of course. But for the sake of planning, it would be great to know when my other languages will get a look-in again.

Stats Life

To keep on top of what’s coming your way soon, two sections in the stats are worth getting familiar with: Card Counts and Future Due.

Anki Stats: Card Counts

Anki Stats: Card Counts

Making sure the Greek deck is the one selected, I call up the Stats window. The number I’m interested in is New. These are new (or reset) cards that are queued to present during future reviews. Only when these have been drip-fed through will my other languages get a chance to serve up new words (if I continue to prioritise Greek).

The total currently reads 392. That sounds colossal, but at 10 new cards a day, I will have worked through them in just over a month. A month, that is, if I don’t add any more words for the time being! But that’s just the point: I can use the information here to make a more informed choice about how regularly I add more words to the deck. I am managing Anki, not the other way round.

Anki Stats : Future Due

Anki Stats : Future Due

Similarly, Future Due takes elements of the card count info, but lays it out graphically. This is incredibly useful – at a glance, you can see how the current crop of Greek words tails off after just over a month. By that point, I will have revised and learnt hundreds of Greek items. That’s also when my other projects will start popping in their fresh cards.

Taming Anki

Knowing your numbers is a little thing, but knowledge is power. Anki is no longer a black box spitting out words with no end in sight. I can see exactly where I’m going. And perhaps that’s the clincher for me, as a visual thinker. I like to see my way. (Incidentally, if you do too, there is an excellent heatmap visualiser available for Anki, too.)

Not everybody works well with woolly goals, either. The stats can give you a sneak peek into your language learning future. From that vantage point, you can visualise the finish line (or at least the next checkpoint).

By date X, I will know 500 words and phrases. That is powerful stuff.

You can be a surface user of Anki. It is tremendously useful even if you only use its basic functions. But getting a hold on your numbers can provide a world of support.

Cross-referencing vocabulary in Excel after some tidying is applied with IFERROR.

Excel for Polyglots: Comparative audits to keep languages in sync

Duolingo, Memrise, Anki, Microsoft Excel. Huh, wait – Excel? How is that a language learning app?

Well, the Office software has some handy features that just happen to be right up our street as language learners. Namely, the ability to curate and administer lists in table form. And it just happens that this can be particularly useful if you learn more than one language.

One source of frustration as a polyglot learner is the discrepancy of vocabulary level between languages. This can be most obvious with fairly close language pairs. For instance, when practising Icelandic, I often realise that I know a term in Norwegian – but not the language I am trying to speak.

So how best to address these discrepancies?

Language auditing

Getting into the habit of performing a regular language audit, such a revisiting beginner materials is a good strategy for any learner. But one particularly powerful method for multi-language learners is the comparative audit.

In short, a comparative audit is simply taking stock of which words you know in one language, but not the other.

At the very early stages of learning a language, this can be as easy as scanning down a list. But when you get to the point of having hundreds and hundreds of words in your vocab store, the task is mammoth.

Enter Excel, data wizard!

Microsoft Excel and VLOOKUP

Most of us will have used Excel or another spreadsheet program at some point. But like me, you might not have gone beyond basic numerical information and a few simple sum functions.

It turns out that Excel is pretty good at handling textual data too. Are you thinking what I’m thinking? Yes, vocabulary lists! And it has a special function, VLOOKUP, which allows you to compare data between two tables. Sounds just perfect for our comparative audit.

Here’s how to enlist Excel to your polyglot cause in a few simple(-ish) steps.

Step 1: Port your data into Excel

First things first – you have to get your vocabulary data into Excel. The easiest way is to export from your program of choice as a CSV (comma-separated values) or tab-delimited text file. If you use Anki, this is as easy as heading to File > Export and selecting ‘Notes in Plain Text (*.txt).

Ensure that you only export the basic data and no media or tags. Ideally, you should just be exporting a word and definition / translation field. My Norwegian and Icelandic decks, for example, are populated by vocab notes with an English and Target Language field.

Export a separate file for each of the two languages you want to compare. In my case, I end up with two files, norwegian.txt and icelandic.txt.

Exporting data from Anki

Exporting data from Anki

Step 2: Import your vocab into Excel

In Microsoft Excel, create a fresh spreadsheet document, and head to File > Import. Select Text File, hit Import and locate your first exported vocabulary file from above. To preserve accented characters in our Anki list, select Unicode (UTF-8) as the File origin.

Importing vocabulary into Excel

Importing vocabulary into Excel – note that ‘Unicode (UTF-8)’ has been selected as the file origin to make sure accented characters are handled correctly.

Create a second sheet in the same document, and import your other list of vocabulary into that. You should now have a two-sheet spreadsheet document, each sheet showing a list of words in a different language. For clarity, make sure you name your sheets too. Simply double-click on the tab titles “Sheet 1” etc. to do that.

Step 3: Format your lists as tables

In each sheet, click and drag across the table to select your whole vocabulary list as a block. Now, click Format as Table in the Home section of the function ribbon / toolbar. It doesn’t really matter which style you use – I choose the colour I like best!

Once that’s done, change the new column headers to something more meaningful than the default values. I use English and Norwegian in my example below. One caveat – you need to have a column with the same title in both your tables for the VLOOKUP trick to work. Here, English will be my common column between Norwegian and Icelandic.

Vocabulary data formatted as a table in Microsoft Excel

My Norwegian vocabulary data formatted as a table in Microsoft Excel

Now, instantly, these is already more useful to us than static lists. Formatting as a table means you can use the column heading drop-downs to sort and filter your entries. Try it – sort alphabetically on the target language column. You’ve turned your data into a nifty dictionary! Not our primary goal, but a nice trick on the way.

Before we go on, it’s a good idea to name our tables so they are easy to refer to later. To do this, click anywhere in your table, then switch to the Table tab in the ribbon / toolbar. The simpler, the better – below, I just call mine Icelandic.

Naming a table in Excel

Naming a table in Excel

But now it’s the turn of our star, VLOOKUP. This is where the real magic happens.

Step 4: Adding a comparative column

Click on the target language column header of your second table and copy it (CTRL + C). Now, go to your first table, select the cell next to the target language column header (C1 in my example), and paste (CTRL + V). It should add a blank new column within that table. Let’s fill it up!

In the first cell under that new column header, we type in our VLOOKUP formula. This will depend on what you have named your tables and sheets, but mine looks like this:

=VLOOKUP([@English], Icelandic, 2, 0)

Let’s dissect that just now. The first item in the brackets is the column of the first table we’ll use at the lookup – the English entry. The second item, Icelandic, is the table we’ll look for a value in. Remember, we named that table a little earlier. The third item, 2, is the column number we’ll look for that item in, which is the target language column of the Icelandic table. Finally the fourth value, 0, is a flag to Excel that we want exact matches only.

If that boggles, simply start typing =VLOOKUP( in the cell. That calls up Excel’s formula hints and point-and-click formula building, which should help you tie things together accurately.

After doing that, something special happens – suddenly, the whole column is filled with entries. If the English term was found in the Icelandic table, the corresponding Icelandic word is pulled in. If not, we simply get #N/A.

A quick note if that doesn’t work immediately: check that the data type of the cells in that third column are set to format as General, not Text.

A cross-referencing table in Excel using VLOOKUP

Our first step in creating a cross-referencing table in Excel using VLOOKUP.

Not very tidy, is it? That #N/A is simply stating that the lookup resulted in nothing at all.

Step 5: Tying off the loose ends

We can make it all look better by wrapping it in another Excel formula, IFERROR. Change the formula in that first cell to:

=IFERROR(VLOOKUP([@English], Icelandic, 2, 0), "-")

This tells Excel to carry out our VLOOKUP function, but to return a dash if it results in an error (i.e., no data). Suddenly, it’s looking a lot neater.

Cross-referencing vocabulary in Excel after some tidying is applied with IFERROR.

Cross-referencing vocabulary in Excel after some tidying is applied with IFERROR.

Now it is crystal clear where you know a word in one language but not the other. To make things even clearer, click the dropdown on that third column, and filter it to show just the dashed elements. There is your list of words to work on in the second language!

Filtering your vocabulary items in Excel.

Filtering your vocabulary items in Excel.

Alternatively, filter on everything but the dashes to revel in the wealth of words you know in both. Enjoy that moment of pride!

For reference, here’s an example Excel file comparing sample vocabulary in French and Spanish.

Where to go from here?

What you do next is up to you. But now, you have the data in your hands, and data is power: what you know, you can act on. Export the filtered list of gaps to work on learning missing vocabulary in any number of ways.

Clearly, you can take these techniques a lot further, too. Currently, the table only checks one way, such as Icelandic to Norwegian in my example. But you can experiment with the same techniques to create much more complex and comprehensive spreadsheets to interrogate both ways.

Lastly, I’ve used Microsoft Excel in this example, but the same functionality is available in other spreadsheet programs, too. The free alternative Google Sheets, for example, has its own VLOOKUP function that works in an almost identical manner. Play around with the tools available, and you can add that dull old spreadsheet package to your list of exciting, innovative language apps!

Have you given this trick a spin? Have any interesting and useful variations on it? Please share in the comments!

An owl - not the Duolingo one, but probably related. Image by Pamela Benn on freeimages.com

Approaching Duolingo : One Way to Catch An Owl

In case you hadn’t noticed, Duolingo released its Scottish Gaelic course early this week. And naturally, I leapt straight in like a pig in mud!

Like many, I had waited patiently and eagerly for this release, not least since I study Gaelic at evening classes in Edinburgh. Despite a resolution not to race ahead, Duolingo’s early Christmas present was a gift too good not to open straight away. As expected, it is a joy of bite-sized vocabulary snacks.

Devouring unit after unit, it struck me that there are many ways to systematically approach a resource like this. The question of how to tackle Duolingo also cropped up recently in discussion with excellent iTalki tutor Marcel, who is running a Duo-challenge WhatsApp group I’m part of. Personally, I prefer a two-wave method. I outline it here in the hope that it helps others who, at first glance, find a huge topic tree a bit overwhelming, and wonder how to tackle it.

The two-wave approach to Duolingo

This method has a lot in common with the forward-loading approach to course books, which is one of my favourite ways to tackle language learning material. It involves an initial, exploratory reccy through the material, combined with a systematic and focused follow-up.

The first wave

When you first open up that course, it is all about getting to know the terrain. Like hacking your way through undergrowth, you need to clear a path first.

Duolingo prevents users from accessing material out of order by locking lessons until the previous one is completed to the first of five XP levels. However, unlocking to this first, blue level is usually just a matter of a few short introductory lessons. So, in the first wave, you simply steam ahead, unlocking topic sections just to level one before opening up the next one. In no time at all, you’ll have blued up a good portion of your tree (or maybe even all of it with a shorter course like Gaelic).

Don’t worry too much about retention at this stage. The aim is to have fun, notice the shapes and sounds of the language, and lay down some passive pre-knowledge before we get serious. Above all, it is the no stress stage. Just explore and enjoy your new language!

The second wave

When you have unlocked a fair few topics, the second wave can begin!

At this stage of your Duolingo attack, you go right back to the very first topic section. Work through slowly and carefully, one by one, hammering each topic to level up fully to gold before moving on to the next.

Note that there is no speed pressure here. You can gold a topic up in a single sitting, or take days to do it. The important thing is that, during this more focused stage, you resist the temptation to move on before a topic is gold.

The second stage is where the deep learning occurs. But thanks to the familiarity you have from the first pass, you already have a ground layer to build on.

A snapshot of my progress through Duolingo Scottish Gaelic, showing how the two-wave approach works.

A golden wave works its way through Duolingo!

Own the vocabulary

Here is the crucial turbo-boost you can engage during the second wave: make the vocabulary your own. Duolingo shouldn’t simply be a passive resource. For long-term learning, you should record all those new words and phrases in your own, separate collection for drilling.

Anki is hard to beat on that score. Every time I meet a new item in the lessons, I look up a detailed definition using a resource like Wiktionary, and add it to an Anki deck. Since Anki will also drip-feed that vocabulary to you using its clever spaced repetition algorithm, you effectively double the learning power of the Duolingo course.

And that’s all there is to the two-wave approach. Familiarise first, study systematically, and make the material your own as you go. A simple but effective way to catch an owl!

How do you approach Duolingo courses? Do you use a different technique? And have you started Gaelic yourself? Let us know in the comments!