Google Sheets is an excellent web-based spreadsheet application that offers a powerful set of features for individuals and small businesses, all for free. One of the main reasons people switch to this software from Excel is its collaborative nature. With its handy sharing tools, many people use it for group projects. However, there is often a language barrier when users speak different languages. For these individuals, Google’s translation function can be a real lifesaver.
In this article, we will discuss the Google translation function and how you can use it in Google Sheets. Keep reading to master this function.
What is the Google Sheets translation function?
The Google translation function in Google Sheets allows you to translate text in your spreadsheets from one language to another. It uses Google’s translation service, which supports approximately 100 languages.
The function can be used in any cell by giving it a cell address or a phrase. You also need to specify the source language and the target language in the formula for it to work correctly.
Although Google Translate is generally accurate, we still recommend using simple sentences and easier words, as the function can often mistranslate complex text.
The formula to translate in Google Sheets
Here is the translation formula for Google Sheets:
=GOOGLETRANSLATE(text, [source_language, target_language])
The formula uses three arguments which include:
- Text
- Source language
- Target language
The last two arguments are optional. Only the text argument is required for the formula to work correctly. Here is a brief summary of what each argument does:
- Text: This argument specifies the text you want to translate. You can either type the text directly in quotes or specify the cell address.
- Source_language: This is an optional argument that allows you to select the language of the text you want to translate.
- Target_language: This is also an optional argument that allows you to select the language you want to translate the text into.
Pros and cons of using Google Sheets Translate
Translating in Google Sheets 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’s free.
- It’s flexible when used in Google Sheets.
Cons:
- It’s not the most accurate when translating long sentences and idiomatic expressions.
Translating text in Google Sheets
To illustrate the function in Google Sheets, here is a spreadsheet containing a few sentences in English. Our goal is to use the Google Sheets translation formula to translate this text from English to German and Japanese.
To do this, let’s start by translating the text to German. The steps to follow are as follows:
Step 1: To translate the text, go to the cell where you want to display the translated text and enter the Google translation formula. Make sure the language code is in quotes, otherwise the formula will display 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 lines containing the text we need to translate. Instead of typing the formula in each cell, we will use the auto-fill feature in Google Sheets. To do this, click on the cell and drag the blue dot in the bottom-right corner of the cell to cover all desired cells.
Next, in the Google translation spreadsheet, we will follow the same steps mentioned above to convert the text to Japanese, simply changing the target language.
Use “Auto” as the language argument to translate in Google Sheets
Sometimes, you may not be sure of the language used in the spreadsheets. In this case, you can use “Auto” as the language argument, and the formula will automatically 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 previous examples. Specify the cell address, but instead of typing the language code in the source language argument, write “Auto”. This allows Google Sheets to detect the language in the specified cell.
Step 2: Click Enter to execute the formula. You can use the auto-fill feature to fill in empty cells.
If you are collaborating with multiple users whose language is different from yours, you can select the target language as “Auto”. This will translate the text into the default language of your computer. To do this, follow these steps:
Step 1: Enter 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 user’s computer default language. In this case, our computer’s default language was English.
Step 2: Press Enter to execute the formula.
A simple trick you can use with the formula is to always set the source language and target language arguments to “Auto”. This allows the formula to automatically detect the language and change it to the default language of your computer.
This feature can come in handy if you have a large spreadsheet with multiple users who speak different languages.
Note: If you enter the formula in a cell and get #VALUE! error, it means the target cell is empty. Make sure to check that you have the correct target cell. If the cell is intentionally empty or you have skipped cells 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, the function should do nothing. If you encounter other errors or issues that you are struggling with, you can consult the Google Translate community for quick solutions or post your problem for other community members to help you resolve it.
How to find language codes?
A problem many users may encounter when using the Google Sheets translation formula is using incorrect language codes. All supported languages have their specific code that you need to enter to perform a translation. This can be frustrating for many people, but there is a simple solution.
To detect the language in a cell or range, you can use the DETECTLANGUAGE formula in Google Sheets. The syntax of this formula is as follows:
=DETECTLANGUAGE(text_or_range)
To use this formula in sheets, select an empty cell and enter the formula. It is the same code you use in the Google translation formula. In the text_or_range, simply enter the cell address you want to detect the language of. The language code will be displayed as output.
Alternatively, you can use the following codes:
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
In addition to the Google translation function, you can also use Google Sheets add-ons to translate in Google Sheets. Google Sheets add-ons are like third-party extensions or applications that offer special functions you can use in Google Sheets. One 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 translation engine and can translate your entire sheet or a selected portion of it into over 100 languages with just one click.
To use this add-on:
- Go to Add-ons in the toolbar
- Select Get add-ons.
- Search for “Translate My Sheet” in the search bar.
- Then install the add-on.
- You may need 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 Add-ons > Translate My Sheet and select 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 entire sheet, and you can even duplicate the sheet before translating. If you choose to duplicate the sheet, your translated text will appear in a new Google Sheets window.
You can also automatically add a background color to the translated cells to distinguish them from others.
Unlike Google Translate, the Translate My Sheet add-on will translate the text in its original cell rather than an adjacent cell. It does not allow you to translate selected words in a cell; it can only translate the entire text. The only other option is to translate into a duplicated sheet.
How to use Google Sheets Translate in data analysis
Let’s say you have a website, an online store, or a chain, and you want to analyze the comments section for marketing purposes. Sometimes, comments may be in multiple languages, and that’s where Google Sheets Translate can come in handy.
Using the DETECTLANGUAGE function, you can create a column for the language of the response and a column for translation using the GOOGLETRANSLATE function.
You can create your own custom translator in Google Sheets by combining the GOOGLETRANSLATE function with the DETECTLANGUAGE function. This way, you can automatically detect the language of the original text and translate it.
The first step is to combine the formulas. It should look like this:
=GOOGLETRANSLATE(A2, DETECTLANGUAGE(A2), "en')
This way, you will be able to automatically populate your table with the source language and translations of the responses you want to analyze.
Once you have your data, you can even convert it into a chart. If you need a guide on how to create charts in Google Sheets, you can check out our article on Google Sheets charts.
Frequently Asked Questions
When should you not use GOOGLETRANSLATE in Google Sheets?
Once you enter information into the Google translation tool, it becomes the property of Google. Therefore, you should not use Google Translate for sensitive information, personal information, or any information you do not want Google to own.
Also, note that Google Translate does not recognize idiomatic expressions or complex phrases that require context and does not proofread the text. So, avoid using it for official work. If you need to, make sure to have it proofread by a translator.
What are similar formulas to Google Translate in Google Sheets?
There are several similar formulas to GOOGLETRANSLATE. If you accidentally translate your text and are unsure of the language you translated or want to remove the translation, you can use the DELETELANGUAGE formula.
The DETECTLANGUAGE formula allows you to know what language a piece of text is in. This formula outputs the 2-letter language code in the cell where you place the formula.
There is also IMPORTXML, which allows you to import data into Google Sheets from another website, and GOOGLEFINANCE, which is used to convert currencies.
How to translate an entire sheet in Google Sheets?
Essentially, there are two ways to do it. The manual method involves manually setting up the Google translation formula and translating each cell individually. The second method involves using Google Apps Script.
Can Google Translate translate Google Sheets?
Google Translate can be used in Google Sheets. You can either enter a sentence or select a cell address to translate using Google Translate. You also need to specify the source language and the target language. However, if you are unsure of the language, the function can also detect the language.
Translate in Google Sheets
Having direct access to the Google translation function in your spreadsheet saves you the hassle of manually translating everything. You can simply enter the formula and let the software handle the rest.
This allows you to easily translate text in your spreadsheets and allows you to have different languages for different individuals if you’re using Google Sheets’ handy sharing features. Translation can be very useful for students, small businesses, or large enterprises.
If you found this article helpful, be sure to stay tuned and check out our other content on tips and tricks for Google Sheets.
Note: This article has been translated and adapted from an English text. To learn more about Google Sheets, visit Crawlan.com.
