Spreadsheet guide

Google Sheets currency converter, done right

GOOGLEFINANCE is the obvious answer and the wrong one half the time. Here's the working syntax for live rates, historical lookups, and ECB-pinned alternatives.

The one formula you actually need

=GOOGLEFINANCE("CURRENCY:USDEUR")

Returns the live USD→EUR mid-market rate. Multiply by your USD amount to get EUR. Refresh happens roughly every 20 minutes while the sheet is open — not in the background, and not deterministically.

Variable currency picker

=A1 * GOOGLEFINANCE("CURRENCY:" & B1 & C1)

Where A1 is the amount, B1 is the source currency (e.g. USD) andC1 is the target. Pair with Data → Data validation → Dropdown for the dropdowns.

Historical lookup

=INDEX(GOOGLEFINANCE("CURRENCY:USDEUR","price",DATE(2024,1,15)),2,2)

Pulls the rate for a specific date. Wrap in INDEX(...,2,2) to extract the price from the returned matrix.

When GOOGLEFINANCE breaks

  • Weekends and holidays. Quotes stall on the last close.
  • Some pairs return #N/A. Exotic crosses (USD/ISK, EUR/THB during low liquidity) drop out.
  • Not deterministic. The same cell can return different values across reloads — fine for a calculator, fatal for a finance report.
  • Mid-market only. No spread; your bank will quote worse.

The ECB-pinned alternative: IMPORTDATA + Frankfurter

=IMPORTDATA("https://api.frankfurter.dev/v1/latest?from=EUR&to=USD,GBP,JPY")

Frankfurter republishes ECB reference rates daily at ~16:00 CET. Same rates the Eurozone uses for accounting, free, no API key. More on the cadence: ECB exchange rates.

Snapshotting a rate so totals don't drift

Live formulas rewrite history. A row entered in January at 1.08 EUR/USD will silently revalue to whatever today's rate is on the next recalc. The fix: paste-special-values to snapshot the rate into a static column. Or use Currency Grid — a spreadsheet-style grid with ECB rates and copy-paste back to Sheets.

Frequently asked questions

How do I convert currency in Google Sheets?
Use =GOOGLEFINANCE("CURRENCY:USDEUR") to get the live mid-market rate, then multiply by your amount. For a historical rate, pass a date: =INDEX(GOOGLEFINANCE("CURRENCY:USDEUR","price",DATE(2024,1,15)),2,2).
Can Google Sheets convert currency automatically?
Yes — GOOGLEFINANCE refreshes roughly every 20 minutes while the sheet is open. It does not refresh in the background and the rate it returns is mid-market, not what your bank will actually give you.
How do I add a real-time currency converter in Google Sheets?
Combine GOOGLEFINANCE with a dropdown via Data → Data validation. Build a 'from' and 'to' currency picker plus an amount cell, then a result cell =A1 * GOOGLEFINANCE("CURRENCY:"&B1&C1).
Why is my GOOGLEFINANCE currency rate wrong or stuck?
GOOGLEFINANCE pulls from Google's quote feed, which lags interbank rates and occasionally returns stale values on Saturdays, holidays, and right after market open. For finance-grade rates use a feed pinned to a single source — ECB reference rates (via Frankfurter) are the most-cited free option.
What's the alternative to GOOGLEFINANCE?
If you need daily ECB reference rates or deterministic refresh, use Frankfurter directly via IMPORTDATA, or use a purpose-built grid like Currency Grid.

Try the live grid

Currency Grid is a spreadsheet-style multi-currency converter. Type amounts in any currency and see live conversions across rows and columns, totaled in your home currency using daily ECB reference rates.

Open Currency Grid

Related