How to translate a full excel sheet to another language with the Microsoft Translator?

Question

Following the guide at Best Ways to Translate an Excel File - 2021 Update , clicking on the Microsoft Translator ribbon under "Review", I could not find the "insert" button to insert the new translation in the cells. Instead, I would have to manually copy the translation to each cell since copying the full translation text of many rows does not keep the original spreadsheet's row structure.

Here is a full copy from the guide:

Microsoft Excel has a built-in translator you can use for simple translations (same machine translator can be found within Word when translating Word documents and PowerPoint presentations).

First, open your Excel spreadsheet and select the text you wish to translate. Then, on the toolbar, select Review > Translate.

The translator menu will appear on the right, where you can set the source language and the target language.

Keep in mind that the translation will only appear on the menu. It does not automatically replace the original text in the Excel file. To prompt this, you must click Insert or manually copy-paste the translated segments into their respective cells.

There is no such "Insert" button. This is also said officially in the Microsoft Community at The Insert button in Translate is missing in Excel for Mac ver 16.47.1. Strangely, in this Microsoft guide, you see this "insert" button in Excel:

Yet, even if there was such an "insert" button by some trick, the built-in Microsoft Translator cannot translate a large table. In my case, the translation that appeared on the right could only handle eight rows (with a lot of text in them, admittedly), so that I would need to do manual work again.

How to get the whole Excel spreadsheet translated using Microsoft Translator without manual work?

Solution

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 right bottom 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.