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 steps below show how to set things up, so you can switch languages in Excel.
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.
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.
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.
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))
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.
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.
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.
Change the selected language to English, and the worksheet cells and the cereal box graphic also switch to English.
Download the Sample File
Original 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.
Update: I’ve uploaded another sample file, which has a few more translation features. On the Sample Files page, go to the Functions section, and look for FN0061 – Show Text and Buttons in Selected Language. The zipped file is in xlsm format, and the file contains macros.
_________________
C’est vraiment genial ces astuces Debra. Merci.
Your tips arw awesome Debra. Thanks.
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)?
please send me one excel workout sheet
Hi Debra,
Fantastic solution to language translation for Excel. It will be excellent for a project I am working on.
But do you know of a way of also translating the labels in buttons sitting on a worksheet?
Or of comments associated with cells? These, I fear, would still be in English.
Thanks
Charlie
Thanks Charlie, and I’ve uploaded another sample file, which has a few more translation features. On the Sample Files page, go to the Functions section, and look for FN0061 – Show Text and Buttons in Selected Language. The zipped file is in xlsm format, and the file contains macros.
Thank you so much. This is amazing! Works like a charm.
There are a few small mistakes in the text on this page that had me puzzled for a little while, though:
=INDEX(tblTrans[[English]:[French]],MATCH(A4,tblTrans[ID],0),LangSelID)
should be:
=INDEX(tblTrans[[English]:[French]];MATCH(A4;tblTrans[ID];0);LangSelID)
All of my formulas have a comma separator.
You can change those to semi-colons, if that’s what’s in your Regional Settings.
late comment, so not sure if it will be ever read.
I am using this technique already for long time but now i come into a problem: i would like to use multilingual data validation.
to show and select the values, this works. The problem is that after saving, it is possible that somebody logs on in a different language and i would like to show the selected values in the correct language
and this is something that i cannot work out.
Any suggestions from anyone?