Adding BI data source to Excel, change language of Pivot fields

Question

Hi,

I am using Excel in English, which isn't my native language (German), but I find it makes troubleshooting easier.

Unfortunately, this also changes the language of some PivotTable Fields when I load in a new Analysis Service database to English, resulting in a weird mix of languages which makes comparing data harder than it needs to be. The only workaround I found is switching languages, load in the data source, and switch back. Obviously, that's quite annoying and time consuming.

Is there a way to keep Excel in English, or at least keep the formulas in English, and disable the translation of those values?

Kind regards!

Answer 1

To maintain Excel in English while working with an Analysis Services database, you can connect to the Tabular model using a locale identifier. When you connect to the model in Excel, you can choose the culture for which string translations exist in your model. This allows you to see the translated table and column names without changing the overall language settings of Excel.

However, Excel's internal handling of worksheet function names is recommended to be done using only English function names to avoid any language-related issues. This approach may help you keep the formulas in English while still being able to work with your data effectively.

If you are experiencing mixed languages in your PivotTable fields, ensure that the locale settings in your Excel connection are correctly configured to match the desired language for your data without switching the entire Excel interface.

---

References:

• Translations in tabular models
• Globalization and localization of Excel solutions

Answer 2

Dear @L, 

Good day! Welcome to Microsoft Q&A forum! 

Based on your description, I understand that you're using Excel in English for easier troubleshooting, but when you connect to an Analysis Services data source, some PivotTable fields switch to English as well. This creates an inconvenient mix of languages and makes comparing data harder. You're looking for a way to keep Excel (or at least formulas) in English without having the PivotTable field names automatically translated.

I sincerely apologize for the inconvenience caused by this issue. Regarding your situation, at the moment, Excel automatically localizes PivotTable field names based on the display language. Because of this, the most convenient way to keep your model’s German field names while using Excel in English is the workflow you are already using, which is temporarily switching Excel to German before loading the data source, then switching back to English afterward.

In addition, since this behavior depends on how Excel handles metadata localization, other community experts may have found creative approaches or tools that could help. You could post your question on StackOverflow, where many BI and Excel specialists actively discuss scenarios involving Analysis Services models and language handling. You may discover alternative ideas or workflows from the broader expert community.

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

If you have any other questions or need further assistance, I'm here and happy to help.

I appreciate your time and look forward to hearing how things are going! 

Thank you for your time and patience.

---

If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.