Excel spreadsheet translation tools – Documentation

Excel spreadsheet translation tools icon Extract Translatable Text and Translate using Translation Table tools provide an easy workflow for translating all translatable text (cell values and textbox contents without repetitions) using a CAT tool or with the help of a standard two-column translation table.

Detailed information about Extract Translatable Text and Translate using Translation Table tools

How Extract Translatable Text and Translate using Translation Table tools can help you in your work

Extract Translatable Text and Translate using Translation Table tools help you translate large spreadsheets containing repetitive text, such as financial reports, datasheets, etc. Extract Translatable Text tool extracts all non-repetitive text from cells and textboxes into a translation table in Word or Excel format, while Translate using Translation Table tool merges the translations from the final translation table back into the spreadsheet. This workflow is very useful for translators who do not use a CAT tool, and may be useful for CAT tool users who encounter an error when handling a large spreadsheet in their CAT tool.

Where to find the tools

To run Extract Translatable Text and Translate using Translation Table tools, select the relevant menu item from the Translation menu located on TransTools ribbon in Excel:

Excel translation tools on the ribbon

Using Extract Translatable Text and Translate using Translation Table tools

Workflow overview

Step 1: Run Extract Translatable Text tool. Select whether to extract text from one or more worksheets within the current workbook (check the required worksheets) or selection (this may be a range of cells, a textbox or a group of shapes containing textboxes). Click Continue.

Screenshot: Using Extract Translatable Text

Step 2: A new workbook (translation table) will be created. The 1st column contains unique text (cell values, textbox values) from the worksheets or selection. The 2nd column should be filled with translation of the text in the 1st column. The 3rd column shows whether the text was extracted from a cell or from a textbox (as part of a shape).

Screenshot: Generated translation table

Step 3: Translate the second column of the translation table. When it has been translated, select a range of cells from the 1st and 2nd columns. Press Ctrl+C or another key combination that copies text to the clipboard depending on your system. Alternatively, click Copy button in Clipboard group on the Home tab.

Screenshot: Copying to clipboard

Step 4: Switch to the workbook where replacements should be made. Run Translate Using Translation Table tool. Select worksheets to process or check 'Selection' to process the selection in the active worksheet only (may be a range of cells, a textbox or a group of shapes containing textboxes). Change filtering options as explained below. Click Paste next to the textbox below to paste the data from the clipboard (or press Ctrl+V or another key combination for pasting from clipboard depending on your system). Select 'Highlight replacements." – this will change the background colour of all replaced cells/textboxes so that you will be able to format them when translation is complete (this is necessary because the cell formatting may prevent you from seeing the entire cell contents).

Step 5: The “translated” cells will be highlighted (if the 'Highlight replacements' option was selected).

Screenshot: Translate Using Translation Table results

Once all worksheets have been checked, highlights can be removed by running Remove Highlight or manually by setting cell/textbox fill color to 'No Fill' (show how to do this).

Important notes about the workflow

  • The tools do not process text in embedded objects (embeddings) and Excel charts.
  • If the cells in your Excel spreadsheet have internal formatting (i.e., some words are formatted differently than other words in the same cell), this formatting will not be retained after you import the translation table into the spreadsheet with Translate using Translation Table tool. You will need to restore such formatting manually.
  • When you translate the spreadsheet using Translate using Translation Table tool, you will not be able to undo the operation or any actions performed before it. Save the spreadsheet before using this tool and make sure that you have a back-up copy of the spreadsheet.

Extracting translatable text using Extract Translatable Text tool

To extract translatable text from the current spreadsheet, run Extract Translatable Text tool as described above. You will see the following dialogue:

Screenshot: Extract Translatable Text dialogue

The dialogue has the following options:

  1. Worksheets: Select this option to extract text from one or more worksheets in the current (active) workbook. Check the appropriate worksheets or click All / None to select / de-select all worksheets in the list.
  2. Look in print area only: If this option is checked, the command will only process cells in the print area. This is useful if your spreadsheet contains a lot of text, but you only need to translate text that will be printed.
  3. Selection: Text will be extracted from the selection (may be a range of cells, a textbox or a group of shapes containing textboxes).
  4. Formulas: Extract text from cells that contain formulas (i.e. instead of formulas the command will extract the resulting text).
  5. Process hidden groups and outlines: Sometimes the Excel spreadsheet will contain grouping and detail rows/columns.

    Screenshot: Hidden groups and outlines in Excel

    By default, if the worksheet contains grouped (hidden) detail rows/columns, the command will extract text from them. If you would like to exclude hidden detail rows/columns from the operation, uncheck this option.

  6. Process cells with specified font color: This option will allow you to process only those cells whose font color matches the specified color. For example, you may format cells that need to be processed with a specific font color, and then use this option to collect text only from these cells. This allows you to translate spreadsheets where only some of the text requires translation. This option is off be default.
    Note: The entire cell must be formatted with the specified color, not just a few letters.
  7. Translation Table Format: Use the options under this tab to configure the format of the translation tables which will be generated.



    You can select the desired format of the translation table (Microsoft Word or Microsoft Excel) and whether to output additional information in the generated translation table (file name, file path, column headings, type of text object).

Click Continue to complete the process. Click Cancel to exit the dialogue.

Translating the translation table

There are several efficient ways to translate the translation table:

  • If you do not use a CAT tool, simply translate the source text inside the 2nd column.
  • If you use a CAT tool:

    • If the translation table is in Word format, copy the translatable cells from the 1st column to the 2nd column and highlight them in yellow color, then hide everything except the highlighted text using Hide/Unhide Text tool included in TransTools for Word. Translate the document in your CAT tool (the CAT tool should ignore the hidden text unless your CAT tool does not support hidden text). When the translated document is exported from the CAT tool, unhide previously hidden text using Hide/Unhide Text tool.
    • If the translation table is in Excel format, copy the translatable cells from the 1st column to the 2nd column, then hide rows 1 to 3 and all columns except the Translation column. Translate the document in your CAT tool (the CAT tool should ignore hidden rows and columns by default). When the translated document is exported from the CAT tool, unhide the first column so that you can see both source and translation columns side by side in order to be able to copy both to the clipboard.
    • If your CAT tool does not support hidden text in Word or hidden rows/columns in Excel, you can copy the translatable cells from the 1st column into a separate document, translate it, then paste the translations into the 2nd column of the translation table, making sure that the source and target cells align correctly.

Translating the spreadsheet using Translate using Translation Table tool

When the translation table has been translated, select a range of cells from the 1st and 2nd columns and copy the range to the clipboard using Ctrl+C key combination or Copy command in Word/Excel. Then switch to the spreadsheet that needs to be translated and run Translate using Translation Table tool as described above. You will see the following dialogue:

Translate with Translation Table dialogue

  1. Worksheets – Select one or several worksheets that need to be processed in the active workbook or click All / None to select / de-select all worksheets in the list.
  2. Look in print area only: If this option is checked, the command will only process cells in the print area. This is useful if your spreadsheet contains a lot of text, but you only need to translate text that will be printed.
  3. Selection – Select this option if you want to process the selected range (in the active workbook) only. Make sure that the range is selected prior to running the 'Translate Using Translation Table' command.
  4. Translation Table – The translation table should be entered here. There are two ways to insert text into this box:

    • Use the translation table generated by the Extract Translatable Text tool and fill the second column with the translation of text in the first column. Once the translation table is fully translated, copy both columns to the clipboard and click Paste or press Ctrl+V (or another key combination assigned on your system) – this will paste the text from the clipboard.
    • Enter the translation table manually by typing the text you want to translate, then Tab key, then the translation. If you need to insert a line break, use <br>. Press Enter to insert a new line. Continue until the translation table is complete.
  5. Process hidden groups and outlines: Sometimes the Excel spreadsheet will contain grouping and detail rows/columns.

    Screenshot: Hidden groups and outlines in Excel

    By default, if the worksheet contains grouped (hidden) detail rows/columns, the command will replace text in them. If you would like to exclude hidden detail rows/columns from the operation, uncheck this option.

  6. Process cells with this font color: This option will allow you to process only those cells whose font color matches the specified color. For example, you may format cells that need to be processed with a specific font color, and then use this option to replace text only in these cells. This option is off be default.
    Note: The entire cell must be formatted with the specified color, not just a few letters.
  7. Highlight replacements with this color – this option allows you to highlight the cells or textboxes where replacements were made. This is done by changing the background colour of these cells/textboxes. Make sure that you pick a colour which is not used as a background colour within the worksheets / selection. Use 'Remove Highlight' command to remove this background color (reset to White / Transparent) when you are done with the formatting of the translated spreadsheet.

When the appropriate options have been selected, click Replace All. To exit, click Close.

The command's progress will be shown at the bottom of the dialogue.

Notes

  • Because the translated text may be longer than the source text, it is strongly advised to go through each worksheet that was translated with this command and format each cell individually:

    Use 'Highlight replacements in' option to make it easier to find the cells that were changed by the tool.

  • When you copy a range into the clipboard, make sure that you do this immediately before you run Translate Using Translation Table tool. If you copy a range to the clipboard and then perform another formatting action in Excel, the program may clear the clipboard. Sometimes the clipboard will also be cleared by Microsoft Excel after the text has been pasted into the Translation Table field of 'Translate Using Translation Table' dialogue.