Google Sheets is a great spreadsheet web application that provides an excellent feature set at no cost for individuals and small businesses. Among the main reasons people are switching from other software such as Excel is Google Sheets’ collaborative nature.
Due to its handy sharing tools, many people use it for group projects. Yet, there is often a language barrier when users speak different languages. For these individuals, the Google Translate function can be a lifesaver.
In this article, we will discuss the Google Translate function and how you can use it in Google Sheets. Read on to master this function.
Table of Contents
What Is the Google Sheets Translate Function?
The Google Translate function in Google Sheets allows you to translate text in your spreadsheets from one language to another. It uses the Translate service offered by Google for around 100 languages.
The function can be put into any cell and given a cell address or a phrase. You must also specify the source and the target languages in the formula to work correctly.
Although Google Translate is accurate for the most part, we still recommend you use simple phrases and easier words, as the function can often translate complex text inaccurately.
The Formula for Google Sheet Translate
Here is the Google Sheets translate formula:
=GOOGLETRANSLATE(text, [source_language, target_language])
The formula uses three arguments that include:
- Text
- Source-language
- Target-language
The latter two are optional. Only the text argument is needed for the formula to work correctly. Here is a brief summary of what each argument does:
Text: This argument specifies the text you wish to translate. Either you can write the text directly in quotation marks or specify the cell address.
Source_Language: This is an optional argument that allows you to select the language of the text that needs to be translated.
Target_Language: This is also an optional argument that allows you to select the language you want to translate the text to.
Pros and Cons of Using Google Sheets Translate
Google Sheets translate can be used for many reasons, such as building a word library. However, there are pros and cons to using Google Translate in Google Sheets.
Pros
- There are over 100 languages available.
- It is free to use.
- It is flexible when using it on Google Sheets.
Cons
- It is not the most accurate when translating long phrases and idioms.
Translate Text In Google Sheets
To demonstrate the function in Google Sheets, here is a sample spreadsheet containing a few English sentences. Our goal is to use the Google Sheets translate formula to translate this text from English to German and Japanese.
To do this, first, let’s translate the text into German. The steps you need to follow are:
Step 1: To translate the text, go to the cell you wish to use to display the translated text and write the Google Translate formula. Ensure that the language code is in quotation marks, or the formula will show an error. In this case, the formula looks like this:
=GOOGLETRANSLATE(A2, “en”, “de”)
Step 2: Press Enter to execute the formula. This may take a few seconds, depending on your internet connection.
Step 3: In this example, we have multiple rows containing the text we need to translate. Instead of writing down the formula in every cell, we will use the Google Sheets autofill feature. To use this, click on the cell and drag down the blue dot at the bottom right part of the cell to cover all the desired cells.
Next in the Google Translate spreadsheet, we will follow the same steps mentioned above to convert the text to Japanese, only changing the target language.
Using Auto as a Language Argument to Translate in Google Sheets
Sometimes, you may be unsure of the language used in the spreadsheets. When that happens, you can use “Auto” as a language argument, and the formula will detect the language in the cell. To do this, follow these steps:
Step 1: Follow the steps we used to translate the text in the above examples. Specify the cell address, but instead of typing the code for the language in the source_language, we will write “Auto.” This prompts Google Sheets to detect the language in the specified cell.
Step 2: Click on Enter to execute the formula. You can use the autofill feature to fill in the empty cells.
If you collaborate with multiple users whose language isn’t the same as yours, you can select the target_language to “Auto.” This will translate the text to the default language of your computer. To do this, follow these steps:
Step 1: Input the formula in the cell and write the cell from which you want to get the text. In the target_language argument, type “Auto” to translate the language to the default language of the user’s computer. In this case, our computer’s default language was English.
Step 2: Press Enter to execute the formula.
A simple trick you can do using the formula is to always set both source_language and target_language arguments to “Auto.” This makes it so that the formula automatically detects the language and changes it to your computer’s default language.
This feature can be convenient if you have a large spreadsheet with multiple users who speak different languages.
Note: If you input the formula in a cell and you get #VALUE! That means that the target cell is empty. Make sure to check that you have the correct target cell. If the cell is empty on purpose or you have skipped cells in between your data, you can remove the #VALUE! by using the IF function for example:
=IF(A2=””,,GOOGLETRANSLATE(A2))
This means that if the specified cell is empty then the function should not do anything. Suppose there are any other errors or problems you are having difficulty with. In that case, you can check the Google Translate community for quick solutions or post your concern for others in the community to help you solve it.
How Can I Find the Language Codes?
One problem that many users may face when using the Google Translate formula in Google Sheets is not using the correct language codes. All the supported languages have their own specific code that you input to translate. This can cause frustration among many, but there is a simple fix for this.
To detect the language in a cell or a range, you can use the Detect Language formula in Google Sheets. The syntax for this formula is:
=DETECTLANGUAGE(text_or_range)
To use the formula in sheets, select an empty cell and type the formula in it. This is the same code that you use in the Google Translate formula. In the text_or_range, simply type in the cell address of the cell whose language you wish to detect. The language’s code will be displayed as the output.
Alternatively, you could use the following codes:
| Language Name | Language Code |
| Afrikaans | af |
| Albanian | sq |
| Arabic | ar |
| Azerbaijani | az |
| Basque | eu |
| Belarusian | be |
| Bengali | bn |
| Bulgarian | bg |
| Catalan | ca |
| Chinese Simplified | zh-CN |
| Chinese Traditional | zh-TW |
| Croatian | hr |
| Czech | cs |
| Danish | da |
| Dutch | nl |
| English | en |
| Esperanto | eo |
| Estonian | et |
| Filipino | tl |
| Finnish | fi |
| French | fr |
| Galician | gl |
| Georgian | ka |
| German | de |
| Greek | el |
| Gujarati | gu |
| Haitian Creole | ht |
| Hebrew | iw |
| Hindi | hi |
| Hungarian | hu |
| Icelandic | is |
| Indonesian | id |
| Irish | ga |
| Italian | it |
| Japanese | ja |
| Kannada | kn |
| Korean | ko |
| Latin | la |
| Latvian | lv |
| Lithuanian | lt |
| Macedonian | mk |
| Malay | ms |
| Maltese | mt |
| Norwegian | no |
| Persian | fa |
| Polish | pl |
| Portuguese | pt |
| Romanian | ro |
| Russian | ru |
| Serbian | sr |
| Slovak | sk |
| Slovenian | sl |
| Spanish | es |
| Swahili | sw |
| Swedish | sv |
| Tamil | ta |
| Telugu | te |
| Thai | th |
| Turkish | tr |
| Ukrainian | uk |
| Urdu | ur |
| Vietnamese | vi |
| Welsh | cy |
| Yiddish | yi |
How to Translate in Google Sheets with Add ons
Other than the Google Translate function, you can also use Google Sheets Add on to translate in Google Sheets. Google Sheets Add ons are like extensions or third party apps that come with special functions you can use on Google Sheets. A good add-on you can use to translate in Google Sheets is the Translate My Sheet add on.
This add-on uses the Google Sheets translate engine and can translate all or even just a selected portion of your spreadsheet into over 100 languages in just one click.
To use add on
- Go to Extensions on the toolbar.
- Select Add-ons and Click get Add-ons
- Search for Translate my sheets in the search bar
- Now all that’s left is to install the add-on.
You might be asked to give it permission if you are not using the chrome browser in which case you click continue.
- To use the add-on, go to Extensions > Add-ons and select View document add-ons.
- Your add-on window will pop-up on the right, and you can click use. From there, you can set your source language and target language. The add-on can also automatically detect the source language for you.
You can translate an individual cell or the whole sheet and you can even duplicate the sheet before you translate. If you choose to duplicate the sheet, your translated text will appear in a new Google Sheets window.
You can also add a background color automatically to the translated cells to separate them from the others.
Unlike with Google Translate, the Translate my sheet add-on will translate the text in its cell and not an adjacent cell. It does not have the option to translate selected words in a cell, it can only translate the entire text. The only other option is to translate in a duplicate sheet.
How to Use Google Sheets Translate in Data Analysis
Let’s say you have a website, online store, or a channel, and you want to analyze the comment section for marketing reasons. Sometimes the comments can be in multiple languages, in which case Google Sheets translate will come in handy.
Using the DETECTLANGUAGE function, you can create a column for the response language and a column for the translation with the Google Translate function.
You can build your own personalized translator in Google Sheets by combining the GOOGLETRANSLATE function with the DETECTLANGUAGE function. By doing this, you can detect the language of the original text and automatically translate it.
The first step is to combine the formulas. It should look like this:
=GOOGLETRANSLATE(A2, DETECTLANGUAGE(A2), “en’)
With this, you will be able to fill your table automatically with the source language and the translations of the responses you would like to analyze.
Once you have your data, you can even convert it into a chart. If you need a guide to making charts in Google Sheets, you can check out our article on Google Sheet Charts.
Frequently Asked Questions
When Should You Not Use GOOGLETRANSLATE in Google Sheets?
Once you enter information into the Google Translate tool, it becomes the property of Google. You should, therefore, not use Google Translate for sensitive information, personal information, or any information you don’t want Google to own.
Also, be aware that Google Translate can’t recognize idioms or complex phrases requiring context and does not proofread, so avoid using it for official work. If you must, make sure to run it by a translator to proofread it.
What are Some Similar Formulas to Google Translate in Google Sheets?
There are a number of formulas similar to GOOGLETRANSLATE. If you accidentally translate your text and your not sure which text you translated to or you want to remove the translation you can use the formula: =DELETELANGUAGE()
The formula DETECTLANGUAGE() to find out what language a text is in. This formula will return the 2 letter language code in the cell you put the formula in.
There is also IMPORTXML() which imports data into Google Sheets from another website, and GOOGLEFINANCE which is used for converting currencies.
How Do I Translate an Entire Sheet in Google Sheets?
There are essentially two ways you can do this. The manual way involves manually setting up the Google Translate formula and translating all cells individually. The second method involves using the Google Apps Script.
Can Google Translate Google Sheets?
Google Translate can be used inside Google Sheets. You can either enter a phrase or select a cell address to translate using Google Translate. You will also need to specify the source and the target language. However, if you’re unsure of the language, the function can also detect the language.
Translating Google Sheets
Having direct access to the Google Translate function in your spreadsheet saves you from the hassle of manually translating everything. You can simply put in the formula and let it handle the rest.
This allows you to have the ability to translate the text in your spreadsheets on the fly, and you can have different languages for different individuals if you’re using the handy sharing features in Google Sheets. Translate can be very helpful for students, small businesses, or large companies alike.
If you found this article useful, make sure you stick around and check out our other content on tricks and tips for Google Sheets.
