If you ever wondered how to translate excel spreadsheets into another language.
This blog will give you a clear guidance on how to do exactly that.
Translation is more than just changing words from one language to another. Translation builds bridges between cultures.
English is a language that is pretty much everywhere. However, solely operating in English can hold back companies and businesses.
There is no question that English is a widely spoken language. A lot of those numbers, though, are made up of people who speak English as a second language. This would mean that most people would actually respond better if they were spoken to in their native language.
These people do understand and comprehend English. They have no problems piecing together words to form a sentence in response to whatever you are asking them. But until you speak the language that their heart speaks (their native language), you won’t really be communicating with them in the most optimal way
Most people simply prefer their native language. It is what they are most comfortable with and it shows in their confidence when they’re speaking. This is why we need translation; it will allow people to communicate more effectively.
With all that being said, it can clearly be seen that translation is more important than the attention and credit it is given. So many people around the world have already benefited from the effects of good and clear translation with its importance is becoming increasingly more well-known today.
In this article I will explain how to translate in Excel with 3 different options:
Table of Contents
Translation through the Review Tab
In this example, I have in column A some words in English that I want to translate into 5 different languages.
The Translator pane opens on the right side and shows the selected text in the upper box. English language is automatically detected.
From the lower drop list select the target language, I selected French. Text is instantly translated
Put the mouse cursor in the lower box and hit CTRL + A to select all the translated text, then copy it CTRL + C
Pasting the translated text in cell B2, will result in pasting all the words in one single cell.
To solve the problem, Double click in Cell B2 (the Enter mode of Excel) ► Paste CTRL +V ► Then select all CTRL+A ► Cut the selected translated text CTRL + X ► deselect cell B2 by clicking on a different cell ► Re-select cell B2 ►and Paste CTRL + V … voila. Each word appears in a different cell.
Now, repeat the process for each one of the other languages.
Translating Functions
Translating functions follows a totally different process.
We start by adding a Microsoft Add-in called Functions Translator.
On the Insert Tab click on Get Add-ins.
Click on “Store” ► Select “Productivity” ► then scroll down to “Functions Translator” ► Click Add.
A window opens ►Click continue to accept the License terms
The tool is added to the right side of the Home Tab, showing 2 options Reference & Translator (both will open the same pane). Click on Reference.
The first time you use the functionality, a Welcome screen allows you to select the languages. You can change the language later one by clicking on the gear icon in the lower right corner of the reference tab.
I have English selected as a source language and German as a target language.
The Reference tab also allows you to select a Function category ► Financial Functions are shown along with the corresponding name in German.
When I clicked on the PMT function ► it switched to the “Dictionary” Tab where it shows a description of the selected function.
To translate any function (including nested functions) go to the “Translator” tab.
In the upper box, type your function (You cannot click on cells in the worksheet to use them as references). Alternatively, if you already have the function in the worksheet, copy it from the formula bar and paste it in the same box.
You can download the exercise file and follow along by clicking Here.
In the “Functions” Worksheet, I copied the PMT function in B5 from the formula bar and I pasted it in the box ► Click on the down pointing arrow for the Translation direction ► you get the translation in German in the lower box
Since delimiters vary from one language to another, you have a set of controls for changing the delimiters.
Test with a different function (the LEFT function) in cell B9.
Test with a Dynamic Array function (TEXTAFTER) in cell C9.
Note if you click on the Replace button to replace the original function with the translated one, this action cannot be undone. But you can always click again on the upper replace button to bring the function in the original language.
But what about nested functions? They work perfectly fine.
I have in cell F19 a VLOOKUP function with a nested CHOOSE function. I copied it from the formula bar and pasted it in the upper box ► I clicked on the down arrow for the Translation direction and the result is amazing.
If at anytime you don’t want the Functions Translator in Excel, you can remove it by going to the Insert Tab ► Click on My Add-ins ► The Office Add-ins dialog box opens ► Select Functions Translator ► Click on the ellipsis ► Click on Remove ► Confirm by hitting Remove ► Then close.
Dynamic Translation using Power Query
Notes:
- You can Download the exercise file and follow along by clicking Here.
- I learned the basic concept for this project from the MrExcel message board. Here is a link to the original post:
- https://www.mrexcel.com/board/excel-articles/using-power-query-inexcel-to-translate-data-using-the-google-translation-api.33/
- I modified the M code to make it more useful and dynamic. • I am using the PQ worksheet from the exercise file.
Preparation:
In this example, I have the data to be translated from the source language English (sl) to any other language I select, in a table structure in Column A. Table is named “Shorts”
I also created a second table in column C (C1:C2) named “Language” for the destination language, or the Target language (tr). C2 is a drop list, that allows me to select any target language.
In cells C4:C5 I created a third table that returns the abbreviated code of the selected language in C2. I named the table “Code” and I used a VLOOKUP function to return that code from a table array named “AllLanguages”.
=VLOOKUP(C2,AllLanguages,2,0)
Here is the “AllLanguages” named range in a hidden sheet named “Data”
You can get the full list of ISO-639.2 codes from the url:
The Concept
Load the “Language” table to power Query. Data Tab ► From Table Range ► Right click the single value and select ► Drill Down ► you get a single text value.
On the Home Tab of the query editor : Close & Load To ► Only Create a connection
Repeat the previous steps for the “Code” table. Drill Down ► Load as a connection only.
Solution
On the Data Tab ►Get Data ► From Other Sources ► Blank Query.
Name the Query ”Translate”.
On the Home Tab of the Query Editor ► Click on Advanced Editor ► Delete everything ► Then copy and Paste this code.
let
fnTranslate =
(Shorts as text) as text =>
let
Source = Json.Document(
Web.Contents("https://translate.googleapis.com/translate_a/single?client=g
tx&sl=en&tl=" & Code & "&dt=t&q=" & Shorts)
),
Translation = Source{0}{0}{0}
in
Translation,
Source = Excel.CurrentWorkbook(){[Name="Shorts"]}[Content],
ChangeDataType = Table.TransformColumnTypes(Source,{{"English",
type text}}),
Result = Table.AddColumn(
ChangeDataType,language,
each fnTranslate([English]),
type text
)
in
Result
Let’s break down this code:
A table is sent from Excel to Power Query.
Power Query connects to Google Translate through an API (Application Programming Interface), a software that allows 2 applications to interact with each other.
The same API reads data from the webservice which returns translated data in JSON format (JavaScript Object Notation).
Since it’s a free API, limit the number of requests by not refreshing the query often and keeping the translation table short.
To access the Free Google Translation API use the URL:
https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=fr&dt=t&q=%22Text
In this URL there are 3 parameters:
Sl ► stands for Source language ISO code.
Tl ► stands for Target Language ISO Code.
Q ► is the text to be translated
The API endpoint returns a JSON file (in array format) that contains French translation(fr) for text given in English
We rename this array Source
let
Source =
Web.Contents("https://translate.googleapis.com/translate_a/single?client=g
tx&sl=en&tl=" & Code & "&dt=t&q=" & Shorts)
in
Source
Power Query will parse the content of the JSON file and then loads it into Excel after some transformation, to present the corresponding translation in tabular format.
We need to parse this JSON file to access the translated text.
Json.Document is the function which is responsible of parsing the JSON content. The result is stored in a variable “Translation”
let
Source = Json.Document(
Web.Contents("https://translate.googleapis.com/translate_a/single?client=g
tx&sl=en&tl=" & Code & "&dt=t&q=" & Shorts) ),
Translation = Source{0}{0}{0}
in
Translation,
Note, Source{0}{0}{0} is the first item in the translated text we want to get.
As soon as we run the query, we will be notified to specify how we would like to connect to the web service unless you already defined permission for the Google Translation API endpoint. Click on the Edit Credentials button ► I selected Anonymous
To make this code reusable, I encapsulated it in a Custom function fnTranslate(). We can create the custom function in a separate code and call it in other queries.
Here is my code where I replaced: the target Language by the query named Code, the name of the source table by Shorts, and the column header of the translated text by the query Language.
let
fnTranslate =
(Shorts as text) as text =>
let
Source = Json.Document(
Web.Contents("https://translate.googleapis.com/translate_a/single?client=g
tx&sl=en&tl=" & Code & "&dt=t&q=" & Shorts) ),
Translation = Source{0}{0}{0}
in
Translation,
Source = Excel.CurrentWorkbook(){[Name="Shorts"]}[Content],
ChangeDataType = Table.TransformColumnTypes(Source,{{"English",
type text}}),
Result = Table.AddColumn(
ChangeDataType,Language,
each fnTranslate([English]),
type text
)
in
Result
Close and Load To the same Excel worksheet in cell E1
Test by changing the destination language from the drop list in Excel. Every time we select a different target language we need to refresh the query either by:
- Clicking on Refresh All on the Data Tab
- Using the shortcut: CTRL + ALT + F5
- Right-click the sheet tab ► View Code. Copy and paste the following Change Event Code.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C5")) Is Nothing Then
ThisWorkbook.RefreshAll
End If
End Sub
Watch the Full Tutorial on my YouTube channel by clicking here below.
As I am not familiar with all these languages, I wish to hear back from you in a comment; how accurate the translation is.
If you enjoyed the article and the accompanying Video tutorial share it on your social platforms for the benefit to spread.
