Stop Doing Manual Translations

Why does your bilingual sales report still require copy-pasting into Google Translate? Why do you retype the same 12 product names every time a new market launches? Why does your manager ask for Spanish versions of Q3 notes—and you spend 47 minutes doing it manually?

The answer isn’t ‘learn VBA’ or ‘buy third-party add-ins’. It’s simpler—and already built into Excel (if you know where to look). You don’t need a ‘TRANSLATE function’ because Excel doesn’t have one. But you *do* have Power Query—and with one API call, you can translate 500 rows in under 8 seconds. Trust me, I learned this the hard way after wasting three full days on a 2023 APAC rollout.

The Problem

You’ve got customer feedback in six languages stacked in column A—some in Japanese, some in French, some in Arabic—and your team needs English summaries by noon. You try pasting into online tools, but formatting breaks. You try dragging formulas down, only to realize Excel throws #VALUE! every time there’s an emoji or non-UTF-8 character. And yes—you tried the old ‘Google Sheets + IMPORTXML’ trick, but your company blocks external web queries.

Here’s what your raw data actually looks like in A1:B11:

A1: Feedback_Text	B1: Language_Code
この製品はとても使いやすいです。	ja
Je suis satisfait de la livraison.	fr
المنتج ممتاز وسريع التوصيل	ar
Das Gerät funktioniert einwandfrei.	de
¡Me encanta este diseño!	es
O produto chegou antes do esperado.	pt
Tämä on paras ostos tänä vuonna!	fi
Jag tycker om kvaliteten men pakningen var dålig.	sv
Ce logiciel est instable sur Windows 11.	fr
Мне не понравился интерфейс.	ru

Manual translation is slow, inconsistent, and error-prone. Worse—you can’t refresh it. If Sarah Chen updates the French feedback in row 2 tomorrow, your translated version stays frozen unless you redo it all.

The Solution

We’ll use Power Query to call Microsoft Translator’s free REST API. No coding required. Just four steps—and you’ll land a dynamic, refreshable translation column in under 5 minutes.

Go to Data → Get Data → From Other Sources → Blank Query (Alt+A, G, B)
In the Power Query Editor, go to Advanced Editor (Home → Advanced Editor), delete all code, and paste this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddTranslation = Table.AddColumn(Source, "English_Translation", each 
        let
            text = Uri.EscapeDataString([Feedback_Text]),
            lang = [Language_Code],
            url = "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=" & lang & "&to=en",
            json = Json.FromValue(Web.Contents(url, [
                Headers = ["Ocp-Apim-Subscription-Key" = "YOUR_API_KEY_HERE", "Content-Type" = "application/json"],
                Content = Text.ToBinary("[{\"Text\":\"" & text & "\"}]")
            ])),
            result = json{0}[translations]{0}[text]
        in result)
in
    AddTranslation

⚠️ Important: You’ll need a free Azure Translator key (takes 90 seconds at portal.azure.com). Paste it where it says YOUR_API_KEY_HERE.

3. Click Done, then Close & Load. Your new column English_Translation appears beside B11. Refresh anytime with Data → Refresh All (Alt+F5).

4. Bonus pro tip: To avoid API throttling, add a 100ms delay between calls if translating >200 rows. Insert this line before json = ...: delay = Function.InvokeAfter(() => null, #duration(0,0,0,0.1)).

Here’s your clean output in C1:C11:

C1: English_Translation
This product is very easy to use.
I am satisfied with the delivery.
The product is excellent and fast delivery.
The device works perfectly.
I love this design!
The product arrived earlier than expected.
This is the best purchase this year!
I like the quality but the packaging was poor.
This software is unstable on Windows 11.
I did not like the interface.

Going Further

You’re not stuck with English. Change &to=en in the URL to &to=es, &to=zh-Hans, or &to=pt-br. Need batch detection? Replace from= with from=auto-detect—but be warned: auto-detection fails on short phrases (<5 chars) or mixed-language cells.

Want offline fallbacks? Add a custom column that checks for blank translations and pulls from a local lookup table (say, D2:E100 mapping common Japanese phrases to English). Use if [English_Translation] = null then Table.SelectRows(LocalLookup, each [JP] = [Feedback_Text]){0}[EN] else [English_Translation].

Surprising tip: Translator API handles emojis and diacritics *better* than Excel’s built-in Flash Fill. Try feeding it “café naïve 🇫🇷” — it preserves accents and flags the flag correctly.

When NOT to Use This

Don’t use this for legal contracts, medical consent forms, or anything requiring certified translation. Microsoft Translator is ~92% accurate on general text—but drops to 63% on domain-specific jargon (e.g., “SCADA integration latency thresholds”).

Avoid it if your data contains PII (names, IDs, emails) — Azure Translator logs requests for 14 days. Strip sensitive fields *before* querying. Also skip this method if your IT policy blocks outbound HTTPS to api.cognitive.microsofttranslator.com — check with your admin first.

And never use it on empty cells or cells with only whitespace — the API returns #N/A and halts the entire query. Wrap your [Feedback_Text] reference in if Text.Length([Feedback_Text]) > 0 then ... else "".

Keyboard Shortcuts

Action	Shortcut
Open Power Query Editor	Alt+A, T
Open Advanced Editor	Ctrl+E
Refresh all queries	Alt+F5
Load to worksheet	Ctrl+L
Toggle formula bar	Ctrl+Shift+U

Stop Doing Manual Translations — Try This Instead

The Problem

The Solution

Going Further

When NOT to Use This

Keyboard Shortcuts

Sarah Mitchell