Excel recently received an update that allows us to create reports in any language. So in this video, I'm going to explain how to create these interactive reports. And I will also show how to optimize them for performance and a way to make them work in offline mode. Oh, and I'm also going to show a technique for a double X lookup, which means I might need a new coffee cup.
So let's get to it. So in this example, I have this report and chart, and I have a list of languages here, over 130 languages. And I'll just choose French.
that will recalculate and instantly translate all of the text into French. And to make this work, I'm going to explain the setup. The first part of this is the new translate function.
So Microsoft just released a new function. We'll type equals translate here. Translate is going to translate a text string from one language into another language, and it uses Microsoft's translation service. So we'll tab into this.
The first argument is the text, and this is the text here in cell B4. So I'll just reference B4. Then we have the source language. And as you can see, there's a list of languages here. So this is the language that the text is currently in.
And it's currently in English. I'll just double click English here to make that reference. And then the target language is going to be the language that we want to translate it to. So if I want to translate this to Spanish, we'll go ahead and find Spanish here in the list. Again, double click or you can type these in.
Hit enter. And that's going to connect to the Microsoft Translation Service to do the translation and then to display the results in the cell. And I should mention that translate is currently in beta at the time of this recording. Microsoft also released the new detect language function along with translate, and I'll explain more about that later in the video. So now we'll take a look at how to create this report.
And this is a summary report that uses the new pivot by function. You can also do this with a pivot table, which I have an example of here as well. And to create those summary reports, we need some source data. And this is where we're going to do the translations.
So any columns that contain text we can translate and we're going to do that for the source column and the month column. So the first thing I'm going to do is in this cell right here, I'm just going to put the target language. So I'm going to type ES for Spanish here. And then to the right of the table, I'm going to use the translate function. And the text is going to be the source type a comma.
The source language will be English. So I'll type ES. I'm sorry, EN there in quotes, comma. And then for the target language, I can just reference the cell that contains that code. It does not need to contain quotation marks around it.
I'm going to hit F4 on the keyboard to make that an absolute reference. So as it copies down, that reference doesn't change. We'll hit enter. As you can see, the new column is added there and the translate function does the translations.
And I'm going to rename this column. I'm going to call it source X, and this just stands for translated. And then we'll also need to do that for the month column.
I can just drag the formula out to the right. And we'll call this month X and go ahead and copy that down. And so now we have two translated columns that we can use in our reports.
So the next step is to make this more dynamic so the user can select a language. Of course, right now we could just change the language code. I'll change that to French.
All of our formulas update and recalculate. But we want to make this easier to use. And so on this language list sheet over here, I have a table with all of the languages and their codes. from the Microsoft Translation Service.
And I created this table by copying it from the website, but I'll make this file available for free download and put a link in the description below so you can download this and copy this table into any of your workbooks. Because it's something you will use often if you're going to do this setup. So back over here on this sheet, right here I want to add a drop down.
I'm going to go to the data, validation, and then we're going to choose list And the source of this list, again, will be all of our languages. So we can just select this language column in the table here, hit OK. And now we have all of our languages right here. So when we choose one, we'll just go ahead and choose one here. What we want to do here is update this code.
And for that, I'm going to use an XLOOKUP. You can use XLOOKUP or VLOOKUP or whichever lookup formula you like. We're going to look up this value, type a comma. We're going to look that up in this list here in the language column, comma. and then our return array will be the code.
We can go ahead and hit enter, and then that's going to return the code here as we change the language. So as we change the language in this dropdown here, as you can see, that automatically updates the code. And then of course, the translate function also recalculates. The next thing we're going to do is create the summary report.
I'm first just going to change this to Spanish, which is a little bit easier for me to read. And then we'll jump over to the report sheet here. And right here, we're going to use the pivot by function, the new pivot by function, I have a separate video that explains pivot by in more detail. I'll link that up in the description below. So for pivot by we have our row fields that we want to specify this is from the data sheet here, the source data.
and we're going to specify the translated column. So I'm just going to reference month x here, type a comma. For the column fields we want the source across the top, so we'll specify the source column. And then for the values we'll take the quantity column, which contains numbers, comma, and then we want to do a sum. So we'll type sum in there, hit enter, and that's going to create the summary report.
And of course that's in this spill range here. Spill ranges don't have any formatting applied, so you can quickly do some formatting. We'll also quickly format these numbers here, Home tab, just do that comma formatting for now.
And then we want to have a place where the user can select the language on this sheet. So I'm going to type language in this cell here. And then what I'm going to do is just go over to this customer data sheet and I'll take the cell that contains a dropdown and hit control C to copy that, jump back to the report.
I'm just going to paste that right here. So we now have the dropdown here where we can select the language. However, what we need to do is link that or essentially link this cell. We can say equals and then link this cell to the new dropdown because this is where the user is actually going to interact with this cell.
So that will change this cell. So now if we go back over to our report, as we make a change here, we'll just make this to let's try Tahitian. Again, that's going to recalculate. We'll now see the results there.
And that's driving this cell here, which drives the lookup value to this cell. into our source data in the translate function there. And I'll quickly apply some additional formatting to just make this look a little nicer.
Our orange color for the selection there. We'll change the header row here to a light blue. Select this row down here, hit F4 on the keyboard to repeat that action.
Also gonna make that bold. And that's looking pretty good. Next, we'll add the chart.
So I'm gonna select the data here. I'm gonna exclude the total rows. Insert tab. We're going to use a line chart. And we can just drag this over here.
Everything looks pretty good with this chart. And of course, you can take some extra steps to format it however you'd like. But one thing you'll notice is the chart title is in English.
And we might want to translate this as well. To do that, over here to the right, I have a little bit of setup work where I've taken the chart title or the title that I want to be in the chart in English, sales by month, and then translate it here again using the translate function. And this is just referencing the code.
back there on the customer data sheet. So this is the translated title. Now what we can do in the chart title, we can just select it, go up here to the formula bar. I'm going to type equals, and then I'm going to reference this cell that contains the translated title. We'll go ahead and hit enter, and that's going to display it right here.
And again, this is all interactive. So if we go back over here and again, change this maybe to tie, that'll recalculate there. You can see the title translates as well. Now one interesting thing here, and I kind of chose the wrong language for this, so I'm going to go back up and choose Spanish. There we go.
You'll notice that the title is not capitalized, or at least the other words in the title are not capitalized like they are here in the original. And one thing you can do for this is add the proper function. So around our translate function, we can use the proper function. Then we go tab into proper. Proper is just going to capitalize every word in.
the text here in the phrase. So now it looks like this. Now, technically, I didn't have this second word capitalized, but I think in general, this is going to look better. And so I'd probably use proper most of the time, at least for titles and things where you have multiple capitalized words in one phrase.
So next, we're going to look at how to make this setup more efficient and also work when you're not online. And I want to warn you that this is a little bit more of an advanced technique. but it's a good opportunity to learn some new techniques, including a double X lookup.
So the translate function does require an internet connection to do the translation. You might not always have an internet connection, but we can still set this up so your users can do some translations. And when I say make it more efficient, right now what's happening here is translate is doing this translation for every single one of these cells, even though we have a lot of repeating values.
We only really have three unique values in this column. But if you have thousands or hundreds of thousands of rows, that's going to cause translate to do all those translations multiple times. And that can just be a waste of resources. So what we need to do is create a translation table.
For this, we're going to start with Power Query. Now, I have a separate video that explains Power Query in more detail. I'll link that up in the description below. But we're going to go data tab from table or range. And here I just want to keep the columns that I'm going to translate.
And for this, that's the source column. Source column right here along with the month column now power query change the data type automatically So I'm going to delete this step so we have our text in here I'm just going to hold control to select both of these columns, right click, remove other columns. So I just have these two columns, select both of those columns. I'm going to go transform unpivot columns.
And this is essentially going to create a row for the column name and then the value. So we have those combinations there. This is going to be the column name, or we can just rename this column. This value column is actually going to be the English value. So I'm going to type the English code there.
Hit enter for that, and I'll explain that in a bit. And then the last thing we're going to do is just select both of these, right-click, remove duplicates. So now we just have a unique list of all of the values that we want to translate. And, of course, we could also sort this to make it look a little nicer. We'll go ahead and Home tab, close and load.
That's going to create a new sheet in the workbook with the results. And so this is our translation table. I'm going to quickly rename the query here. So we know what this is along with this sheet name as well.
And with this translation table, what we can do is add additional columns out here for other languages. So I'll just go ahead and add one for Italian, French. We'll do Spanish, Portuguese, and let's say Filipino.
And next, we're going to translate our values into the respective languages. So this is the text that we want to translate. Comma for this, I'm going to reference cell B1. Hit F4 on the keyboard. B1 contains the English language code.
comma, then our target language. I'm going to reference cell C1. I'm not going to use table references for this.
I'm going to use regular A1 style references. F4 on the keyboard there two times to make the row number absolute, but the column relative. Hit enter there. That's going to create our formula.
We'll just go ahead and copy that across and paste it, recalculates, and we have all of our translated values in different languages. And since we've translated all the values here, we no longer need to do that in the source data. So instead of using the translate function here, we're actually going to do a lookup.
I'm gonna use X lookup for this, we'll tab into that actually going to use two X lookups. The lookup value is going to be this value here that we want to translate, type a comma. For the lookup array, we're going to go over to the translation table. And we're going to select the English column, because this is where our values exist, or the English version of those values.
We'll type a comma. For the return array, here we're actually going to do another lookup. We're going to use XLOOKUP again. We'll tab into that. And what we want to do is look up the language code that's currently being used.
So we can go over to the, back over to the data tab. We'll select this cell here. I'm going to hit F4 on the keyboard to make that an absolute reference.
Type A comma. For the lookup array, we'll jump back over to the translation table. And here I'm just going to select the entire header row or make reference to the entire header row.
We can see that reference right here, pound headers. And that's in case we add new columns out to the right, they will automatically be included. Type a comma.
The return array is going to be the entire table. Since we selected the entire header row, we also need to select the entire table. So what this XLOOKUP is going to do, it's going to look up that language code that's being used in the header row here.
When it stops and finds it, let's say it's this one here. then it's just going to narrow down the return array to just this single column right here. And it's going to narrow that down and return it back to the other XLOOKUP. So I'm going to close parentheses here.
We'll type a comma. We can just put two quotation marks here for returning a blank if nothing is found. Close parentheses on that XLOOKUP and hit Enter. And you can see we have the results right here. Now we can just copy that over to the right.
All we need to do here is change this. This is. Instead of the source we want to look up the month column hit enter and that's going to look up and return the translated values For the months so now when we change the language We'll go over to the report tab to do that and let's look at Filipino here You can see that we get the results here and again this translation has already been done in the translation table So therefore the translate function doesn't need to recalculate when we select a different language One thing to note here is that we will only be able to use languages that are in our translation table.
So if we try German, for example, we don't have that in our translation table. So it's going to show an error. So what you'd want to do is just narrow down the list here to only the values you have, or only the languages that you have in the translation table.
I'll go ahead and hit Ctrl Z to undo that. Now the advantage of using Power Query for this is that we fully automated this process. When we get new data or if our data changes, let's say here we just have a change in the data, it will change us to event instead of retail.
We don't have that word in our translation table yet. So XLOOKUP is just returning an error because it can't find it in our translation table. but all we need to do is go over to the translation table right click refresh again this is based on the source data that we have it'll find event as one of the unique values there add a row for it do all of the translations here out in these columns and if we jump back over to our source data we can see that that has now been translated right here and if we jump back over to our report our report's also going to expand to include that new column And of course, we'd also need to change our chart here, our chart source, along with the formatting to fully reflect that.
Earlier in the video, I mentioned that you can also use a regular pivot table for this, which makes the setup easier and the formatting. However, when you change the language, you'll notice that nothing happens here. And that's because the pivot table needs to be refreshed. You can right click refresh keyboard shortcut is all F5. Once you do that, then we'll see the new data here.
And of course we have it in the pivot table, the pivot chart, and any connected slicers as well. So the user will need to know that they need to refresh once changing the language. Of course, you could automate that with a macro or provide some instructions here, but the pivot by function might just be a little bit more fluid in this case, even though it requires a bit of extra setup.
And I wanted to mention that you can use this entire setup, even if you don't have the translate function yet. The way you can do that is just by taking your list of unique values that you want to translate in your language, your original language. I'm going to hit Control-C to copy that. And then you can jump over to Bing Translator or Google Translator.
Just paste the text in here. And then you get the text or the language you want to translate that to. Copy that. Jump back over to Excel. And then paste it in the translation table.
So it'll take a little bit of manual work to build that out or do those lookups for each of the languages. But once you have that, the XLOOKUPs will still work to just look into that translation table, and you can still create these reports in several different languages. And as I mentioned earlier, this update also comes with the new DETECT LANGUAGE function. So if you come across some text in a language that you're not familiar with, you can just reference that in the formula, and then this is going to return the language code. Now, a lot of times you won't recognize this language code, or you won't know what language this is.
One little trick here is you can use translate for this. So I'll just tab into translate, hit a comma, and that's going to show me the language list here with all of the codes and the name of the language. So you can find it in this list. However, this list is not in alphabetical order by the code.
So one other thing that you can do, and one reason I like to have this language list table is because you can just hit control F here to find it, or you could even sort these to find the language code and then determine what language it's in. So I still think translate is the one I'll be using most often, but it's great that we also have detect language here as well. I'm curious to know which one you'll be using most often, so leave a comment below and let us know. So that's a way to enhance your Excel reports with the new translate functions. If you enjoyed the video, hit the like button and subscribe for weekly Excel tutorials.
And if you want to learn other ways to enhance your Excel files, then check out this video next. Thanks again for watching, have a great day, and I'll see you in the next video.