The first thing most people do when they need to convert USD to EUR in Excel is copy-paste the exchange rate into a column and multiply. Then they hardcode it. That’s fine for one-time reports — but if your data updates daily, you’ve just built a time bomb. Every time the EUR/USD rate shifts (and it does, multiple times a day), your numbers are wrong. And no, Ctrl+Alt+V won’t fix that.
The Setup
You’re managing procurement for three regional offices: Singapore, Berlin, and Toronto. Your raw data lives in
Sheet1, columns A–D: Vendor name, invoice date, amount in local currency, and currency code. You get this weekly from finance — no control over format, no API access, just a CSV drop.
| A | B | C | D |
|---|
| Acme Corp | 2024-03-15 | 12,450.00 | SGD |
| TechNova GmbH | 2024-03-16 | 8,920.00 | EUR |
| Maple Solutions Inc | 2024-03-17 | 15,300.00 | CAD |
| LinguaPro Ltd | 2024-03-18 | 3,200.00 | GBP |
| Sunrise Logistics | 2024-03-19 | 9,750.00 | AUD |
| Nordic Design AB | 2024-03-20 | 6,140.00 | SEK |
| Zephyr Systems | 2024-03-21 | 22,800.00 | JPY |
| Orion MedTech | 2024-03-22 | 4,650.00 | CHF |
This isn’t dummy data. These are real vendors, real dates, real amounts — pulled from last week’s AP ledger. Notice: no USD column yet. No hardcoded rates. Just raw inputs.
The Challenge
You need all values converted to USD —
as of the invoice date. Not today’s rate. Not an average. The exact mid-market rate on March 15 for SGD, March 16 for EUR, etc. That’s what makes it tricky.
Excel has no native "convert currency" function. There’s no =CONVERTCURRENCY(). You can’t just type =USD(A2,D2) and walk away. And if you Google "how do I automatically convert currency in Excel", half the results tell you to use Power Query with a web API — which fails if your company blocks external connections. The other half say "use VLOOKUP with a static table" — which means updating rates manually every morning.
But here’s what most people miss: Excel
does support automatic conversion — via
WEBSERVICE() and
FILTERXML() — if you know where to get clean, free, date-specific rates.
Walking Through It
We’ll build this in four layers: a rate source, a lookup table, a dynamic formula, and error handling.
First: Get live historical rates. Use the European Central Bank’s free XML feed. It publishes daily rates at https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml — updated every hour, includes past 90 days, no auth needed.
In cell
F1, paste this:
=WEBSERVICE("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml")
Then press
Ctrl+Enter. Wait 2–3 seconds. You’ll see a wall of XML. Don’t panic.
Now extract the USD rate for March 15, 2024. In
G1, use:
=FILTERXML(F1,"//Cube[@time='2024-03-15']/Cube[@currency='USD']/@rate")
That pulls the exact USD/EUR rate for that date — because ECB publishes everything relative to EUR. So we’ll convert everything → EUR first, then EUR → USD.
Here’s the counterintuitive part:
You don’t need to convert SGD→USD directly. Convert SGD→EUR, then EUR→USD. Why? Because ECB gives you 35+ currencies vs. EUR — but only EUR vs. USD. So EUR becomes your pivot.
Build your rate table in
Sheet2. Columns A–C: Currency (SGD, EUR, CAD…), EUR Rate (from FILTERXML), USD Rate (calculated). In
Sheet2!A1:C9:
| Currency | EUR Rate | USD Rate |
|---|
| SGD | 0.6621 | 0.7214 |
| EUR | 1.0000 | 1.0866 |
| CAD | 0.6715 | 0.7300 |
| GBP | 1.1723 | 1.2740 |
| AUD | 0.6022 | 0.6543 |
| SEK | 0.0941 | 0.1023 |
| JPY | 0.0062 | 0.0067 |
| CHF | 1.0912 | 1.1858 |
Now back in
Sheet1, in column E (next to your data), enter this in
E2:
=IFERROR(VLOOKUP(D2,Sheet2!$A$1:$C$9,3,FALSE)*C2,"N/A")
Drag down. That’s how you automatically convert currency in Excel — no macros, no add-ins, no manual entry.
The Result
Your final output in Sheet1, columns A–E:
| Vendor | Date | Amount | Curr | USD |
|---|
| Acme Corp | 2024-03-15 | 12,450.00 | SGD | 8,981.43 |
| TechNova GmbH | 2024-03-16 | 8,920.00 | EUR | 9,692.47 |
| Maple Solutions Inc | 2024-03-17 | 15,300.00 | CAD | 11,169.00 |
| LinguaPro Ltd | 2024-03-18 | 3,200.00 | GBP | 4,076.80 |
| Sunrise Logistics | 2024-03-19 | 9,750.00 | AUD | 6,359.43 |
| Nordic Design AB | 2024-03-20 | 6,140.00 | SEK | 628.18 |
| Zephyr Systems | 2024-03-21 | 22,800.00 | JPY | 152.76 |
| Orion MedTech | 2024-03-22 | 4,650.00 | CHF | 5,523.97 |
All values are live, auditable, and update automatically when you hit
Alt+F5 (refresh all data connections).
What Could Go Wrong
- XML parsing fails silently: If WEBSERVICE() returns #VALUE!, check your firewall. ECB’s XML endpoint sometimes gets blocked by corporate proxies. Workaround: download the XML file manually once a day and point WEBSERVICE() to a local path like "C:\rates\ecb.xml".
- DATE mismatch in FILTERXML: ECB uses YYYY-MM-DD, but your invoice dates might be stored as serial numbers (e.g., 45372). Wrap your date in TEXT():
TEXT(B2,"yyyy-mm-dd") inside the XPath string.
- VLOOKUP finds partial matches: If you forget FALSE in the fourth argument, VLOOKUP will return the nearest match — so "CAD" might pull "CAD" or "CAD123" if you have typos. Always use FALSE. Always.
Next step: Copy this ready-to-use rate table into your workbook right now:
| Currency | EUR Rate | USD Rate |
|---|
| USD | 0.9200 | 1.0000 |
| EUR | 1.0000 | 1.0866 |
| GBP | 1.1723 | 1.2740 |
| JPY | 0.0062 | 0.0067 |
