Download the practice workbook 👉 HERE and follow along:
Excel added a new function. It automatically translates a text in your spreadsheet from one language to another. Let’s explore how to use these functions with simple steps and examples.
What is the TRANSLATE Function in Excel?
The TRANSLATE function in Excel helps you translate text from one language to another.
It supports over 100 languages. You can find the supported languages and their respective language codes 👉 here.
Syntax
=TRANSLATE(text, [source language], [target language])- text: The text you want to translate. You can either type it directly in quotes or reference a cell with the text.
- source_language (optional): The language code of the text you’re translating from (e.g., “en” for English). If you don’t specify this, Excel will try to detect the language automatically.
- target_language (optional): The language code you want to translate the text into (e.g., “es” for Spanish). If you don’t specify this, Excel will use your system’s language settings.
How to Use the TRANSLATE Function
Here’s a simple example to get you started:
- Type the Function: In cell A5, type
=TRANSLATE( - Select the Text: Choose the cell that has the text you want to translate, which is A2 in our example.
- Enter the Source Language: Type the language code of the text’s language, like “en” for English. You can skip this if you want Excel to detect the language automatically.
- Enter the Target Language: Type the language code you want the text translated into, like “es” for Spanish.
- Press Enter: Your text will be translated immediately.
=TRANSLATE(A2, "en", "es")
Making Translations Dynamic
You can also make your translations dynamic. For instance, create a dropdown menu to let users choose the language:
- Create a Dropdown: Go to Data > Data Validation, choose List, and enter language codes like “en, de, es, fr”.
- Link the Dropdown to TRANSLATE: Use the dropdown cell in your TRANSLATE function.
Example
If your dropdown is in cell H2 and your text in English is in column V, use this formula to translate dynamically:
=TRANSLATE(V5, "en", $H$2)💡 The dollar signs ($) are necessary to lock the reference to the dropdown in cell H2. This way, when you drag the formula down, the reference to H2 stays the same while the V5 reference changes.
This way, changing the dropdown value in H2 will automatically update the translations for all the cells where you apply this formula.
Auto-Detecting Languages
If you’re not sure of the source language, Excel can detect it for you. Just leave out the source language argument:
=TRANSLATE(V5, , $H$2)
Excel will figure out the language of the text in V5 and translate it to the language specified in H2.
Troubleshooting Common Errors
- Text too long: You have too many characters in a cell. Reduce your cell size and try again.
- Error in value: You have a non-text value in your cell. The function only accepts a text argument.
- Invalid language: You have entered an invalid language code or one not supported. Please refer to the list of supported languages.
- Request throttled: You have exceeded your daily quota for the translation function.
💡 The TRANSLATE function requires an internet connection because it uses Microsoft Translation Services to perform the translations.
Featured Course
Master Excel’s Essential Modern Functions
DETECTLANGUAGE Function in Excel
The DETECTLANGUAGE function helps you identify the language of a given text. This can be useful when you are dealing with multilingual data.
Syntax
The syntax for the DETECTLANGUAGE function is straightforward:
=DETECTLANGUAGE(text)- text: The text whose language you want to detect. You can either type it directly in quotes or reference a cell with the text.
How to Use the DETECTLANGUAGE Function
Here’s a simple example:
- Type the Function: In a cell, type
=DETECTLANGUAGE( - Select the Text: Choose the cell with the text you want to detect the language for, such as B2.
- Press Enter: The function will return the language code of the text.
This will return the language code like “en” for English or “de” for German.
Availability
These functions are currently available to Beta Channel users running:
- Windows: Version 2407 (Build 16.0.17808.20000) or later
- Mac: 16.87 (Build 24062430) or later
Excel’s TRANSLATE and DETECTLANGUAGE functions are essential tools for handling multilingual data. They are easy to use, support a wide range of languages, and ensure accurate communication. If you need to translate text or detect a language in your data, these functions can boost your workflow.
Also, Excel’s TEXTBEFORE and TEXTAFTER functions simplify text extraction tasks. They make it easy to clean and manipulate your data. It’s perfect for quickly separating names, codes, and other text segments. This saves you time and improves accuracy.
Try these functions today and experience the difference they can make. For more tips and tricks, check out our guide on TEXTBEFORE and TEXTAFTER functions.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel TRANSLATE and DETECTLANGUAGE functions with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
