3 Ways To Translate Languages In Google Sheets (Easy!)

Do you ever find yourself struggling to understand foreign language text in your sheets?

The GOOGLETRANSLATE formula is here to save the day! It not only provides efficient translations, but in most cases, it also eliminates the need for any additional software or tools.

But what if the GOOGLETRANSLATE formula isn’t cutting it?

In this article, I’ve got a few alternative translation methods up my sleeve that will have you translating like a pro in no time.

Before I wrap up, don’t forget to check out the helpful tips I’ve included at the end of this article! You’ll find some great advice on how to prepare your sheet for translation. If you’re new to the world of translation, don’t worry – I’ve got you covered.

Understanding the Google Sheets Translate Formula (GOOGLETRANSLATE function syntax)

The Google Sheets Translate formula syntax is simple and takes three arguments:

=GOOGLETRANSLATE(text, source_language, target_language)

Where:

• text: represents the content you want to translate
• source_language: is the language code of the original text
• target_language: is the language code of the desired translated language.

Additional notes:

• Both source_language and target_language should be provided in the ISO 639-1 two-letter language code
• If you’re unsure of the source language or want Google Sheets to detect it automatically, you can use the “auto” option as an argument like so:

=GOOGLETRANSLATE('text to translate', 'auto', 'fr')

alternatively, you can use the GOOGLEDETECT function to detect the language first.

Translating Text in Google Sheets

Here I prepared a step-by-step guide on how to use the translation formula

Step 1: Open a new spreadsheet or the working sheet I prepared (if you’d like to follow along)

Step 2: In A column create a “default language” header and add some phrases for translations below

Step 3: In column B create a “french translation” header and enter this formula: =GOOGLETRANSLATE(A2, "en", "fr")

Step 4 (Optional): If you want to translate the entire column, click on the blue handle and drag the formula down to apply it to all cells.

Step 5 (Optional): If you want to translate the text into multiple languages simply repeat Step 3 for each language you want to translate to.

Using DeepL for Translation in Google Sheets

Step 1: Open your Google Sheet, and click on the “Extensions” menu at the top. Then, select “Apps Script” to open the script editor for Google Sheets.

Step 2: Create a new script file by naming it “DeepL.gs”. Copy the contents from this file

Step 3: Locate line 26 in the script, which should look like this:

const authKey = "b493b8ef-0176-215d-82fe-e28f182c9544:fx"; // Replace with your authentication key

You can find your DeepL Authentication Key in the summary tab (Scroll down until you see the “Authentication Key for DeepL API” tab).

Step 4: Close the Apps Script editor and go back to your Google Sheet

Step 5: Use the DeepLTranslate function to translate text like so:

DeepLTranslate("Good morning", "auto", "FR")

Step 6: If you need more detailed information on how to use the function you can find it on the project page

If you are wondering why DeepL may be a better solution make sure to check this article comparing Google Translate and DeepL features

Third-Party Add-ons for Translation in Google Sheets

Did you know there are other add-ons you can use to make translating even easier? These add-ons offer extra features to help you translate your spreadsheet content quickly and efficiently. Check out some of the most popular ones below:

Translate My Sheet

Download Translate My Sheet here

At the time of writing, this is one of the most popular translation plugins for Google Sheets.

While it doesn’t offer much beyond what the built-in GOOGLETRANSLATE function can do (it’s actually using the same Google Translate engine), it does have one major advantage: it can translate the contents of your cells directly.

However, there is a downside too. The free version has a daily limit of a few thousand cells, so you may need to upgrade to a paid plan if you have a lot of content to translate.

On top of that, there’s one thing I personally don’t like about this plugin. The thing is that it automatically formats translated cells with a green background.

Instaglot

Download Instaglot here

InstaGlot is another add-on for translating content within Google Sheets, designed to simplify the process.

It uses the powerful DeepL engine to offer real-time translations in 26 different languages, making it a great option if you want a straightforward translation solution without having to play with App Scripts as I described in the previous chapter.

In my opinion, it’s unnecessary as you can integrate DeepL directly without using a middleman like InstaGlot.

Translate All

Download Translate All here

This add-on is very similar to Translate My Sheet (in fact, it was also written by the same author). The main difference is that it has a more user-friendly interface and can be used with other Google Workspace services, including Google Docs and Google Forms.

Bonus material: Organising Your Google Sheet for Translations for Beginners

To have a successful translation process in Google Sheets, it’s important to prepare and organize your data properly. Here are some tips to help you get started:

Clean and format your data:

Before starting the translation process, ensure your data is free from inconsistencies, errors, and formatting issues.

Remove any extra spaces, unwanted characters, or incorrect line breaks. Also, ensure your text is consistently formatted with proper capitalization, punctuation, and font styles.

Separate content into columns

Divide your content into logical sections or columns to make it easier to manage.

For example, if you’re translating product descriptions, create separate columns for product names, descriptions, and specifications.

This approach allows you to focus on translating one column at a time, and it minimizes the risk of errors or missed translations.

Create separate columns for translations

For each language, you want to translate your content into, create a separate column next to the original text.

This organization makes it easier to track and review translations, as well as modify them if needed.

For example, if your original text is in column A, you could create columns B, C, and D for translations in Spanish, French, and German, respectively.

Add header labels

Label each column with a clear header to indicate the content and language. This practice helps you quickly identify the original text and its translations while working with the data.

Use headers like “Product Name (EN)”, “Product Name (ES)”, “Product Name (FR)”, etc.

Freeze header row

To move around your sheet easily, freeze the header row. This means that the headers will stay put even when you scroll down the page. It also gives you a consistent place to look for the original text and its translations.

Consider using data validation

If you’re sharing your Google Sheet with others, consider using data validation to prevent accidental modification of the original text or language codes. This feature helps maintain the integrity of your data and reduces the risk of translation errors.

My Final Thoughts

In this article, I showed you how to use Google Sheets for translations. You can use the powerful GOOGLETRANSLATE formula and third-party add-ons to make translations easy.

I also gave you tips on how to prepare your data and organize it effectively in Google Sheets. These tips can help you avoid mistakes and make your translations smoother and more efficient.

I hope this guide will be helpful to anyone who wants to translate the content within Google Sheets, whether you’re a beginner or an advanced user.