Living in a new country where a different language is spoken is something that I can barely fathom. Doing it as a child just blows my mind. Our schools are often ill-equipped to support these kiddos, but they often do have one powerful tool that can help: compassionate teachers.
One of these compassionate teachers once came to me looking to create a tool to support an ELL (English Language Learner) student. We both knew that we worked with a great ELL Tutor who was helping this student assimilate to the school . . . but what about the vocabulary that was being learned in the meantime?
His idea was to organize important English vocabulary words and their translations to the student’s native language into a spreadsheet. After he came to me with this idea, I started exploring options. What I discovered was really exciting! There’s a Google Translate formula in Google Sheets!
Enter a word in one language in a cell, and then use the formula =GoogleTranslate(text, source_language, target_language) in another cell to automagically translate it! Translating one word this way doesn’t save my time, but you can drag (or double-click) the fill handle at the bottom of the formula cell to apply this formula to more than one cell.
This formula appears to work for all languages supported by Google Translate, of which there are more than 100! It even outputs the results with the correct letters and alphabet–not just our ABC English letters. You’ll just need to know the 2-letter code for the language, which you can find in this list.
A note: as with using the regular Google Translate site, the results are not 100% accurate, but they’re close. This method is free, fast, and easy. There may be other options that can yield better results.
Check out how to do it in the animation below and then read the steps below the EduGIF!
- Type a word into 1 cell.
- In another cell, enter the formula =GoogleTranslate(
- Click on the cell that contains the original word. This will make the formula say =GoogleTranslate(A2, where A2 represents the cell that your first word was in.
- Now type the 2-letter code for that original word in quotation marks. For English, it’s en. You can also use auto to let Google Sheets figure it out for you. Add a comma. You should now have something like =GoogleTranslate(A2, “en”,
- Now enter the 2-letter code for the language that you’d like to translate to. A full list of codes is available here. In my example, I translated my word to Spanish using =GoogleTranslate(A2, “en”, “es”)
- You can drag the fill handle down to apply that formula to multiple cells. If the cells are consecutive, you should be able to double-click the fill handle to do it even faster.
This post was updated on March 27, 2022. A new EduGIF was added (here’s the original), the step-by-step instructions were added, and the post text was edited slightly.
This is brilliant! Such a pity Excel won’t do it as well. Will be using with my Polish boys on Monday! Thank you
Woohoo! I’m so glad that you found this to be beneficial!!
Thanks! With over 70 languages spoken in our school, this will be helpful. I will pass it on to staff.
Do you happen to have a video on how you create the animation?
Hi! I’m glad that this animation will help your students!! I use Camtasia to create all of my GIFs. Later this week, I’ll be announcing an online course about this GIF creation process.
This is a wonderful tool. Is it possible to show both the traditional scripts and the English phonetic letter analysis? Many of our kids have oral but not written competence in their languages and this would help as we try to encourage them to use their home languages more.
I’m not sure what you mean… Can you give an example?