How to Automatically Translate Cells in Google Sheets Cells

Google Translate can be of great help if you need to translate a bunch of individual words or phrases into another language – or multiple languages. If you happen to have those words and phrases listed in a Google Sheets file, you don’t have to copy and paste them into the Google Translate app or website.

All the translation work can be done within the cells. Here’s how to use Google Translate formulas to translate Google Sheets Cells automatically.

Use Google Translate Formula in Google Sheets

The Google Translate formula has a simple syntax that’s 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 either the exact text, enclosed in quotation marks or the name of the cell. For the source and target languages, use two-letter codes, such as “en” for English, “de” for German, “es” for Spanish, and so on. Let’s have a look at one example. Here’s a simple sheet. The cells in column A contain some words and phrases in English, which should be translated to German (appropriate cells in column B), Spanish (column C), and Italian (column D).

1. Open a new spreadsheet in Google Sheets.
   
2. Enter the source text in appropriate cells. Here, we typed a couple of phrases in cells A3 to A7. You can name the column A “English”.
   
3. Select the cells that need to contain the translated text. In our example, cells B3 to B7 should display the text in German. So in cell B3, try typing “=GOOGLETRANSLATE(A3, “en”,”de”)” . Now hit Enter.
   
4. If you’d like to translate the rest of the phrases, just expand the selected area.
   
5. If you’d like to translate the cell text from column A into another language, name the next column Spanish (or pick another language and do the same. Type “=GOOGLETRANSLATE(A3,”en”,”es”)”, hit Enter and expand the selected area by dragging the border of the cell C3.
   

How to Set the Cell Text to Translate Automatically

Once you’ve entered the formula to translate text from one language to another, you don’t have to do it again for the same language. If you intend to keep adding words in English, just expand the selected area within the column with the translated text, to include the number of cells you need (which you can further expand later). Do this:

1. Select the last cell that contains the translated text, generated by the formula you’ve already entered. If you followed our example, it’s cell B7.
   
2. Click and hold the down right corner of the cell, and move it down, to include more cells.
   
3. The newly selected cells will display the warning: “#VALUE!” When you place the cursor on it, it will display “Error Function GOOGLETRANSLATE parameter 1 value should be non-empty”. Don’t worry about it, as we’re going to fill these cells in a minute. You can do the same with the cells in the columns C and D if you’d like your entries to get translated to multiple languages at once.
   
4. Return to column A and type an English word or phrase, such as “Happy Birthday!”, hit Enter, and see what happens. If you did everything right, the phrase has been translated to all the languages that you’ve set.
   

Now that you’ve learned how to automatically translate Google Sheets cell content, you can add as many languages as you want and keep entering words in cell A.

Translate Cells from Auto Detected Language to Your System Language

If you have a bunch of words in various languages that you’d like to translate to English, you don’t even have to know which languages are those. There is a shortened version of Google Translate function which allows you to leave out the source and the target language. 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:

1. Select a cell and type a foreign word. We typed “Grazie” in cell D14.
2. Select the next cell in the row and type the shortened function “=GOOGLETRANSLATE(D14)”.
   

   

3. Hit Enter and expand the area if you have more words to translate.

Translate Cells from Auto Detected Language to Any Language

If you’d like Google Translate to auto-detect the source language and then to translate into a language other than your system language, it’s possible to write the function like this:

=GOOGLETRANSLATE(D14, “auto”, “ru”)

and translate the Italian word to Russian. Again, you can expand the area as you wish.

Note that shortened versions of the Google Translate formula have to follow the logic shown above. If you omit the source language, you have to omit the target language too. If you’d like the source language to be detected automatically but you want to define the target language, you need to write “auto” in the appropriate place within the formula.

Instant Cell Translation

Google Translate and Google Sheets are a winning combination. Once you’ve learned how to use Translate in Google Sheets cells, it will save you lots of time and effort.

Have you had a chance to create a multilingual spreadsheet? Did you use the Google Translate function? Tell us about it in the comments section below.