Translate a Japanese Excel File to English Without Breaking Formulas: TRANSLATE() vs a Small Python Round-Trip

You received a Japanese .xlsx that still has to calculate. Blind cell translation breaks SUM, sheet references, and IDs. Here are two options: Excel's TRANSLATE() on Microsoft 365, and a ~40-line openpyxl round-trip we actually ran. Konnichiwa! I'm AI Sensei LAB, and I share prompts and AI workflows for bilingual creators. Disclosure: this post was drafted and edited with AI tools. #ABotWroteThis. Microsoft Support wording for TRANSLATE() was checked on October 24, 2026. The Python round-trip was run on this writing box with openpyxl 3.1.5 on a team-made fictional workbook. We did **not* run TRANSLATE() live (no Microsoft 365 here). ChatGPT Free CSV translate was run ~06:09–06:12 JST on October 24, 2026 (signed in; no model name shown); reply quoted from the saved ChatGPT Free CSV. No product links.* Japanese workbooks are awkward for machine translation because the cells you want to read (headers, product names, notes) sit next to the cells Excel must keep untouched (formulas, SKUs, mixed JP/EN IDs, full-width digits used as values). Two practical paths: Option A — =TRANSLATE() in a helper column (Microsoft 365). Option B — export only Japanese text cells → translate the CSV → write back into a copy, then verify formula addresses are unchanged. Option A: Excel's TRANSLATE() (Microsoft 365) Microsoft Support's TRANSLATE function page (https://support.microsoft.com/en-us/excel/functions/translate-function, checked October 24, 2026) says it Applies To: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, and Excel Mobile. Syntax: =TRANSLATE(text, [source language], [target language]) Microsoft describes it as translating text "by using Microsoft Translation Services," and notes it is a service backed function — supported languages and results may vary as languages are added or removed. Example pattern for a Japanese label in A2: =TRANSLATE(A2, "ja", "en") Put that in a helper column (or a helper sheet). Leave the original Japanese and every formula column alone. Common errors Microsoft lists (verbatim sense from the same page): Text Too Long — "You have too many characters in a cell. Reduce your cell size and try again." Request Throttled — "You have exceeded your daily quota of the translation function." Also listed: Error in Value (non-text), Invalid Language. Microsoft's page does not state a numeric per-cell character cap such as "3,000," and it does not quantify the daily quota. I won't invent those numbers. Live test status: we did not evaluate TRANSLATE() on this machine (no Microsoft 365). Treat Option A as documentation-backed, not demo-backed. If you have M365, try the helper-column pattern on a copy of your file and watch for throttling on large sheets. Option B: openpyxl round-trip (what we ran) Idea: Open the .xlsx with openpyxl. Export only non-formula text cells that contain Japanese (or full-width digits) to CSV: sheet,cell,text. Translate the text column with any tool (ChatGPT Free paste, DeepL, …) while protecting IDs. Write the English strings back into a copy of the workbook at the same coordinates. Compare formula count and addresses before vs after. openpyxl's tutorial notes that data_only "controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet." For a round-trip that must keep calculations, load with the default (formulas as strings), never overwrite formula cells, and remember openpyxl "does currently not read all possible items in an Excel file so shapes will be lost from existing files if they are opened and saved with the same name." Charts / images are outside a simple cell pass — same limitation competitors note for sheet names, comments, and headers. Sample workbook (fictional) Sample workbook (team-made, fictional) — Sheet 価格表 + メタ: Japanese headers and product names SUM and cross-sheet formula =F8*メタ!B3 Full-width digits １２３ Mixed IDs that must stay put: KEEP-ID-９９, DOC-倉庫-001 Numeric unit prices left as numbers (not exported) Script: a small openpyxl helper (export / writeback / check; ~40 lines on our box). Export output (verbatim from our run) openpyxl_version=3.1.5 formula_count_before=6 formula:価格表!F2==D2*E2 formula:価格表!F3==D3*E3 formula:価格表!F4==D4*E4 formula:価格表!F5==D5*E5 formula:価格表!F8==SUM(F2:F5) formula:価格表!E10==F8*メタ!B3 exported_rows=39 skipped_formula_cells_seen=6 skipped_number_like=9 skipped_no_japanese=9 CSV preview (export run): sheet,cell,text 価格表,A1,品番 価格表,B1,商品名 価格表,C1,カテゴリ 価格表,D1,単価（円） 価格表,E1,数量 価格表,F1,小計 価格表,G1,備考 価格表,H1,社内ID ... 価格表,E6,１２３ 価格表,H6,KEEP-ID-９９ 価格表,H12,DOC-倉庫-001 メタ,B2,本表の単価は税抜です。消費税は別途。 Translate the CSV Integrity demo (hand translation, also kept): hand-translated CSV (kept for the integrity demo) — used first to prove write-back. ChatGPT Free run (run note): Run 2 — ~06:09-06:12 JST 2026-10-24. ChatGPT web, signed in (sidebar showed account with 'Free' plan label; no model name shown, not guessed). New chat. No rate limit. Reply was plain CSV. Prompt: translate the CSV text column only, protect IDs. Reply copied verbatim: sheet,cell,text 価格表,A1,Product No. 価格表,B1,Product Name 価格表,C1,Category 価格表,D1,Unit Price (JPY) 価格表,E1,Quantity 価格表,F1,Subtotal 価格表,G1,Notes 価格表,H1,Internal ID 価格表,B2,Green Tea Bags (20 bags) 価格表,C2,Beverages 価格表,G2,Eligible for Winter Campaign 価格表,B3,Roasted Green Tea Powder 価格表,C3,Beverages 価格表,G3,For Commercial Use · Reorders Available 価格表,B4,Ceramic Mug (White) 価格表,C4,Tableware 価格表,G4,Fragile · Handle with Care 価格表,B5,Wooden Coasters (Set of 2) 価格表,C5,Tableware 価格表,G5,Low Stock 価格表,B6,Tasting Sample (Not for Sale) 価格表,C6,Other 価格表,E6,１２３ 価格表,G6,Contains an ID excluded from translation; for trade show use 価格表,H6,KEEP-ID-９９ 価格表,E8,Total Quantity 価格表,G8,Formula Cell (Do Not Translate) 価格表,D10,Estimated Total (Tax Included, Demo) 価格表,G10,Formula Referencing Another Sheet 価格表,A12,Note 価格表,B12,Do Not Translate the Product No. or Internal ID Columns 価格表,H12,DOC-倉庫-001 メタ,A1,Exchange Rate Note メタ,B1,Tax-Inclusive Display メタ,A2,Rate Note メタ,B2,Unit prices in this table exclude tax. Consumption tax is charged separately. メタ,A3,Reference Coefficient メタ,A4,Description メタ,B4,The coefficient is an example of a consumption tax rate (fictional demo) Same sheet/cell keys as the export (full CSV above). IDs KEEP-ID-９９ and DOC-倉庫-001 and full-width １２３ were left unchanged. Writing this CSV back into a copy (the ChatGPT English copy) reported formulas_unchanged=True (6 formulas). Write-back + formula integrity check After writing translations into a copy — hand CSV → English copy A, ChatGPT CSV → English copy B — both runs: updates_applied=39 formula_count_after=6 formulas_unchanged=True Full before/after dump saved with the run notes. --- BEFORE (r20261024_2_sample_ja.xlsx) --- formula_count=6 formula:価格表!F2==D2*E2 formula:価格表!F3==D3*E3 formula:価格表!F4==D4*E4 formula:価格表!F5==D5*E5 formula:価格表!F8==SUM(F2:F5) formula:価格表!E10==F8*メタ!B3 --- AFTER writeback (r20261024_2_sample_en.xlsx) --- formula_count=6 (same six addresses and formulas) Checker habits for Japanese sheets Formula count and full sheet!address=formula strings must match. Flag leftover hiragana/katakana/kanji in columns you meant to translate. Full-width digits (１２３) — decide deliberately: leave as-is, or normalize to ASCII outside the translator if your ERP needs half-width. Never let the translator rewrite SKU / 社内ID cells (KEEP-ID-９９, DOC-倉庫-001). Re-open the English copy in Excel/LibreOffice and confirm calculated values still make sense (openpyxl stores formulas; it does not recalculate them). What a cell pass still misses Even when formulas survive: Sheet names (our demo left 価格表 / メタ untranslated on purpose) Comments, charts, text boxes, headers/footers, shapes (openpyxl may drop shapes on save) If those matter, plan a second pass or keep a human checklist. Which option when? Situation Lean toward You have Microsoft 365, a few columns of labels, and formulas already isolated Option A helper column Large Japanese sheets, mixed IDs, need a repeatable check Option B round-trip No M365, or you hit Request Throttled Option B Charts/shapes are the deliverable Neither cell method is enough — expect manual work Related (Dev.to only) Translate a Japanese i18next JSON file to English with AI without losing placeholders How to translate SRT subtitles with ChatGPT (Japanese to English) without breaking the timing If you try this on a nasty workbook (array formulas, external links, shared strings quirks), leave a comment with the edge case — no sample that contains confidential data, please.