“Cloze” to Perfect : Extending Anki’s Gapfill Activities

January 29, 2023Leave a comment

Ever had that realisation that there was a better way to do what you doing all along, one hiding under your nose the whole time? Well, that was my week of epiphany with Anki.

Anki has included cloze functionality pretty much from the get-go. If you’ve not come across cloze before, it’s basically fill-in-the-missing-word. Your card pops up, and instead of providing the whole answer, you just recall the missing section.

Cloze is a great tool in your learning box to ward against the isolation issue with vocab. Learning items in context is just as (if not more) important than learning individual items. If you drill ich habe einen Hund (I have a dog) in German, you’ll not only pick up Hund, but a handy sentence frame and grammatical information to boot. Vocab plus structure is always a winning combo (and why mass sentence drilling is so powerful).

Native Cloze in Anki

Anki’s native cloze capabilities are simple enough to use. To make a cloze card, you simply type in your sentence with the gapped words surrounded by braces, along with a special tag to signify the gap:

Ich habe einen {c1::Hund}.

In the toolbar, there’s even a button to do this for you – just highlight your word to gap, and click […].

There are even some extra tricks in there, right out of the box. For instance, you can add a hint that appears in the blank before you guess:

Ich habe einen {c1::Hund::noun}.

You can add several gaps, or sets of gaps. For instance, if you change a couple of them to c2 instead of c1, they’ll be treated as separate question sets:

Ich {c1::habe} einen {c1::Hund} und er {c2::ist} sehr {c2::lustig}!

When you come to test them, the c1 and c2 words will appear on separate cards. Really handy to drill more complex material.

As great as it is, though, it’s not perfect. For one thing, Anki hides and shows all your grouped gaps at once. Not great if you have two or three gaps on one card, and want to test your recall of them in their own right, rather than in one fell swoop.

Enhanced Cloze

Thankfully, the Anki Open Source community comes to the rescue. Anki Enhanced Cloze retains all the native functionality that Anki already did so well. But it also allows for individual hide/show within a set, adds a number of useful extra fields, a main/pseudo cloze distinction and some much nicer formatting.

Anki Enhanced Cloze

The resulting card is so much more flexible for self-testing, and looks much nicer, too. And the best thing? Card creation follows exactly the same method as Anki’s native cloze, along with the extra little hint trick. It’s a very quick way to make your cloze cards a lot more effective.

Needless to say, I’ll be spending some time this week converting my older cloze cards to the newer format. It’s one of those cases where a better way of doing things was hiding under my nose the whole time – the add-on has been around since 2021. Ah well – better late than never!

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

October 23, 2022Leave a comment

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!

An old, brick-style mobile phone. The notification problem was significantly less noticeable with these! Image from freeimages.com.

Creating a Notification-Free Language Routine

May 1, 2022Leave a comment

We’re slaves to our mobile devices these days. At least that’s what a whole tranche of research suggests, popularised in books like How to Break Up With Your Phone, Digital Minimalism and Smart Phone, Dumb Phone. Mobile operating systems bake in an addiction-dependency loop, the notification system being the carrot to our donkey brains. We just can’t help coming back for more.

I took a short study break away recently, in order to get some well-needed head space. My mistake? I didn’t plan any notification downtime. And it was my language learning apps that rudely interrupted my calm most, calling me to constant action. Green owl, I’m particularly looking at you. It’s time for your lesson! You were knocked out of the top ten! There’s still time to move up in the Diamond league!

Now, I’m a good lad and I always do my daily Duo. But the nagging began to feel a bit… stressful.

Pavlov’s Notifications

There’s an element of shtick to all this, of course, that Duolingo has very successfully spun into social media gold. It’s genius, to be honest; a top-class case study in building a brand identity. That mock menace is all part of the fun in the learning. It’s often great to have bad cop on our backs, cajoling us into action when we’d rather just idle.

But it can all feel a bit Pavlov’s dogs at times.

As a bit of a control freak myself, I find that aspect particularly unsettling. How much control have I ceded to my phone’s notification system? To what extent am I still enacting my own free will here? And how well has that notification system trained me to keep running back for more endorphin hits, even sans notification? Checking the phone first thing in the morning, walking to various destinations (never a great idea), last thing at night…

If I were a dog, my trainer would be collecting an award right now.

Granted, we’re not talking about mindless entertainment or trivial content. Those language learning pings emanate from some of the best educational apps out there: Duolingo, Anki, Glossika. Surely that isn’t a waste of time?

Well, no. But as part of a wider problem of notification addiction, I thought it was time to wrest control back just a little. To start using these resources on my own terms again.

Off With His Notification!

So it’s off with the Duolingo notifications, for a start. As much as I love the competitive side of it – daily targets, leagues, monthly quests – I hate being told what to do (it’s that control freak in me again). I already love doing my daily lessons. I’m not going to forget, so you don’t have to stress me out by reminding me every five minutes that I’ve dropped out of the top ten.

Likewise, I’m always on the lookout for more non-digital opportunities to learn and practise foreign languages. I’m building up an old-school language library, and taking time to go through those wonderful, physical materials mindfully, and far from my phone. I build in plenty of one-to-one and group classes to get time with real human beings. I’m using my devices for more slow learning tasks like reading books and listening to podcasts, which complement the fast-and-furious educational app mode (variety is key!). And I’m trying to follow general advice around breaking phone addiction: having a no-scroll rule for morning and night, and giving myself a phone curfew.

It is possible to break notification addiction, while still benefitting from wonderful resources like Duolingo. You just have to cede to your own inner control freak now and again.

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

Who Moved My Cheese? Anki media folder, Mac edition

August 29, 20211 Comment

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

April 11, 2021April 11, 20211 Comment

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).

Anki Image Occlusion : Lessons from the Medics

November 29, 2020November 29, 2020Leave a comment

It was a long time coming, but I finally did it: I started using Anki for something other than foreign language vocabulary. Anki is steadily creeping into the rest of my life.

I know. What is the world coming to?

Admittedly, the new subject wasn’t a huge leap. I’ve started creating flashcards to drill terms and concepts from linguistics. That said, it does represent quite a departure from the way I usually create drill lists in the app.

The chief difference is the complexity of each chunk of learning material. Rather than one-to-one word and phrase combinations, we have terms with much more complex, interlinking definitions. And however brief, the information is a lot trickier to condense than simple vocabulary. Some of my flashcards were looking decidedly clunky.

If only there were some way to make it all a bit more concise and economical.

Anki-nspiration

So where to look for flashcard inspiration? Well, as it happens, language learners haven’t completely monopolised the Anki world. In fact, the app has quite the double life as a tool for medical students learning, amongst other things, terms and complex definitions!

It certainly pays to see how a diverse bunch of people use the same tool. We can learn a lot from users in other fields. And, nestling amongst the sprawling web of Reddits, there is a ton of general advice on optimising your cards.

Perhaps the cleverest trick of medical Anki users is the use of imagery for testing. Now I’m not talking about simple, one-to-one picture-word correspondences. Ohhhhh no. Medical students take it to another level, condensing lots of information into a single tableau. But to do that, they need to enlist some extra help.

Image Occlusion for Anki

The Image Occlusion Add-On for Anki allows for some quite sophisticated multi-field labelling questions. Obviously, these are ideal for drilling parts of the brain or major arterial pathways. But they lend themselves to pretty much any topic. If you can cover it up, you can turn it into an image occlusion activity.

For instance, you might think that linguistics is a rather text-heavy subject. Difficult to find too many diagrams to label, perhaps. But with a bit of creativity, you can adapt anything to fit the mould. Here’s an image occlusion activity I put together to drill the IPA consonants table and manner / place of articulation features:

Anki Image Occlusion with an IPA chart

Table-based data is actually perfect for these kinds of activity. And if you remember things quite visually, as I do, then making image activities out of them can yield some great memory results.

Fortunately, the Image Occlusion Add-On creators have provided a raft of training videos to learn how to use this incredibly useful tool. And – I’m very relieved to say – it’s not particularly difficult to get to grips with at all.

It is easy to forget that the Anki universe is quite massive. There is a huge amount of inspiration out there beyond our little bubble. Thanks, medical students, for pointing out this particular path!

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

November 22, 20201 Comment

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.

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

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

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

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 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!

A wheel of colours. Image by Karen Barefoot, freeimages.com

Styling It Out With Anki

September 20, 2020September 20, 2020Leave a comment

Flashcard wonder Anki is not only a rock solid learning tool, but also one of the best maintained pieces of software in the linguaphile sphere. Updates come regularly, and with each one the app gets more and more robust.

One impact of the most recent updates, for instance, has been to organise the interface for styling Anki cards a lot more tidily. What was formerly a slightly clunky, overwhelming form window now supports a more logical workflow.

Change can pull the rug from under our feet, though – so how do we style our cards now?

Stylin’ It With Anki

Accessing the styling panel is thankfully much the same as before. To access the updated card styling panel, first click Browse in the desktop app. Then, select one of your card types in the left-hand list, and click on the Cards… button that appears in the main right-hand panel (it doesn’t matter which entry is selected – the styling is shared by all of them).

Now, you get a nice, neat interface with tabs for the front and back sides of the card, as well as a shared styling tab for both. Previously, the window presented all of this information in a single window. The new format is a lot less overwhelming, especially if you are new to the feature.

Styling Anki cards in the latest version

So where, exactly, do we do all the fancy stuff in this new layout?

Nice and Easy Styling

To give it a whirl, you can start with one of the simplest but most effective tricks: adding some colour to a card template with CSS. Colour-coding is fantastic for keeping multiple language projects apart if you use Anki for multiple languages or subjects.

Here’s a code snippet you can drop straight into that ‘Styling’ tab to give you a basic outlay for changing colours:

html, body {
background-color: green;
}
.card {
font-family: arial;
font-size: 28px;
text-align: center;
color: black;
background-color: white;
border: 9px solid red;
}
hr {
border: 2px solid blue;
}

Here is a version of that in action, modelled by my lovely new Swahili cards:

Brightening up some Swahili vocab cards using CSS in Anki

I was a bit sneaky and threw in something extra here: that smart little Tanzanian flag. It’s no bother to do this, either, but it got a little more difficult in the latest update.

No Thanky, Anki

Adding images to your cards via the application media folder, be they template images for all cards, or individual learning items, ensures that they sync across your devices. But oops – the link to the folder has now disappeared from the Preferences > Backup window.

Not to worry. You can still locate this it in your file system, which is a bit longer-winded but works the same way. On MacOS, you should find this in:

[your user directory]/Library/Application Support/Anki2/[your username]/collection.media/

In Windows, try:

AppData/RoamingData/Anki2/[your username]/collection.media

Once located, you can drop images and sounds into this folder to use in your cards. Every time you sync from the desktop, the app saves these files in your online account.

In the styling tabs, you can then reference them by filename – no path required – to add them to cards. For instance, to drop a Greek flag PNG, present in that folder as flag_fr.png, onto a card in the new edit window, paste in this code (adjust the width and height as necessary):

<img src="flag_gr.png" width="50" height="38" />

Embedding an image into an Anki card template

It goes without saying that you should always be very careful when accessing and changing the contents of the collection.media folder.

The updates to Anki are great tweaks that improve usability (although we would love the Backups Folder link back, please!). Here’s to the app going from strength to strength in future revisions!

Language Learning by Numbers : Anki Stats

August 2, 2020August 2, 2020Leave a comment

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

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

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

January 26, 2020January 26, 20208 Comments

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

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 – 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.

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

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.

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.

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.

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!

Native Cloze in Anki

Enhanced Cloze

Share this:

Revisiting Vocabulary

Damage Limitation

Share this:

Pavlov’s Notifications

Off With His Notification!

Share this:

Anki media folder (Mac)

Share this:

The Right Way To Anki

Share this:

Anki-nspiration

Image Occlusion for Anki

Share this:

Topical Application

Playing Tag with Anki

From Anki to Beyond

Tag Tips

Share this:

Stylin’ It With Anki

Nice and Easy Styling

No Thanky, Anki

Share this:

Get Him to the Greek

Stats Life

Taming Anki

Share this:

Language auditing

Microsoft Excel and VLOOKUP

Step 1: Port your data into Excel

Step 2: Import your vocab into Excel

Step 3: Format your lists as tables

Step 4: Adding a comparative column

Step 5: Tying off the loose ends

Where to go from here?

Share this: