This article is contributed. See the original author and article here.
Limited additional language support is currently available to 50% of Microsoft 365 Subscribers in the Beta level of the Office Insider program using Excel on Windows or MacOS. Additional language support for Excel on the Web will be coming later.
For a little over a year, English language Stocks and Geography data types have been available to customers who have English as an editing language in their workbooks. We have heard from customers that they’re benefiting from using data types to get current stock prices and demographic information into Excel. We are excited to bring these capabilities to a broader audience by supporting Stocks and Geography in French, German, Italian, Portuguese, and Spanish!
You will have access to any data type that is supported by one or more languages chosen in your authoring language settings. For English, Spanish, French, German, Italian, and/or Portuguese this includes both Stocks and Geography.
You can ensure data types will be created in the language you want by setting it as your preferred authoring language. You can read more in this support article about language settings in Office.
- For Office on Windows, you can change this setting in File > Options > Language. Under Office authoring languages and proofing select the language you want and then select Set as Preferred.
- For Office on MacOS, you can configure this by going to the Apple menu, choosing System Preferences > Language & Region and set the desired language to be your preferred language by dragging it to the top of the Preferred languages list.
NOTE: You may have to restart Excel twice after making a change to your language settings to ensure the data type service registers the change.
Converting to a Data Type
After you have configured your language settings, start by typing some text in a cell like the name of a city or country for geographic data, or ticker symbol for stock data. Read more about Stocks and Geography data types. You should type your text in the language you will be using for the data type.
Using the Data Tab
Select your cells containing text and go to the Data tab in the ribbon. You should see two buttons in the Data Types group. Select the button corresponding to the data type you want to create. Note that the text for these buttons will match your Office display language (for the screenshot below this is English).
When authoring in a supported language, the Stocks and Geography buttons will look like the above.
If you configure your settings so that your preferred authoring language is not supported for a data type, but a supported language is included in the list, the data types created will fall back to using one of the supported languages. You can see an example of this in the button-like UI in the screenshot below. The language name in parentheses helps you to know the data types will be created in Spanish. (In this case the Display Language is set to English, while Russian is currently set as the preferred language and Spanish is included in the list)
Exploring the data type
After you convert text into the Stocks or Geography data types, an icon will appear in the cell. Click the icon to see the card. The card reveals a list of fields and corresponding values. Depending on the data, there could be numerous field/value pairs that you can see and work with. You will be able to see that the field names and values are created in the configured language.
Data types persist in the language that they were created in. Changing your language settings and performing a data connection refresh of the data will not change the display name, field name, or field values in the data type.
Using fields in formulas
Fields like “Nombre”, “Precio”, and “Población” are the buckets of information that make up the data type. When a data type is created in a certain language, it means that all the field names and field values for that data type are forever in that language.
You can use these field names as identifiers in formulas and calculations using dot notation. For example, the formula “=A1.Población” uses dot notation after referencing a cell A1 then using a period followed by the field name. This will result in the formula evaluating, and the field value being shown in the cell.
Another way to insert a field into the workbook is by using the “Insert Data” button on the card, on a cell, or next to a table.
Remember, the data type persists in language it was created in. If you change your language settings or send your workbook to someone working in a different language, the field names and formulas using dot notation will not change.
Additional language support for Stocks and Geography in Spanish, French, German, Italian, and Portuguese is available to 50% of Microsoft 365 Subscribers in the Beta level and will be available to users of other channels later this year. I will update this blog as these become more widely available.
In the meantime, please provide feedback either in the comments below or Please note our translations and coverage of data may not be perfect, please let us know how we can improve.
Stay engaged with the Excel product team and the Excel community:
- Join our Excel Community
- Send us ideas and suggestions via UserVoice
- Subscribe to the Excel Blog
- Follow Excel on social (@msexcel, /microsoftexcel)
Kaycee Reineke (@KayceeSue)
Program Manager, Excel
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.