How to Use the Google Translate Function in Google Sheets

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.

What Is the Google Translate Function in Google Sheets?

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 to translate around 100 languages.

The function can be put into any cell and given a cell address or a phrase. The source and the target languages also need to be specified 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 Translate in Google Sheets

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.

Translate Text In Google Sheets

To demonstrate the function in Google Sheets, here is a sample spreadsheet that contains a few sentences in English. 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 to 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

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.

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. 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

Google Sheets Translate Function – FAQ

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 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.

Most Popular Posts

Jake Wright

Jake Wright

Jake is a spreadsheet expert and content writer from New Zealand. He has a double Bachelor's Degree in Teaching and has been working in the education industry for over 11 years. His experience makes him adept at breaking down complex topics so that everyone is able to understand.