How to Translate in Excel

by

You’ve got text in Excel that you need in another language, but you’ve been copying every translation by hand.

This used to be a pain, especially when you had a whole column to translate.

But nothing to worry about. Excel now offers formulas for a range, while the pane still works well for a couple of cells.

In this article, I’ll show you all three approaches.

Method #1: Using the TRANSLATE Function

The TRANSLATE function is my first choice when several cells use the same source and target languages.

Below, column A contains eight English app labels. Cell B1 has the header Spanish (es), and I want the translations below it.

Dataset for TRANSLATE example 1

Here is the formula:

=TRANSLATE(A2:A9,"en","es")
=TRANSLATE(A2:A9,"en","es") in B2

The range in the first argument makes the formula spill eight translations from B2 through B9.

The Spanish (es) header pairs the language name with its code. The en source code tells Excel the input is English.

Language codes use short values such as en, es, and fr. If you omit the source code, Excel detects it automatically.

When I calculated the workbook for this article, Microsoft’s service returned “Guarda tus cambios” for “Save your changes” and “Añadir al carrito” for “Add to cart”.

In that same calculation, the service returned “Sigue comprando” for “Continue shopping” and “Tu pedido ha sido enviado” for “Your order has shipped”.

Note: TRANSLATE is only available in Excel for Microsoft 365 and needs internet access with connected experiences turned on. A #NAME? error means your Excel version lacks the function.

Method #2: Using DETECTLANGUAGE With TRANSLATE

Here’s another formula approach when the source text contains several languages.

Below, column A contains eight customer reviews in different languages. I want to identify each language before translating every review into English.

Dataset for TRANSLATE example 3

Here is the DETECTLANGUAGE formula:

=DETECTLANGUAGE(A2:A9)
=DETECTLANGUAGE(A2:A9) in B2

The service returned es, fr, de, it, pt, nl, sv, and pl in B2:B9 when I calculated this workbook for the article.

We can now feed those detected languages into TRANSLATE and return the reviews in English.

Here is the TRANSLATE formula:

=TRANSLATE(A2:A9,DETECTLANGUAGE(A2:A9),"en")
=TRANSLATE(A2:A9,DETECTLANGUAGE(A2:A9),"en") in C2

DETECTLANGUAGE supplies one source code for each review. TRANSLATE uses those codes with the matching text and spills the English results into column C.

At this article’s time of writing, the first three translations were “The sound is excellent,” “Battery lasts a long time,” and “Very good quality.”

Method #3: Using the Translate Pane on the Review Tab

If you use Office 2021 without Microsoft 365, or only need a couple of cells, the Translate pane is the better fit.

This method works in Microsoft 365 and Office 2021 with an internet connection.

Below is a survey sheet containing English text. I want to translate its contents into Swahili.

Dataset that needs to be translated

Here are the steps to translate the survey with the Translate pane:

  1. Select cell A1. On the Review tab, click Translate in the Language group.
Click the translate icon

The Translator pane opens on the right side of the Excel window.

  1. Set the source language to English and the target language to Swahili.
select the source and the target language

The pane shows a preview after you choose the target language.

  1. Copy the translated text and paste it into cell A1. Excel has no Insert button in this pane.
copy and paste the translated text in Excel

If you paste into merged cells, Excel shows a warning.

warning shown when pasting into merged cells in Excel

When you click OK, Excel shows a second warning.

warning when trying to paste in merged cells

Note: If you’re pasting into merged cells, unmerge the cells first. Paste the translation, then merge the cells again.

  1. Repeat the same steps for the remaining cells.
all text translated in Excel

Additional Notes About Translating in Excel

  • Machine translation needs a human check before you use it in customer-facing text.
  • Translation service quotas can interrupt a large batch. #BUSY! means the service is still answering.
  • TRANSLATE results can change over time because the wording comes from a live service.
  • Always pass target_language, so the result doesn’t depend on the system language of the computer opening the workbook.
  • The Translate pane works cell by cell, while TRANSLATE can process and spill a whole range.

Frequently Asked Questions

Is there a translate formula in Excel?

Yes. Excel for Microsoft 365 on Windows, Mac, and Mobile includes TRANSLATE(text, [source_language], [target_language]).

Why does TRANSLATE give #NAME?

#NAME? means your Excel version lacks TRANSLATE. The function isn’t available in Excel 2019 or Excel 2021 perpetual versions.

Does Excel translate offline?

No. TRANSLATE, DETECTLANGUAGE, and the Translate pane need an internet connection. A #CONNECT! error usually means Excel can’t reach the translation service.

What happened to the Functions Translator add-in?

Microsoft retired it. It is no longer available or supported.

Can the Translate pane translate a whole sheet at once?

No. The pane translates selected text cell by cell, so use TRANSLATE when you need to process a whole range.

Conclusion

I usually reach for TRANSLATE when I have a range, and I keep the Translate pane for a couple of cells or an Office 2021 workbook.

I hope you found this article helpful.

Other Excel articles you may also like:

Leave a Comment