'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Translate Text in Excel Without Losing Table Structure
Question details
The user needs to translate text within Excel cells while keeping the data separated properly in a table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Translating multiple cells or an entire table into another language directly within a worksheet.
- Observed behavior
- Copying and pasting translated content from an external web application collapses the text into a single sentence, destroying the original table structure.
Ensure you have an active internet connection, as built-in Excel translation features require online access to process language data. Verify your Microsoft 365 version to check if the new TRANSLATE function is supported on your device.
Use the Built-in TRANSLATE Formula
For users with newer Microsoft 365 versions, the TRANSLATE function allows for seamless inline translation without breaking the grid format.
The TRANSLATE function is a dynamic array formula that can translate text directly inside your worksheet. By dragging the formula down a column, you retain the exact table structure of your original data.
Click on an empty cell adjacent to your table where you want the translated text to appear.
Type =TRANSLATE(A2, "en", "fr") into the formula bar. Replace 'A2' with your source cell, 'en' with your original language code, and 'fr' with your desired target language code.
Press Enter. Once the translation loads, click the bottom-right corner of the cell and drag the fill handle down to apply the translation to multiple cells automatically.
Translate Using the Review Tab's Translator Pane
If the TRANSLATE formula is not available in your version of Excel, use the built-in Translator pane to translate large ranges while preserving table structure.
Translate Spreadsheets Seamlessly with WPS Office
WPS Spreadsheet features a powerful built-in translation tool that makes converting text across different languages fast and reliable. You can translate specific cells or entire documents without compromising your table structures.
- 1. Open your spreadsheet: Launch WPS Office and open the spreadsheet containing the text you want to translate.
- 2. Access the Review tab: Click on the 'Review' tab located on the top menu ribbon.
- 3. Open the Translate tool: Click the 'Translate' icon to open the translation sidebar on the right side of the screen.
- 4. Apply your translation: Select the cells you wish to translate, set your target language in the sidebar, and apply the translated text back into your worksheet.
Frequently Asked Questions
Why does my translated text paste as a single long sentence?
When you copy text from an external browser-based translator, the copied content often loses the tab-delimited formatting required by Excel to separate text into individual cells. Using Excel's built-in translation features preserves these delimiters.
Is the =TRANSLATE function available in older versions of Excel?
No, the =TRANSLATE function is a newer feature and is typically only available to users with current Microsoft 365 subscriptions. If you are using an older version, you should use the Translator pane found under the Review tab.
Can I use Power Query to translate text in Excel?
Yes, advanced users can use Power Query to connect to an external translation API (like Google Translate or Microsoft Translator APIs) to process text. However, this requires setting up API keys and writing custom M code, making it less accessible for quick translations.
