Switch Languages in Excel Workbook

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



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.


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

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.


8 thoughts on “Switch Languages in Excel Workbook”

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

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


  3. 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:
    should be:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.