This guide will discuss how to use the GOOGLETRANSLATE function in Google Sheets.
When we need to translate text values to another language, we can easily do this using the GOOGLETRANSLATE function in Google Sheets.
Table of Contents
The rules for using the GOOGLETRANSLATE function in Google Sheets are the following:
- The
GOOGLETRANSLATEfunction is used to translate text from one language into another chosen language. - The text argument must be enclosed in quotation marks or be a cell reference containing the appropriate text.
- When we leave the source_language argument blank, the function will auto-detect the language by default.
- If the source_language argument is omitted, the target_language argument must also be omitted.
Google Sheets is an excellent tool for collaborating with people from different places. However, the language barrier can be a common issue in such situations.
Fortunately, we can utilize the GOOGLETRANSLATE function to solve the issue of the language barrier in collaborative work. This will make it easier for us to collaborate using different languages.
The GOOGLETRANSLATE function uses the Translate service offered by Google for around 100 languages. Thus, an internet connection is needed for the function to work.
In this guide, we will provide a step-by-step tutorial on how to use the GOOGLETRANSLATE function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.
Great! Let’s dive right in.
The Anatomy of the GOOGLETRANSLATE Function
The syntax or the way we write the GOOGLETRANSLATE function is as follows:
=GOOGLETRANSLATE(text,[source_language],[target_langauge])
- = the equal sign is how we start any function in Google Sheets.
- GOOGLETRANSLATE() refers to our
GOOGLETRANSLATEfunction. This function allows us to translate text from one language to another using Google Translate. - text is the only required argument. This refers to the text we want to translate. Furthermore, this can be a text string enclosed in quotation marks or a cell reference containing the text string.
- source_languge is an optional argument. This refers to the language code of the source text. If omitted, Google Translate will try to detect the source language automatically by default.
- target_language is another optional argument. This is the language code of the target language we want to translate the text into. When omitted, the default target language is English or the system language the current spreadsheet is using.
Optional Arguments of the GOOGLETRANSLATE Function
There are two optional arguments within the GOOGLETRANSLATE function that makes it more efficient to use. Both arguments utilize language codes to function.
Language codes are codes that assign letters as classifiers for languages. For example, the language code for English is en.
Firstly, the source_language argument is referring to the language code of the text we want to translate. By default, the function will use auto-detection to identify the language.
However, this increases the possibility of error in identifying the language. It is much better to input the language ourselves and only use auto-detection when we do not know the source language.
Secondly, the target_langauge argument allows us to select the language we want to translate the text to. By default, the function will translate the text into English.
Otherwise, it will translate to the language you are using in your spreadsheet.
The table below is a list of common language codes. You can also refer to this list to learn more language codes.
| Language | Code |
|---|---|
| English (UK) | en-GB |
| Portuguese (Brazil) | pt=BR |
| English (US) | en |
| Filipino | fil |
| French | fr |
| German | de |
| Japanese | ja |
| Korean | ko |
| Chinese (PRC) | zh-CN |
| Thai | th |
A Real Example of Using GOOGLETRANSLATE Function in Google Sheets
Let’s say we have a collaborative data set containing several texts in different languages. Our initial data set would look like this:
In the spreadsheet above, we can see text strings in different languages. We want to translate the text strings into English for everyone to understand. In this case, we can easily do this using the GOOGLETRANSLATE function.
The basic formula to perform this task is:
=GOOGLETRANSLATE(A2)
The formula above is only using the required text argument, which is cell A2. Since we omitted the source_langauge argument, the function auto-detected the language of the selected text.
Afterward, the function translated the text into English since our spreadsheet system is set to English.
The data set using the basic formula would look like this:
For instance, we want to translate the text strings into another language, not English. We can do this by using the optional arguments.
The formula we will use is:
=GOOGLETRANSLATE(A2,"ko","ja")
The first part of the formula is referring to the text argument. Next, we identified the language of the text strings as Korean. This means we will input the language code “ko” as our source_language argument.
Lastly, we want to translate the text strings into Japanese. Hence, our target_langauge argument in the formula is “ja”.
Our final data set would look like this:
You can make your own copy of the spreadsheet above using the link below.
Amazing! Now we can dive into the steps of using the GOOGLETRANSLATE function in Google Sheets.
How to Use GOOGLETRANSLATE Function in Google Sheets
1. First, we will create another column in the data set to display the result.
2. Let’s first use the most basic formula. To begin, we will type an equal sign and the function name, giving us the formula “=GOOGLETRANSLATE(”.
3. Then, we will simply select the cell containing the text. Our formula would be “=GOOGLETRANSLATE(A2)”.
4. Press the Enter key to return the result.
5. Drag down the Fill Handle tool to apply the formula to the rest of the cells.
6. Let’s translate the text to another language, not English. To start, we will use the same basic formula “=GOOGLETRANSLATE(A2”.
7. Then, we will utilize the optional argument. Firstly, we will input the source language of the text we want to translate. In this case, the text string is in Korean. Our formula would be “=GOOGLETRANSLATE(A2,”ko””.
8. We want to translate the text strings from Korean to Japanese. So we will type in the language code of Japanese, which is ja. Our final formula would be “=GOOGLETRANSLATE(A2,”ko”,”ja”)”.
9. Press the Enter key to return the result.
10. Lastly, we will drag down the Fill Handle tool to copy the formula.
And tada! We have successfully used the GOOGLETRANSLATE function in Google Sheets.
You can apply this guide whenever you need to translate text strings into another language. You can now use the GOOGLETRANSLATE function and the various other Google Sheets formulas available to create great worksheets that work for you.
FAQs:
1. How can I find the language code of an unfamiliar language?
If you do not feel confident using the auto-detection of the GOOGLETRANSLATE function, you can also utilize the DETECTLANGUAGE function.
This function will identify the language of the given text and return the language code.
2. What are the common errors in using the GOOGLETRANSLATE function?
The most common mistake is using incorrect language codes. Make sure to use the correct two-letter language codes.
Another common mistake is not enclosing a given text string or language code in quotation marks. When entering text directly into the formula, enclose it in quotation marks.
That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
