Translate Excel Sheet with VBA + TRANSLATE()
Automatically translate an entire Excel sheet using VBA and the built-in =TRANSLATE() function.
If you've ever needed to translate a whole worksheet (an invoice, product list, packing list, etc.) without breaking the layout, this approach works well:
- Keep your original sheet untouched
- Create a new "translated" sheet with the same formatting
- Use Excel's built-in
=TRANSLATE()function to translate each text cell automatically
=TRANSLATE() is a cloud-based Excel function powered by Microsoft Translator. It requires an internet connection to work. It's available in Excel for Microsoft 365 (including Mac), Excel for the web, and mobile, but it may not exist in some builds/channels yet.
Before you start: confirm =TRANSLATE() exists in your Excel
In any cell, try:
=TRANSLATE("hello","en","ja")If you get #NAME?, your Excel build doesn't have the function yet. Microsoft notes it's supported on Microsoft 365 / web / mobile and is service-backed.
It has also been introduced as a new function via Microsoft 365 Insider channels, so availability can depend on update channel/rollout.
What You'll Build
A VBA macro that:
- Creates a copy of your active sheet
- Translates all text cells using the
=TRANSLATE()function - Preserves numbers, formulas, and formatting
- Names the new sheet with the target language code
Prerequisites
- Microsoft 365 subscription (the
=TRANSLATE()function requires Microsoft 365) - Excel for Windows or Mac (Web version has limited VBA support)
- Macros enabled in Excel settings
Save Your File as Macro-Enabled Workbook
- 1Open your Excel file
- 2Go to File → Save As
- 3Select Excel Macro-Enabled Workbook (*.xlsm) from the file type dropdown
- 4Click Save
Important: Regular .xlsx files cannot contain macros. You must save as .xlsm to use VBA code.
Open the VBA Editor
Keyboard Shortcut
Press Alt + F11 (Windows) or Option + F11 (Mac)
Alternative Method
- 1.Go to the Developer tab
- 2.Click Visual Basic
Tip: If you don't see the Developer tab, go to File → Options → Customize Ribbon and check the Developer box.
Download and Import the VBA Code
Get the VBA Macro from GitHub
Download the TranslateSheet.bas file from our open-source repository.
How to Import the .bas File
- 1Download the
TranslateSheet.basfile - 2In the VBA Editor, go to File → Import File...
- 3Select the downloaded
TranslateSheet.basfile - 4The module will appear in your project's Modules folder
Alternative: You can also copy the code directly from GitHub and paste it into a new module (Insert → Module).
Run the Macro
- 1Make sure the sheet you want to translate is active
- 2Press Alt + F8 to open Macros dialog, select TranslateActiveSheetToNewSheet, then click Run
- 3Enter the source language code (leave blank for auto-detection)
- 4Enter the target language code (e.g., "ja" for Japanese)
- 5Enter the new sheet name or accept the default
- 6Choose whether to convert formulas to values (Yes/No)
Note: Translation speed depends on the number of cells and your internet connection. Sheets with 500+ text cells may take several minutes. For very large sheets (1000+ cells), consider splitting into smaller batches.
Important Notes
- Rate limits: Microsoft Translator has usage limits. If you're translating thousands of cells, you may see temporary errors. Wait a few minutes and try again.
- Wait for calculation: After running the macro, wait for all TRANSLATE formulas to finish calculating before converting to values. You'll see "Calculating..." in the status bar while it's working.
- Merged cells: The macro preserves merged cell formatting, but only the top-left cell of a merged area contains text. Other cells in the merge will remain empty.
Supported Language Codes
Use these codes when prompted for source or target language:
| Code | Language |
|---|---|
| en | English |
| ja | Japanese |
| zh-CN | Chinese (Simplified) |
| zh-TW | Chinese (Traditional) |
| ko | Korean |
| es | Spanish |
| fr | French |
| de | German |
| it | Italian |
| pt | Portuguese |
| ru | Russian |
| ar | Arabic |
| hi | Hindi |
| vi | Vietnamese |
| th | Thai |
Tip: For a full list of language codes supported by the TRANSLATE function, visit Microsoft's official documentation.
Troubleshooting
"TRANSLATE function not available"
The TRANSLATE function requires a Microsoft 365 subscription. Make sure you have the latest version of Excel and that you're signed in to your Microsoft 365 account.
"Macros are disabled"
Go to File → Options → Trust Center → Trust Center Settings → Macro Settings and select "Enable all macros" or "Disable all macros with notification".
"Translation returns #VALUE! error"
This usually happens when the cell contains special characters or when there's a network issue. The macro skips cells with errors and continues translating.
"Macro runs too slowly"
Large sheets with thousands of cells may take time. Consider splitting your data into smaller sheets or use Doc2Lang's Excel translation service for faster batch processing.
"Translation not working" or cells show #CONNECT!
The TRANSLATE function requires an active internet connection. Check your network connection and try again. Corporate firewalls may also block access to Microsoft Translator services.
Video Tutorial
Watch the step-by-step video guide:
Need More Accurate Translation?
Doc2Lang uses AI-powered translation with context awareness for higher accuracy, preserving all formatting, formulas, and styles.