Currency conversion with GOOGLEFINANCE

Converting between currencies live without leaving the sheet is one of the simpler GOOGLEFINANCE recipes. This tutorial walks through the simplest, most maintainable formula-only approach in Google Sheets, plus the variants that come up once your data is more than a hundred rows.

The one-cell solution

=A2 * GOOGLEFINANCE("CURRENCY:USDEUR")

Cell A2 holds the USD amount. The GOOGLEFINANCE call returns the live USD→EUR rate. Multiply for the converted value. For historical rates, add start and end dates as additional arguments.

Why this beats copy-paste

Doing currency conversion by hand works once. Doing it with a formula means the answer updates automatically when source data changes, and the audit trail is right there in the formula bar — no chance of someone forgetting to repeat the steps next month. The trade-off is a small upfront investment in writing and testing the formula; the payoff is every time the data refreshes.

Edge cases to test

Before deploying any currency conversion formula in a real workflow, test three cases: empty source range, single-row source, and the full expected dataset. Spreadsheet formulas have an unfortunate habit of working perfectly on the sample data and falling apart on the live data because of a single missing value or unexpected type. Walk through the edge cases in a scratch tab before promoting the formula into the dashboard.

Production-grade variant

Wrap the production version in IFERROR with a friendly message, ARRAYFORMULA the column header inline, and add a small note cell explaining what the formula does. The next person to inherit the sheet (often a future-you with no memory of writing this) will silently thank you. Build it once, document it once, never write it again.

Worked examples

Baseline

=A2 * GOOGLEFINANCE("CURRENCY:USDEUR")

Drop into the cell where you want the answer.

With error handling

=IFERROR(=A2 * GOOGLEFINANCE("CURRENCY:USDEUR"), "")

Returns blank when the source is empty rather than #REF or #N/A.

ARRAYFORMULA-friendly version

=ARRAYFORMULA(=A2 * GOOGLEFINANCE("CURRENCY:USDEUR"))

Use only if the inner functions array-evaluate correctly — test on a small range.

Common mistakes (and how to avoid them)

Hardcoding range bounds ("A2:A100") that go stale as data grows. Use open-ended ranges.
Skipping the empty-source case in testing — the formula works on data and fails the day someone clears the sheet.
Putting many copies of the same formula in different cells. Promote it to one ARRAYFORMULA-driven column.
Mixing presentation (currency formatting, units) into the formula. Format the cell instead.

Tips that pay off later

If the formula gets longer than two lines, break it into pieces with LET for readability.
Always sanity-check the answer against a manually computed value on a small dataset before trusting it on production data.
When sharing the sheet, lock the cell containing the formula so collaborators cannot accidentally overwrite it.

When to reach for something else

If your currency conversion workflow needs more than three coordinated formulas, consider promoting it to Apps Script — formulas are the right tool until they are not.