If you work for an international company, you might need to communicate in various languages. If you are not familiar with a language, this can be challenging. Fortunately, in Google Sheets, the Google Translate function lets users translate text within cells or ranges of cells without leaving the worksheet.
It’s a built-in Google Formula, so you don’t need a browser extension or Google Sheets add-on. Here’s how to use a Google Translate formula in your worksheets to translate text.
The Formula for Google Translate
Take a look at the syntax and arguments of the Google Translate function.
=GOOGLETRANSLATE(text, [source_language, target_language])The Google Translate formula contains three arguments, as shown above. The first one is mandatory, while the other two are optional.
- Text: The argument refers to the text you want to translate. You can either place the text directly in quotation marks in the formula or refer to the cell in Google Sheets containing the text.
- Source_Language: This is an optional argument that consists of two words code. It refers to the language in which the text currently is. Google Sheet is smart enough to understand the source language on its own if you set this argument as auto. You should, however, specify the source language if you know it. Otherwise, there is no harm in setting it at auto.
- Target_Language: This is also an optional argument that refers to the language you want the text to be translated into. You should specify the argument if you intend to translate text in a language other than your default language. If this option is set to auto, the Google Sheet will translate text into the language set on your PC by default.
Although the last two options are optional, it’s good practice to specify them to avoid any errors or complexities. If you are not familiar with any of the source or target languages, you can set the arguments as auto.
However, if you don’t know the abbreviations for languages you are translating, you can check them out from the Wikipedia list of language codes.
Before the implementation phase, you should first copy any language code that you plan to either convert from or to. This will save you time when defining arguments in the function.
Among the language codes, English is “en”, German is “de”, and Italian is “it”. Note down the language codes, and let’s put the Google Translate function into action.
Translating the Text in Google Sheets
In the following four cells, A2 to A5, you can see samples of English text which we want to translate from English to German.
1. Go to Cell B2.
2. Add in the Google Translate function.
=GOOGLETRANSLATE(text, [source_language, target_language])
3. Reference cell A2 as the first text argument.
4. Set source language to be “en”.
5. Set target language to be “de”.
6. Press Enter to execute the formula.
As you can see, the English text has been translated into German. Using the auto-filling sequence function, drag down to translate the text in other cells down the row.
If you forget the function arguments, you can tap the question mark to see the formula help.
The formula help will open up the function syntax and an example showing formula implementation that you can use as a reference.
Now let’s implement the Google Translate formula by setting either the source language or target language arguments to auto and keeping both arguments as auto in another.
Making Google Translate Formula Auto-Configurable
Let’s convert the German language back into English by keeping the source language auto.
1. Go to Cell D2.
2. Set cell B2 as a reference for text argument.
3. Add “auto” as the source_language argument.
4. Set target language argument to be “en”.
5. Press Enter to execute the formula.
Google Sheets has smartly picked up the source language and converted it into our target language.
After being translated into German, the original text has been translated back into English slightly differently. Even though the text may not translate with exact synonyms, it’ll make sense in any language.
Populate the other cells down the row.
Let’s set both arguments to auto to see how it affects the final output.
With Both Language Arguments Set to Auto
Let’s convert the text to the computer’s default language by leaving both source and target language arguments set to auto.
1. Add Google Translate formula in cell E2.
2. Choose cell C2 as a reference.
3. Set both language arguments as auto.
4. Press Enter.
In this case, Google Sheets has automatically translated the text from its source language into English. As a default language on our computer, English is the target language.
If you set a default language other than English on your computer, Google Sheets will use that as the target language.
Translate Google Sheets With Google Translate Function
Translating text in Google Sheets is made easy with the help of Google Translate. By using this function, you can translate worksheets without the use of third-party programs.
Google Translate’s direct access to the Google database significantly reduces the chances of error.
