Working in a global, multilingual business environment often requires collaborating on Excel spreadsheets in different languages. Fortunately, Excel provides built-in translation capabilities to help bridge language barriers.
In this comprehensive guide, we’ll explore the various ways to translate in Excel, with simple step-by-step instructions and examples.
Why Translate in Excel?
Here are some common scenarios where translating Excel spreadsheets becomes necessary:
-
Sharing financial reports, sales data, inventory levels etc with international teams or affiliates
-
Consolidating data from global operations into a unified Excel model for analysis.
-
Sending Excel templates and dashboards to foreign clients and partners.
-
Preparing Excel training materials and tutorials for non-native speakers.
-
Receiving Excel files from international colleagues that need translation,
-
Migrating data during offshoring of operations to other countries.
-
Supporting a multicultural workforce with diverse language needs
Excel’s Built-in Translation Feature
Excel has a straightforward translation utility built into the Review tab:
To use it:
-
Select the cell(s) containing text you want to translate.
-
Go to Review > Translate.
-
Choose source and target languages in the sidebar.
-
Copy over the translated text manually.
However, this has some major limitations:
- It won’t translate an entire sheet at once.
- Formulas and functions can’t be translated.
- Translated text doesn’t automatically flow back into the sheet.
So for anything more than quick one-off translations, we need more powerful solutions.
Translate Excel Sheets with Microsoft Translator
Excel documents can be translated in bulk using Microsoft Translator integration:
-
Copy the entire Excel sheet and paste it into a blank Word doc.
-
Go to Review > Translate and choose languages.
-
Click Translate Document. Entire content will be translated.
-
Copy back the translated table into Excel from Word.
This leverages Word’s full document translation while retaining Excel formatting. Much easier than cell-by-cell!
Install the Excel Functions Translator Add-in
To translate Excel formulas and functions across language versions, use the Functions Translator add-in:
-
Go to Insert > My Add-ins > Get Add-ins.
-
Search for “Functions Translator” and install.
-
Select language pairs – e.g. English to Spanish.
-
Click inserted add-in icon and input formula to translate.
-
Copy back into sheet.
The add-in dictionaries can translate 50+ languages. It’s ideal when localizing formulas.
Use the Google Translate Excel Add-in
For easy in-sheet translations, install the Google Translate add-in:
-
Get it from Insert > Add-ins > Search “Google Translate”.
-
Highlight cells and use add-in toolbar to translate in 90+ languages.
-
Translated text automatically flows back into your sheet. No copy-pasting needed!
The Google Translate integration is quick, convenient, and free. Great for everyday translations.
Best Practices for Translating in Excel
Follow these tips for smooth translations in Excel:
- Use consistent formatting and structure to retain layouts when translating.
- Break down large complex sheets into smaller sections for easier translation.
- Validate translations of key terms against industry glossaries.
- For critical legal/financial docs, use professional human translation services.
- Add alternate language sheets rather than overwriting originals.
- Test thoroughly after translating formulas, number formats, and data.
- Use translation version control and change tracking to enable audits.
- Don’t translate images or embedded objects – replace with translated versions.
- Localize sheets for region-specific number/date formats, currencies etc.
Translating External Excel Data Sources
In addition to worksheet content, any external data connections may also require translation.
Translating imported/exported data:
- For CSV/text imports, translate source file in Word before importing to Excel.
- For Excel linked workbooks, translate each workbook separately before linking.
- For exports, translate Excel data first, then export as CSV/text.
Translating data from databases and web:
- Use SQL SELECT statements on database views to retrieve translated text strings.
- For web data, pass retrieved content through translation APIs before loading into Excel.
Automating Excel Translation Workflows
Translating Excel sheets manually is time consuming. Automation can help:
- Use Power Query to load, translate and refresh external data flows.
- Build VBA macros to programmatically iterate and translate cell ranges.
- Develop Excel Add-ins with translation APIs like Google Cloud Translation to translate on the fly.
- Use DAX in Power Pivot to create a separate translation table for common terms.
Automated solutions scale much better than manual processes for recurring translations.
When Machine Translation Isn’t Enough
While Excel’s built-in and third-party translation tools work well for gisting, sometimes getting high quality human translations is advisable:
- Legal agreements, financial statements, and other binding documents.
- Public facing materials like sales proposals and marketing content.
- High value research reports, business plans, and technical specifications.
- Anything where mistranslation could have significant costs or consequences.
Professional translators provide greater accuracy, industry expertise, and regional nuance. Their services are worth investing in forExcel translations with high impact.
Key Takeaways
The steps we’ve covered enable translating Excel to be a breeze. To quickly recap:
- Use Excel’s Review tab translator for quick one-off cell translations.
- Paste sheets into Word for high fidelity bulk translations.
- Get the Functions Translator add-in to localize formulas across languages.
- Add the Google Translate add-in for in-sheet real time translations.
- Automate translation workflows with Power Query, VBA, and APIs.
- Use human translation services for critical communications and documents.
With the right technique for each use case, language barriers don’t have to get in the way of effective collaboration and global business processes.
You now have all the tools to seamlessly translate in Excel and unlock new levels of productivity when working across languages. Time to go forth and translate!
2 Answers 2 Sorted by:
Workaround: Do the translation of the Excel table in Word
If you have a wide table with many columns, you might better change the layout landscape, large size, and narrow borders, but I have not done so and it should be unimportant since we will not work with that Word file anyway, we do not even need to read anything of it.
Copy the full spreadsheet into word with a normal Ctrl+V, then hover over the pasting method that pops up directly after insertion at the bottom right or press Ctrl. Choose not the standard insertion method, but the one that uses the destination format styles (the second one in my case):
Then, the Excel table will be pasted with its table structure in Word. Mind that this may take quite some time to react. After that:
- Review –> Translate –> “Translate document” and choose the needed language. This again might take some time (10000 words need a few seconds). A new word file with the translated table will pop up.
- Mark the translated table in the new Word file and copy it back to Excel, choosing to adapt to the destination format.
I found a trick. Microsoft Word has translate document function. If you copy whole columns to Word, you can translate with 1 click. Then you can paste it back to Excel.
I hope it helps
