Get the Excel TRANSLATE Practice File
Want the file without building it yourself? Download the ready-to-use practice file and follow along with every method in this tutorial.
- Practice TRANSLATE and DETECTLANGUAGE right away
- See the dynamic dropdown translation example already set up
- Includes sample text in 5 languages ready for translation
Enter your email below for instant access.
How the TRANSLATE Function Works (Syntax + Example)
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 TRANSLATE in Excel (Step-by-Step)
Here is a simple example to get you started. In this scenario, we will translate text in cell A2 from English to Spanish.
Step 1: Type the Function
In your target cell (e.g., B2), begin by typing =TRANSLATE(. This opens the function and prepares Excel to receive your translation arguments.
Step 2: Select the Source Text
Choose the cell that contains the text you want to translate. In our example, click on cell A2. You can also type text directly into the formula if you wrap it in double quotes.
Step 3: Enter the Source Language Code
Type the two-letter language code for the original text, such as “en” for English. If you are unsure of the source language, you can skip this argument and Excel will attempt to detect it automatically.
Note: auto-detection works best with longer text. Specify the source language when you can for more accurate results.
Step 4: Enter the Target Language Code
Type the language code you want the text translated into. For Spanish, you would enter “es”.
If left blank, Excel defaults to your system’s display language.
=TRANSLATE(A2, "en", "es")Step 5: Press Enter to See Your Results
Close the parentheses and press Enter. Your text will be translated immediately using Microsoft Translation Services.
How to Build Dynamic Translations with a Drop-Down Menu
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.
Not sure how to set up the dropdown? See our full guide on creating drop-down lists in Excel for step-by-step instructions.
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.
How to Fix TRANSLATE Errors (#VALUE!, #CONNECT!, Quota)
While these functions are powerful, you might encounter a few common errors. Here is how to solve them:
- VALUE! Error (Text Too Long): There is a limit to how much text Excel can translate in a single cell. If your text is too long, the formula will fail.
- The Fix: Break the text into multiple cells or summarize the content before translating.
- GETTING_DATA (Still loading): This status appears while Excel is fetching the translation from Microsoft’s servers. It usually resolves on its own within a few seconds.
- The Fix: Wait a moment. If it doesn’t resolve, try pressing F9 to force a recalculation.
- CONNECT! (No Internet Connection): These functions use Microsoft Translation Services in the cloud. If you are offline or have a slow connection, you will see this error.
- The Fix: Ensure you have a stable internet connection
- Request Throttled (Quota Reached): Microsoft limits the number of translation requests to prevent abuse. If you’re making a large number of requests in a short time, you may hit this limit.
- The Fix: If you see this error, you have exceeded your quota. Wait a while and try again.
- Invalid language: You have entered an invalid language code or one that is not supported.
- The Fix: Please refer to the list of supported languages.
- NAME? Error: This usually means the function isn’t available in your version of Excel yet.
- The Fix: Double-check the Availability section below to see if your version is supported.
💡 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
How to Use DETECTLANGUAGE in Excel
The DETECTLANGUAGE function is perfect for auditing large, multilingual datasets. Here is how to use it in three simple steps.
Step 1: Enter the DETECTLANGUAGE Function
Select an empty cell and type =DETECTLANGUAGE(
This function only requires one argument: the text you want to analyze.
Step 2: Select the Source Cell
Choose the cell containing the text you want to identify (e.g., cell B2).
You can also type a string of text directly into the formula by wrapping it in double quotes.
Step 3: Press Enter to Get the Language Code
Press Enter. Excel will return the standard ISO language code, such as “en” for English, “es” for Spanish, or “de” for German.
Note: For best results, use this function on longer text. Very short strings, heavy slang, or mixed-language content may produce unreliable results.
Which Excel Versions Support TRANSLATE?
⚠️ “Why don’t I see the TRANSLATE function?”
TRANSLATE and DETECTLANGUAGE are available in Microsoft 365 (desktop and web) and Excel for the Web. Make sure your Microsoft 365 is fully updated.
If you are using Excel 2021, 2019, or older, these functions will not appear — they are only available with an active Microsoft 365 subscription.
Note: Microsoft is still rolling these out, so some users on current versions may not see them yet.
Excel’s TRANSLATE and DETECTLANGUAGE functions make working with multilingual data a lot simpler. They’re easy to set up, support a wide range of languages, and can save significant time when you’re dealing with large datasets.
If you work with text-heavy data and need to clean or extract specific parts, also take a look at Excel’s TEXTBEFORE and TEXTAFTER functions.
They’re built for exactly that: pulling out names, codes, or any text segment without complex formulas.
TRANSLATE is one of several powerful new functions in Microsoft 365. See our overview of new Excel functions including VSTACK and TEXTSPLIT for more.
Next Steps: Master More Modern Text Functions
If you enjoyed using TRANSLATE, you will find TEXTBEFORE and TEXTAFTER equally powerful for cleaning and manipulating your multilingual data.
- Read the Guide: Master TEXTBEFORE and TEXTAFTER in Excel
Download the Free Practice File
Leila’s free practice file
Follow Along in Excel — Get the Free Practice File
Includes TRANSLATE, DETECTLANGUAGE & a dynamic dropdown translation example — pre-built and ready to open
Get the Free FileFree. No spam. Unsubscribe anytime.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
