Google Translate can be very helpful if you need to translate many individual words or phrases into another language – or several languages. If the words and phrases are listed in the Google Sheets file, you don’t need to copy and paste them into the Google Translate application or website.
All translation work can be done in a cell. Here’s how to use the Google Translate formula to translate Google Sheets Cells automatically.
Use Google Translate Formula on Google Sheets
The Google Translate formula has a simple syntax that is easy to use. It goes like this:
= GOOGLETRANSLATE (text, [source_language, target_language])
Here, the text refers to the source text that needs to be translated. You can enter the exact text, enclosed in quotation marks or cell names. For source and target languages, use two-letter codes, such as “en” for English, “de” for German, “es” for Spanish, and so on. Let’s look at one example. This is a simple sheet. The cells in column A contain several words and phrases in English, which must be translated to German (corresponding cells in column B), Spanish (column C), and Italian (column D).
- Open a new spreadsheet on Google Sheets.
- Enter source text in the appropriate cell. Here, we type a few phrases in cell A3 to A7. You can name column A “English”.
- Select cells that need to contain translated text. In our example, cells B3 to B7 will display text in German. So in cell B3, try typing “= GOOGLETRANSLATE (A3, [en, de])”. Now press Enter.
- If you want to translate the rest of the phrases, just expand the selected area.
- If you want to translate cell text from column A to another language, name the next column Spanish (or choose another language and do the same thing. Type “= GOOGLETRANSLATE (A3, [en, es])”, press Enter and expand the area selected by dragging cell C3 boundaries.
How to Set Cell Text to Be Translated Automatically
After you enter a formula to translate text from one language to another, you don’t need to do it again for the same language. If you want to continue to add words in English, simply expand the selected area in the column with the translated text, to enter the number of cells you need (which you can further develop). Do this:
- Select the last cell that contains the translated text, produced by the formula you have entered. If you follow our example, it’s cell B7.
- Click and hold the lower right corner of the cell, and move it down, to enter more cells.
- The newly selected cell will display a warning: “#VALUE!” When you place the cursor over it, it will display “Error Function GOOGLETRANSLATE parameter value 1 must be non-empty”. Don’t worry, because we will be filling these cells in a little while. You can do the same thing with cells in columns C and D if you want your entry to be translated into multiple languages at once.
- Return to column A and type in an English word or phrase, such as “Happy Birthday!”, Press Enter, and see what happens. If you do everything correctly, the phrase has been translated into all the languages that you specify.
Now that you have learned how to translate Google Sheets cell content automatically, you can add as many languages as you want and continue entering words in cell A.
Translate Cells from Automatically Detected Languages into Your System Language
If you have a lot of words in various languages that you want to translate to English, you don’t even need to know what language it is. There is a shortened version of the Google Translate function that allows you to ignore source and target languages. If you use it, the source language will be detected automatically, while the target language will be set to your default system language (in this case, English). Here’s how to use the shortened function:
- Select the cell and type in a foreign word. We typed “Grazie” in cell D14.
- Select the next cell in the row and type the short function “= GOOGLETRANSLATE (D14)”.
- Press Enter and expand the area if you have more words to translate.
Translate Cells from Automatically Detected Languages into All Languages
If you like Google Translate to automatically detect the source language and then translate it to a language other than your system language, it is possible to write functions like this:
= GOOGLETRANSLATE (D14, “auto”, “ru”)
and translates Italian words into Russian. Once again, you can expand the area as you wish.
Note that the shortened version of the Google Translate formula must follow the logic shown above. If you omit the source language, you must also eliminate the target language. If you want the source language to be detected automatically but you want to specify the target language, you need to write “auto” in the appropriate place in the formula.
Instant Cell Translation
Google Translate and Google Sheets are a winning combination. Once you learn how to use Translate in Google Sheets cells, it will save a lot of time and effort.