Switch Languages in Excel Workbook

Our two official languages in Canada are English and French, and I’m currently working on an Excel project in which you can choose either language.

The data in my client’s file is confidential, so I’ll show you how this works using some text from a Honey Nut Cheerios box. Actually, this is how many of us learned some French – reading the back of the cereal box at the breakfast table!

Set up the Translation Table

In the sample Excel file, I set up a table with 3 columns – ID, English and French. I entered a few English and French phrases from the cereal box, and typed numbers for each row in column 1.

englishfrench01

List the Languages

On the same sheet, I created a table with languages, and an ID number for each language. I selected cells G2:G3, with the language names, and named that range LangList.

englishfrench03

Make a Language Selector

On the main worksheet, I created a data validation drop down list, based on the LangList range. Next, I named that cell – LangSel, and selected English as the language.

englishfrench02

Get the Language ID

Back on the translation sheet, I added a formula to calculate the ID of the selected language. Here is the formula, in cell I2, which is named LangSelID:

=INDEX(tblLang[LangID],MATCH(LangSel,tblLang[Lang],0))

englishfrench04

When English is selected, the result is 1, and 2 for French.

Show the Selected Language

On the main sheet, I typed the numbers 1 to 5 in cells A4:A8

In cell B4, I entered an INDEX/MATCH formula, to pull the correct text from the translation table.

=INDEX(tblTrans[[English]:[French]],MATCH(A4,tblTrans[ID],0),LangSelID)

The INDEX function looks for a value in the English and French columns of the translation table. For the row, MATCH finds the number typed in column A, in the ID column of the translation table. For the column, it uses the LangSelID.

englishfrench05

Change the Language

To see the French version of the text, select French in the LangSel drop down list. Now the LangSelID result is 2, so the text from the 2nd column of the English/French range is shown.

englishfrench06

Link Shapes to Cells

In my client’s file, there are also a few flow charts, and I’ve linked those shapes to worksheet cells, so they can show text in the selected language too.

In the example shown below, the text box at the top of the graphic is linked to cell B4. There are two other text boxes, linked to cells B5 and B8. When French is selected, the diagram shows the text in that language.

englishfrench07

Change the selected language to English, and the worksheet cells and the cereal box graphic also switch to English.

englishfrench08

Download the Sample File

To see how the translation formulas work, you can download the sample file from my Contextures website. On the Sample Files page, go to the Functions section, and look for FN0029 – Show Text in Selected Language.

The zipped file is in xlsx format, and there are no macros in the file.

_________________

2 comments to Switch Languages in Excel Workbook

  • Maxim Manuel

    C'est vraiment genial ces astuces Debra. Merci.
    Your tips arw awesome Debra. Thanks.

  • Hugo

    Hi Debra, I'd like to have your great solution enhanced.
    Wouldn't it be fine, that Excel will look for the right language by itself?
    My idea is:
    1. Use a formula to get a text, that's only available in this language currently used (e.g. weekday name).
    2. Build a lookup table containing this text in different languages
    3. Continue with your approach

    In other words: which formula determines the current used language (no VBA)?

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>