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!

Triangulation - a polyglot approach to language learning. Image by Nils Thingvall, FreeImages.com

Everyday Triangulation : Three Sides to Every Language Story

A study colleague popped up in our group forum this week, sharing an interesting resource. It was a set of quiz flashcards for the current term’s Swahili vocabulary. But it came with a triangulation twist. It was a Swahili-Spanish set, rather than Swahili-English.

Triangulation – learning one of your foreign languages through another, rather than your first – is nothing new, of course. A beloved technique of polyglotters, it can be an easy, quick-win strategy to learn and maintain / strengthen skills at the same time. Many readily available resources support it, too. Both Duolingo and Glossika have options for learning via a different base language.

The assumption is often that it works best with quite different language pairs, like my colleague’s Swahili-Spanish set. There is certainly a logic to this, as some might expect possible counter-interference with closely related languages. I’ve certainly got some good use out of Langenscheidt’s Polish course for German speakers (a slightly more updated version of my ancient copy is available here!).

Close Triangulation

That said, triangulating with close language pairs does come with a unique advantage. Namely, it shines a bright light on false friends and misleading pairs, which might otherwise remain invisible if English is the medium to learn both.

Take Norwegian and Icelandic, for example. There is an apparent cognate in Icelandic líka and Norwegian like. However, they mean different things: also and alike respectively. If you learn both languages via English, the two will never come into contact with other (at least in your mind), and that discrepancy will remain in the dark. Well, at least until you confuse them in conversation with a native speaker (yes: guilty!).

However, if you create a set of learning resources in Icelandic and Norwegian that makes explicit this (dis)connection, you have a head start.

The same happens with words that are cognates, but slightly overlap in usage. For instance, Icelandic and Norwegian have the cognates sem and som. These can both be used as relative pronouns (the dog that I saw, the doctor that treated me and so on). However, Norwegian som can also be used for the comparing like, as in noen som ham (someone like him). In Icelandic, that doesn’t work at all. Instead, you have to use the term eins og, giving us einhvern eins og hann for the same phrase. It’s exceptionally tricky to learn that distinction if you learn Icelandic and Norwegian through English, but separately from each other.

Triangulating Existing Resources

Great, if you are just starting out, you might say. But what if you are already halfway down the road? By the time I realised the benefit of triangulating Iceland and Norwegian, I already had a ton of English-based Anki flashcards in separate decks for each one. Starting a third set for Icelandic-Norwegian was a less than fun prospect. It felt like treading the same ground all over again.

Tech tools to the rescue, though. There are some clever tricks you can play with your existing data sets to create triangulated versions without starting over. This export / collation technique using Anki and Excel, for example, produces a merged list than can then, in turn, be used to create a fresh Anki deck.

Aside from that, auditing via Excel is a great way to check what you know in one of your languages but not the other.

 

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

Styling It Out With Anki

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 of the program

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

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

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!

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!

Speaking is like a skyscraper. Aim high, but watch your foundations too! Image by createsima on freeimages.com.

Speaking without foundations (and putting it right)

As I sit at the station tapping away at Anki, I’m impressed at the level of the vocabulary I’ve reached: interactive, futurism, equality… All those lovely advanced words! I enjoy a moment of language learner pride, before I remember that when it comes to everyday speaking, I’ve still got a bit of work to do.

It’s a problem you might recognise yourself, if you’ve tried any of a number of accelerated learning techniques with your languages. For me, it was all about grappling with Icelandic in all its glory immediately. I leapt straight into the language a few years back, working with iTalki tutors in a conversational format and lapping up that wonderful vocab.

The pattern was this: prepare a topic to talk on, and chat about it in the lesson. Some of the themes were challenging even by first language measures: politics, social media, technology. But using an “islands” approach, it seemed to be working out really well. I absorbed and consolidated a huge amount of vocabulary and grammar structures.

Speaking high and dry

For sure, it is a learning strategy that builds a solid level of competency, and fast. It has made me quite adept at prepared chat, a wonderfully useful and transferable skill for other areas of life such as public speaking and work presentations. It’s a technique I still use, too. I recently completed the #30DaySpeakingChallenge in Icelandic, and created a short speech on often quite complex topics for every one of those days.

But it is a very particular type of competency, or fluency, that this technique builds.

The crunch comes when you come to speaking a language in the wild. For me, it became painfully clear on trips to Iceland than I almost completely lacked the basic, practical, and social foundations needed to operate on a day-to-day level in the foreign language. Navigating social situations – even producing the most basic of niceties and stock repsonses – felt like pulling teeth.

Yes, I could rattle on about materialism, propaganda and language learning techniques in wonderful Icelandic. But when it came to ordering a sandwich on Reykjavík’s Laugavegur, I was lost.

That’s great – but can you just order a coffee, please?

Swallow that pride!

The warning signs were there well in advance, of course. For instance, I would haplessly flounder after the social glue at the end of lessons. Phrases such as “have a nice week, see you soon!” got stuck in my mouth like treacle toffee. I could clumsily construct them based on grammatical rules and vocabulary. But they never sounded natural.

Now, there is no shame in recognising these lacks, however late. And this is certainly not to bash accelerated learning techniques – I still use and love them. We all learn in different ways, for different reasons. It was always a source of huge motivation to prepare speaking topics on the things I found most interesting.

But here’s the crucial lesson to take: it’s easy to assume that by learning the high-brow stuff, the everyday stuff follows automatically, almost like a side-effect. Sadly, it doesn’t quite work that way!

Two tiers

Instead, it helps to view language learning as a two-tier process. You can leap straight in with advanced topic-based speaking challenges for speed learning. But, at least alongside that, there needs to be a nod to the social reality (if, that is, you are learning the language to use in a social setting some day – not the case for everyone!).

Some of the best resources for this are perhaps those that budding hyperglots might tend to dismiss as too basic. In fact, foundational primers like Teach Yourself – especially those aimed at more casual learners, like the TY “Get Started” series – are gold for training in social speaking. Yes, they can be light on grammar and more obscure vocabulary. But you do that elsewhere because you love it and seek it out! For the stuff at ground level you are liable to skip, you could do a lot worse than go back to one of these courses.

Their strength is in building social muscle memory for the language. This is the more automatic level of trigger-response in language, operating generally below the level of full consciousness. It’s also how a lot of the social interaction in our first language occurs. Just consider how unthinkingly you fire off “no problem” or “you’re welcome” when somebody thanks you. There is close to zero cogitation going on around how to form a grammatically well-formed sentence!

To this end, I’ve returned to what you might call the ‘baby chapters’ of Teach Yourself Icelandic and Colloquial Icelandic. And it constantly surprises me how much of the little stuff I had missed.

The best thing? It’s never too late to go back and learn it.

Many routes to the same destination – walk them all

Admittedly, speaking without foundations is a human flaw baked into the informal, conversational lesson approach, since it is only natural to seek meaningful chat with tutors. But perhaps we can also find time to squeeze in some ‘real world’ modelled dialogues too, like ordering a coffee or checking in at a hotel.

The crux of it all is that there are many routes to that same destination of fluent speaking, none of them mutually exclusive. Treading several different paths gives you a much more rounded feel for the language than a single one.

Diving right into a language and speaking without foundations is an exhilarating and exciting way to learn. But check out your foundations now and again, too!