GOOGLETRANSLATE in Google Sheets: how to use it

The GOOGLETRANSLATE formula in Google Sheets

Type =GOOGLETRANSLATE(A2, "en", "fr") to translate the text in A2 from English to French, or use "auto" as the source to let Google detect the language. The result is a live formula, one per cell; for a lasting copy, paste it as values, and for whole tabs, translate the cells without formulas.

Steady Translate translates whole tabs without formulas. 200 free cells a day.

How it works

GOOGLETRANSLATE(text, source_language, target_language)

text is the words in quotes or, usually, a cell such as A2.
source_language is a language code such as "en", or "auto" to let Google detect it.
target_language is the code to translate into, such as "fr". Both language arguments are optional, but giving both avoids surprises.

Codes are mostly two letters: "en" English, "es" Spanish, "fr" French, "de" German, "ja" Japanese. Chinese takes a region: "zh-CN" or "zh-TW". To see what Google detects, use =DETECTLANGUAGE(A2).

Translate a column in three steps

Write the first formula

Next to your text, in B2, type =GOOGLETRANSLATE(A2, "auto", "es"). To pick the language in one place, put the code in a cell and point to it: =GOOGLETRANSLATE(A2, "auto", $E$1).

Fill it down

Drag the small square at the corner of B2 down the column, or double-click it. Each row gets its own formula.

Keep the result

Copy the column, then Edit > Paste special > Values only (Ctrl+Shift+V). Now it is plain text that will not change.

Where the formula falls short

It stays a formula. Until you paste values, the translation can be fetched again when the sheet recalculates, and it changes or disappears when the source cell does.

Many formulas are slow. Each one is a separate request to Google's translation service. With hundreds or thousands, cells can sit on Loading or show an error for a while. Google does not publish a number.

Numbers and dates need keeping out. To translate only text, wrap it: =IF(ISTEXT(A2), GOOGLETRANSLATE(A2, "auto", "fr"), "") leaves anything that is not text blank.

A whole tab means a second grid. You build a parallel tab of formulas, then copy formats, column widths and merges by hand. Formulas in the source do not carry over as formulas.

Each cell is translated alone. A one-word label has no context, so it can come out wrong. That is true of any cell-by-cell translation.

Many cells, no formulas

Steady Translate uses the same Google translation service from inside Sheets, but writes plain translated text instead of formulas. Open it from Extensions, choose Selected cells, This tab or All visible tabs, and it copies each tab and translates the copy:

Formulas, numbers, dates, empty cells, links, email addresses and product codes are left as they are, and formats and merged cells come along in the copy.

Before each run it tells you how many cells will be translated. A run that is cut off resumes from the next cell.

Free: 200 cells a day. Unlimited: $7.99 for 30 days or $39.99 for one year, one payment with no automatic renewal. Google's own daily limit, 5,000 translations on personal accounts and 20,000 on Google Workspace, applies to every plan.

Where the add-on's limits are

Machine translation, one cell at a time, the same as the formula: review anything important.

Comments, notes and drawings are not translated, and partial formatting inside a cell, such as one bold word, is lost.

Text that an array formula appears to produce is skipped and reported, which can leave some real text next to it untranslated.

Questions

How do I use GOOGLETRANSLATE in Google Sheets?

Type =GOOGLETRANSLATE(A2, "en", "fr") to translate the text in A2 from English to French. The first argument is the text or its cell, the second the source language code, the third the target language code.

How do I let Google detect the source language?

Use "auto" as the source, as in =GOOGLETRANSLATE(A2, "auto", "es"). DETECTLANGUAGE(A2) shows the language code Google detects.

How do I turn GOOGLETRANSLATE results into plain text?

Copy the cells, then use Edit > Paste special > Values only, or Ctrl+Shift+V (Cmd+Shift+V on a Mac). The translations stay and the formulas are gone.

Why does GOOGLETRANSLATE show Loading or an error?

Each formula is a separate request to Google's translation service, and a sheet with very many of them can take a while to fill in. Google does not publish a limit; translating fewer cells at a time, or keeping values instead of formulas, helps.

Can I translate many cells without formulas?

Yes. Steady Translate copies a tab and writes translated text into the copy, leaving formulas, numbers and dates as they are.