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!

Journaling, or writing a diary, can be a wonderful tool in your language learning kit.

Dear Diary… Get personal with language learning through journaling

Sometimes you just have to let it all out. To that end, journaling, or keeping a regular record of the important events in your life in writing, is as old as the hills. From the Latin diurnalis (‘of the day’), diary writing has been both an emotional outlet and historical ledger for countless people. Some, like Samuel Pepys, ended up becoming very famous for it. In fact, the earliest evidence of diary keeping we have dates back nearly 2000 years.

Today, experts continue to expound upon the benefits of journaling. Amongst other things, the positive impact of diary writing on mental health is a popular topic for discussion.

But what if you could tap into some of that power for your own language learning? There are some solid reasons why journaling counts amongst the ultimate daily writing tasks for language learners. Here are just a few of them, along with some tips on getting into language journaling as a total newcomer.

Mine relevant vocabulary

Have you ever found your learning material a little impersonal? Mass-produced language courses cater for the common denominator. The topics you study can sometimes feel a little disconnected from your real life circumstances. As useful as ‘At the doctor’ might be as a vocab theme, it’s not something than many have many learners enthused at first glance.

Conversely, journaling about your own life makes for a beautifully personalised learning journey. As a vocabulary mining exercise, the kind of things you will look up will be very relevant to your life.

Describing people, places and experiences that are important to you increases the salience of each word, and, through that, increasing the likelihood of easy recall. Looking up and claiming those new vocabulary items will give you a real sense of ownership over them.

Conversational relevance

What’s more, the kind of language you use while journaling carries over wonderfully to conversational speech. Think about the kinds of thing we chat to friends about: what we’ve been up to lately, where we’ve been, who we’ve seen and what we think about it all. Journaling is like a masterclass in everyday gossip. Soon, you’ll be chatting over the garden fence in the target language like the best of them!

Connect emotionally with learning

There is no less effective learning than learning for learning’s sake. The brain must regard learning as relevant, and make emotional connections, in order for material to stick. Think of it this way: learning a language should not be about creating a box labelled, say, ‘French’, and filling it up with new things. Rather, it should be about weaving in a whole new set of connections from new ‘French’ material into your existing neural network. Journaling is a fantastic way to stitch together new language material with your existing emotional world.

Make learning cathartic

Journaling can be cathartic. You can work out your everyday frustrations on the page. And by doing so, you start to associate the target language with those warm, fuzzy feelings of emotional release. These kinds of positive associations make for very strong learning experiences!

Motivation to write

Some skills are easily overlooked when learning a language independently. Writing, in particular, is an easy one to neglect. Part of the reason for this is motivation, again; it is difficult for the brain to grasp a point to arbitrary written tasks traditionally given by textbooks and teachers.

Not so with journaling – for all of the reasons above, diary writing can light a fire under some learners’ language bonfires. It can be an absorbing, steam-letting, exciting exercise, and one that you look forward to every couple of days.

The potential to care about what you write about can be nurtured, too. Why not invest in a shiny new Moleskine to journal in, for example? Taking pride in your own writing is yet another route to encouraging your skill to blossom.

A unique souvenir

The best journal writing is the kind that you can look back on weeks, months and even years later, and re-experience your adventures with travel and languages. Writing about your travels in the target language country – in the target language – is a wonderful way to record those moments for posterity.

While you travel, you will also come across lots of new words on public signs, posters and similar. Referencing them in your writing, perhaps even illustrated with pictures, will keep them safe and help you commit useful ones to memory.

Your own secret code

Of course, the chances are that writing in another language lends a whole new level of secrecy to your writing. This takes us back to Pepys, who used a code based on Spanish, French and Italian for some observations deemed a little too sensitive for prying eyes!

Journaling tools and software

Of course, there is nothing quite like keeping a journal the old school way, in that beautiful Moleskine. But there are myriad digital tools to choose from, too.

Two dedicated journaling apps, however, stick out of the pack for me. They have both been designed specifically for the task of diary keeping, and aim to encourage the user to write. They also come with extra features such as password protection, which could be handy if you are writing down your most sensitive secrets – whether or not they are in another language!

Day One

Apple aficionados will certainly want to take a look at Day One. This premium app – currently available only for OS and iOS – is both beautiful simple and clean, as well as feature-packed. If you combine language learning with travel, its geo-tagging of posts makes it a particularly valuable investment for the language journal keeper.

The app can be locked with your fingerprint on a mobile device, which keeps your target language musings nice and private.

Journey

Journey offers the same broad features as Day One, but is available on Windows and Android platforms, too. User can add multiple photos and video to entries, which could be put to great use when journaling about your linguistic adventures.

Both Day One and Journey are excellent apps for journaling, with little to separate them. Both are free to download, with premium features unlocked with in-app purchases. Journey uses Google drive to sync its data, which some users might prefer over the proprietary sync service that Day One now uses.

Other text editing software

Of course, you don’t need to use a dedicated journaling app to start documenting your life in the target language. My first digital journal in a foreign language was simply an iOS Pages document. I just added a little Russian to it each day, and soon it had grown to the size of a short story!

These come with their own benefits, too. While the layout is much more general compared to a dedicated journaling app, you also have the freedom to design your own diary format. Additionally, Word Processing apps include more heavy-duty features of interest to linguists, such as spell-checking dictionaries in a range of languages.

There is no shortage of text editing programs to try out your journaling in. What’s more, many of them are free! For instance, Google Docs offers a solid, cross-platform option for no cost at all. As well as the browser-based web app, it is also available as a handy Android or iOS app. Then, of course, is the behemoth of Word Processing, Microsoft Word, also available across a whole range of platforms and pricing plans, from free to paid.

Specialist writing software

Perhaps you feel like something with just a little more creative nudge than the big, bold industry standards. You are in luck again; there is a burgeoning industry in apps designed to encourage and support creative writing.

They are often no-frills, but organised to make writing as simple a process as possible. For newbie diarists and budding authors taking their first steps, that could make the difference between getting into it, or getting overwhelmed and giving up.

Some of the best include:

On the one hand, these kinds of app tend to go off the beaten track of Word Processing as you know it. However, the pay-off is billed as greater support for the creative writing process.

Under lock and key

One last word of warning… Do be careful where you leave that diary. There is nothing like a burning motive to aid comprehension in a foreign language. And needing to know what somebody wrote about you can turn even the most linguaphobic in our midst into eager, urgent learners!

What are you waiting for? Happy journaling!

Programming in binary code

Love languages? Try programming!

Programming languages have a lot in common with human languages. For a start, they all have a very particular vocabulary and syntax. You need to learn the rules to assemble meaning. And both machine and human languages are tools for of turning concepts in our heads into action in the real world.

My love of languages blossomed around the same time as my fascination with computers. I’d tinker around in BASIC on my Commodore VIC-20 as a little kid, getting that early PC to just do things. (I know, that really dates me!) And today, I’m lucky enough to have made a career combining those two strands together as an educational software developer.

Works in progress

That said, it’s a career that never stands still. And, just as with human languages, it’s important to maintain and improve your skills all the time. In the same way that ‘fluency’ is an ill-defined and unhelpful ‘completion’ goal, you never really stop learning in the tech industry. There’s no end-point where you down tools, show your certificate, and say “I know it all now!“.

A fantastic source of development training for me of late has been the peer-tutorial site Udemy. I like the nature of the platform, allowing ordinary folk the chance to share their skills (and earn a bit of money from it, too). I also like the pick-and-choose nature of it, where you pay per course, rather than an all-in subscription. That’s one reason I always felt I wasn’t getting enough usage from the industry training giant, Lynda.com.

In fact the only downside to Udemy is its odd pricing model. Courses are listed under a ‘normal’, inflated price, but are almost always available at a discount. This discount varies, meaning that users end up course-watching until the price is lowered. Then they pounce, usually at a very reasonable rate of around £10 or so. I realise that the commercial psychology behind it is to increase the sense of bargain, but it does seem a little convoluted.

What I’m working on

In any case – there are some gems of courses on there. That goes especially for those who fancy learning some programming for educational applications. For a brief overview, here are some of the fantastic resources I’ve found useful:

Swift 4 and iOS

Apple introduced the Swift language as a successor to the clunky Objective-C language in recent years. It’s much easier to learn, in my opinion, and is more cross-skill compatible with other programming languages. Instructors have embraced the new language on Udemy, and amongst the best courses are the ones from tutorial guru Ray Wenderlich, and London-based developer Angela Yu. I intended to use their courses as refreshers, but have learnt a huge amount from both of them.

Android and Kotlin

Kotlin has a similar story to Swift, as a new language positioned to supersede and older one. That old one is Java, which is arguably a lot more useful and widespread than Objective-C. However, Kotlin is remarkably similar to Swift in syntax and usage. As such, it’s a pretty good choice to add to your collection if you are aiming for both iOS and Android development.

There is an old-school Android developer on Udemy, Tim Buchalka, who really knows his stuff. He’s my go-to for all my Android courses, and his Kotlin course is probably the most accessible and practical out there.

Not all hard work!

It’s not all hard work, of course. I take a couple of courses just out of interest or curiosity. As a programmer, I’ve always felt a little inferior about my design and illustration skills. Not only that, but I’m often a little jealous of how in the zone and mindful digital artists can get when working. To that end, I’ve been following a great course on creating digital art on the iPad with the Procreate app. Because not everything has to be about languages, programming or otherwise!