Top Tip for Google Sheets with Adrian

On your phone you use your translate but in Google sheets you can actually do it yourself, so let me just wipe out some formulas here and show you how it works. Let’s pretend that we’ve typed in something over here in English. So we’ve got hello, how are you? We want to translate it into another language which is quite easy.

You use a formula that looks kind of like well it looks exactly like this, it’s called Google Translate. Then what it will say is I want to know the text that I want to translate from and what language you want me to put it into. So in my little cell here of A2 I’ve got hello, how are you and then it says OK? What language is that?

So at the moment we know that’s in English so the little code for English is “en” and then we want to convert it to another language. Now over here on the right hand side are all the codes that Google uses for all their languages. So if I want to translate it over to say finnish, I would use an F and I. So I’m going to go back over to my little formula here and type in “fi” and this will now translate for me my hello how are you into Finnish (which is very handy for the Finnish people who will join us today so welcome).

Now that might be good but every time we do it, we have to keep on coming into here and typing in these codes. What would be nicer is if we could come in here and just pick the code that we wanted to and it would just translate it for us. Well we can do that!

Look over here because I’ve got a list of items here and I can do a special trick called data validation. I’m going to use a list from a range (grab all these ones down here) and these are all the languages I can choose. So now I’ve got a bit drop down list, I can come here and choose whatever languageI want it to convert to, look at that, that is fantastic.

So now what I’m going to do is instead of having my “fi” from my one there to work out what it’s going to translate into, I’m just going to grab this little cell in here, so to make this one work I actually used a little formula called vlookup, which we haven’t got time to do, but it’s one of the beautiful ones where it looks at the value goes to the table and grabs the number. So if I type in Basque there. It goes across here till it finds Basque and then grabs that little code there. Cool.

Now, if I come back here, all I need to do is replace this with F2 and what this will do for me now is that will now as I pick a different language, let’s go to Bengali, it’s going to then change it for me live. See how it’s changing it all, this is blowing up live for me and it’s just absolutely fantastic. So that’s really cool.

But you might be going well that’s fantastic as long as I know what this first language is. In our formula here, we’ve told it it’s English. Now Google also has this fantastic formula called ‘detect language’. So believe it or not, in here if I click on detect language in there and then put my bracket around it, it will tell me that it is French, so it’s worked out for me. To make this even better (I mean, you probably all quite excited as well at the moment) what I can do is I can put this formula here, the detect language formula and I can combine it with this formula here so whatever we write in here, Google will work out what it is, and then translate into what we want it to be. How cool is that!

If I come in here now and just copy this little bit here and come in here and then replace this bit here with that cell reference (you’ve got to put a bracket around it because that’s the way it works) go like that, then you will find that this will now read that as English and translate it to whatever language I’d like. Da..da..dah! Absolutely fantastic! So this is a really, really, really, really cool little trick and it works beautifully for you.
It’s using this lovely thing called Google Translate and combining the rest of the two together.
So I really hope you found that exciting and that’s only the tip of the iceberg. There’s much more you can do, but your kids can now go and build a Google translator straight into a spreadsheet, which I think is pretty cool.

Little trick and it works beautifully for you so it's using this lovely thing, called Google Translate#Google #translate. #utbpln. Click To Tweet

UTB Newsletter 2020 – Issue 7

Welcome to this month’s newsletter. I can’t believe another month has FLOWN by! Hopefully you are finding some time for rest, or have plans in place to get some. One of the important concepts I have learnt this year is that you need to take time to breath in.


Skate Park Pedagogy

Why in schools, do we seem to snuff out this natural enthusiasm? The level of concentration in the eyes of the young people trying to learn new tricks was supported by encouragement and advice from their peers. Natural collaboration, community and culture. The skaters controlled their learning, broke big tricks down into a series of smaller ones and slowly increased the level of “danger” as confidence grew. All controlled by themselves.


Find videos for the classroom on YouTube EDU

YouTube EDU is an educational initiative featuring more than 700,000 educational videos. It incorporates the old YouTube for Teachers channel, which was put together by a group of teachers who Google paid to watch and categorise each video according to subject and schooling stage (e.g. elementary maths). Because each of the videos


Top tips for working from home by a remote working team

  With Google Drawings a lot of people don’t realise that it actually sits inside Google Docs. Inside Google Docs you actually have the Google Drawings tool. Under insert you’ve got Drawings and now there are two places that you can get a Drawing from. The ‘New’ is going to


Here’s What
Our Clients Say

PR Industrya


In 2018, Using Technology Better designed and delivered a two phase post-migration training program for this New Zealand based graphic design firm. The initial goal of the training program was to reduce frustration with G Suite, with the long term aim of facilitating a change in culture and collaboration that can lead to transformative practices