Multilingual Excel Models: Language Specific Formatting
In our consulting practice, we have seen a steady rise in the number of companies implementing standardized template models across their organization. We have had a hand in building many of them.
Many of these models will be used within multiple regions with users who don’t all speak the same language. Instead of having multiple models, our clients have instead opted for multilingual models.
In addition to the usual model design planning, multilingual models require some additional design consideration.
The most obvious consideration is that the labels need to be in different languages. The simplest solution is to include all translations in the active label. For example, “Sales” would become “Sales / Ventes / Der Umsatz / Ventas”. This method can work if the terms are short and there are only a few language requirements. It also works well for the labels that appear in other parts of Excel, such as Data Validation and VBA alerts, like MsgBox or InputBox.
However, we find that models present better if they appear in only one language at a time. This means that instead of text, the labels need to be formulas that select the label in an appropriate language based on a user input switch.
The switch is created simply using a Combo Box or Data Validation. This would then drive a CHOOSE function which would display the correct version of the label. A list of labels are required to ensure that translation happens properly. These lists can be stored in a few possible ways:
- In a hidden group of columns somewhere else on the sheet, or
- In a centralized tab in the model, or
- Within the CHOOSE formula itself.
Different Operating Systems
Labels are all you really need to worry about if users are simply reading the model in multiple languages, but what happens if the users’ operating systems utilizes one of the other 108 Windows display languages?
In non-English versions of Excel, certain function names are in the local language. For example, TODAY() becomes AUJOURDHUI() in French Excel. Function names should change automatically, however, there are a few other situations that need to be considered:
- Custom Number Formatting, and
- TEXT function, format_text argument.
Consider the following situation: You need to extract data from a database using a keyword comprised of a category and a date (i.e., Sales20200731. The user can search the database by selecting a category and entering a date. The model then concatenates the inputs and uses a lookup function to search the database. The keyword is recreated using a simple formula.
However, this won’t work for your colleague in France because their computer can’t understand the formatting instructions. In French Excel, to format the year, the keyword is “a” for ans, not “y” for year.
In this scenario, the formatting must be adjusted to specify the language by adding a Local ID (LCID). The revised formula would be:
This will allow your model to continue functioning as intended regardless of a user’s language. Microsoft has over 220 LCIDs.
Multilingual models require some additional planning, but in all respects should continue to follow industry best practices. To learn more, check out our website for information about upcoming training sessions from The Marquee Group.