TRANSLATE function

Translation in Excel documents is not a new feature, and with the advent of artificial intelligence, it works incomparably better than before and can be said to be competitive with the Google Translate service. Recently, we also got a TRANSLATE function that translates text from one language to another, and allows you to create reports that are dynamically translated.

The syntax of the TRANSLATE function is as follows:

TRANSLATE(<text>, [<source language>], [<target language>])

Let’s see how this function is used in practice. If we write a sentence in cell A1, and in cell A2 the expression:

=TRANSLATE(A1,” sr-latn”,” en”)

we will get the correct translation from the source to the destination language. The source and destination languages are specified as a string with the system name of the language being translated.

This formula is dynamic, so if you change the sentence in cell A1, the translation in A2 will change as well. Excel translates by connecting and searching values to the Internet.

Let’s “complicate” things a bit. With the Data Validation  option, we will set the value to be selected in cell E1, and that users can choose “EN” if they want the title and table headers to be in English or “SR” if they want them to be in Serbian. We will also create an auxiliary table with the data from the list, and the system name of the language to be translated.

Next, for the title, as well as for each cell in the header, we will specify the expression in which the word in the cell is translated from English into the language selected from the list in cell E1. For example, in cell A1, the following should be written:

=TRANSLATE(“Analiza prodaje”,$H$2,VLOOKUP($E$1,$G$2:$H$3,2,FALSE))

In cell A2:

=TRANSLATE(“NAZIV ARTIKLA”,$H$2,VLOOKUP($E$1,$G$2:$H$3,2,FALSE))

As a result, if we select the value “EN” in E1, the title and table header will be translated from Serbian to English.