Have you ever needed to translate text from one language to another while working in Excel?
Maybe you’re creating reports for international clients or generating invoices in multiple languages.
Good news – Microsoft Excel now has a built-in TRANSLATE function that can help you do this quickly and easily.
Excel is typically used for data analysis and number crunching, so translation might seem like an odd feature to include. But it’s actually quite handy when you need quick translations without leaving your spreadsheet.
TRANSLATE is a new Excel function, so you’ll need Excel with Microsoft 365 or Excel on the web to use it. If you’re using older versions, this function won’t be available.
Syntax of TRANSLATE Function in Excel
The TRANSLATE function is straightforward and uses three arguments:
=TRANSLATE(text, [source_language], [target_language])Here’s what each part means:
- Text: The text you want to translate. You can use a cell reference or type text directly in quotes.
- Source Language (optional): The original language of your text. While Excel can usually detect this automatically, I recommend specifying it for better accuracy.
- Target Language (optional): The language you want to translate into. If you do not specify this argument, you would get the same text you used as the first argument.
All language codes must be two-letter codes in double quotes. For example, “en” for English, “es” for Spanish, or “fr” for French. Here is the official documentation from Microsoft that lists all the supported languages.
Note that Google Sheets also has a similar function called GOOGLETRANSLATE that has the same syntax.
Now let’s look at some examples of using the Translate function in Excel.
Example 1: English to Spanish Translation in Excel
Let’s say you have English text in cell A1 that you want to translate to Spanish.
Your formula would look like this:
=TRANSLATE(A2, "en", "es")
When you press Enter, Excel instantly translates your text into Spanish.
You can then copy this formula down to translate multiple cells at once.
Even though I don’t speak Spanish fluently, I checked with my Spanish-speaking friends, and they said the translation was as good as it gets.
Example 2: Automatically Detect Language and then Translate in Excel
Let’s say you’re working with a dataset that has multiple different languages (e.g., English, Spanish, Portuguese, and Dutch) and you want to translate all of them with one formula to English.
This can be done using the new DETECTLANGUAGE function along with the TRANSLATE functoin.
Below I have a dataset where I have text in different languages in column A, and I want to translate everything into English and get the result in column B.
Below is the formula that will do this.
=TRANSLATE(A2,DETECTLANGUAGE(A2),"en")
In the above formula, I have used the DETECTLANGUAGE function as the second argument that automatically detects the language and returns the two-letter code for that language. And since the third argument is “en”, whatever language is detected is converted into English.
Important note: While the translations are generally accurate, don’t rely on them completely for critical documents. Always review the results to ensure accuracy and proper context.
Limitations of the Excel TRANSLATE Function
Before you start using TRANSLATE extensively, there are some important things to know.
Internet Connection Required
The TRANSLATE function needs an active internet connection to work. The translation happens online, so you can’t use this feature offline.
3,000 Character Limit
Through testing, I discovered there’s a 3,000 character limit per translation. If your text is longer than this, you’ll get a #VALUE! error.
I tested this with dummy text and confirmed that anything under 3,000 characters works fine. But add even a few more characters, and the function fails (I have shown the test in the video above).
Common Error Scenarios
You might encounter errors in these situations:
- Non-text values: The function only works with text. Numbers or other data types will cause errors.
- Invalid language codes: Using incorrect two-letter language codes will result in errors.
- Request throttling: Microsoft doesn’t specify the exact limits, but there’s likely a daily quota on translations. You can’t run unlimited translations.
When to Use Excel’s TRANSLATE Function
Excel’s TRANSLATE function is perfect for quick translations within your existing workflows. It’s great for:
- Translating column headers for international reports
- Creating multilingual invoices or forms
- Quick translation checks while analyzing data
However, Excel isn’t a dedicated translation tool. For long documents or professional translations, you’re better off using specialized translation software.
I hope you found this Excel tutorial helpful. If you have any questions or suggestions, do let me know in the comments area.
Other Excel articles you may also like:
