Currency Conversion Formula in Google Sheets (2 Examples)

Currency conversion helps you get instant access to accurate, real-time currency data. Google Sheets makes this easier with its built-in function which is known as GOOGLEFINANCE. This function is simple to use and very efficient for keeping your financial data up to date. It’s ideal for budgeting, traveling, working with clients overseas, or managing international sales.

In this guide, we’ll show you how to use currency conversion formulas in Google sheets. We’ll learn two simple and quick methods that make managing your financial data easier and more convenient.

Google Sheets has a powerful function called GOOGLEFINANCE that can bring real-time currency conversion rates. It connects to Google’s finance data and pulls the current exchange rate between two currencies.

To apply the GOOGLEFINANCE function, first you have to create a table in Google Sheets. Prepare your spreadsheet by creating a financial dataset that includes different currencies and the specific amounts you want to convert.

In your Google Sheet create four columns (A to D) and name them Column A as From Currency, Column B as To Currency, Column C as Amount, and Column D as Exchange Rate.

In the first two columns, enter different currency codes like USD, EUR, JPY, etc. Use the third column for the amount you want to convert. For example, you want to fetch 100 U.S dollar exchange rate in euro.

So, you have to type the currency code USD (U.S dollar) into the A2 cell and EUR (Euros) in the B2 cell. And, leave the fourth column empty to apply the function that will return the real-time exchange rate.

Here’s how you can apply this formula:

➤ Open your Google Sheet and create a table of Column A to D for your financial data.
➤ Enter currency codes in Columns A and B, and input the amounts you want to convert in Column C.
➤ Leave the Column D empty to apply the function that will return the real-time exchange rate.

➤ Next apply the GOOGLEFINANCE function in D2 cell.
➤ Type this formula in cell D2:

➤ If we analyze the formula in detail, it becomes:

➤ Press Enter to implement the formula.

➤ Now, you’ll see the converted amount appear in D2 cell, based on the current exchange rate.

➤ Afterward, if you want to apply the same formula for the entire data then drag the formula down to the end of the Column D.

➤ Google Sheets will automatically update the formula for each row. And here you can see the final result in the image below.

That’s how you can apply conversion for multiple currencies in your Google Sheets.

Google Apps Script also helps to create a custom currency converter in Google Sheets. It offers more flexibility for handling conversions directly within your spreadsheet.

Here’s how you can use Apps Script for currency conversion:

➤ Since you already have the spreadsheet you created for the previous formula, you can continue working with it.
➤ Go to the top Menu and click Extensions >> Apps Script.

➤ Delete the existing code (if any). If you create it for the first time, give your project a relevant name.

➤ Now paste this code in place of the previous text you deleted. This script will perform a single currency conversion in cell D2.

Script for Cell D2:

function convertCurrencySingle() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const fromCurrency = sheet.getRange("A2").getValue();
  const toCurrency = sheet.getRange("B2").getValue();
  const amount = sheet.getRange("C2").getValue();
  // Build formula to fetch exchange rate
  const fxFormula = `=GOOGLEFINANCE("CURRENCY:${fromCurrency}${toCurrency}")`;
  // Temporarily place exchange rate in a helper cell
  sheet.getRange("Z1").setFormula(fxFormula);
  SpreadsheetApp.flush(); // Wait for formula to calculate
  // Get the calculated exchange rate value
  const exchangeRate = sheet.getRange("Z1").getValue();
  // Calculate converted amount
  const converted = amount * exchangeRate;
  // Write final converted amount to D2
  sheet.getRange("D2").setValue(converted);
  // Clear helper cell
  sheet.getRange("Z1").clear();
}

➤ Next click the Save icon from the top Menu to save the script. If you don’t save the script it won’t run.

➤ Once you save the code the Run tab will become active. Click it to start the execution of your script.

➤ After that you’ll see the message Execution is completed on the bottom-left corner.

➤ Now you can go back to your spreadsheet to get the result.
➤ If you want to apply this formula for the entire Column D, follow the same process and only change the script.

Script for the Entire D Column:

function convertCurrencyAll() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  for (let row = 2; row <= lastRow; row++) {
    const fromCurrency = sheet.getRange(row, 1).getValue(); // Column A
    const toCurrency = sheet.getRange(row, 2).getValue();   // Column B
    const amount = sheet.getRange(row, 3).getValue();       // Column C
    // Only process if all fields have values
    if (fromCurrency && toCurrency && amount) {
      const fxFormula = `=GOOGLEFINANCE("CURRENCY:${fromCurrency}${toCurrency}")`;
      const helperCell = sheet.getRange("Z1");
      // Put formula into helper cell
      helperCell.setFormula(fxFormula);
      SpreadsheetApp.flush();
      // Read the calculated exchange rate
      const exchangeRate = helperCell.getValue();
      // Convert and write to column D
      const converted = amount * exchangeRate;
      sheet.getRange(row, 4).setValue(converted);
    }
  }
  // Clean up helper cell
  sheet.getRange("Z1").clear();
}

➤ After execution is completed, the formula will automatically apply across all rows in Column D.

Frequently Asked Questions

How to convert currency in Google Sheets?

To convert currency in Google Sheets, use the GOOGLEFINANCE function. For example, to convert 100 USD to EUR, enter: =100*GOOGLEFINANCE(“CURRENCY:USDEUR”)
This formula fetches the current exchange rate and multiplies it by the amount you want to convert .

What is the formula for currency conversion in Google Sheets?

The basic formula for currency conversion is:
=Amount * GOOGLEFINANCE(“CURRENCY:FromCurrencyToCurrency”)
Replace Amount with the cell reference or number, and FromCurrency and ToCurrency with the respective three-letter currency codes like USD and EUR.

Wrapping Up

Currency conversion in Google Sheets is a simple way to manage financial data of different currencies. It’s especially useful for businesses, financial budgets, travelers, or anyone dealing with international projects.

Instead of checking exchange rates manually, Google Sheets lets you convert currencies automatically using the GOOGLEFINANCE function or Apps Script. This saves time, ensures accuracy, and keeps your data up-to-date with real-time rates. However, with just a formula or script, you can handle currency conversions right inside your spreadsheet.