JASMA

If you’ve ever tried to budget an international trip in a spreadsheet, you already know the emotional roller coaster:
“This hotel is only 120!” (USD) becomes “WHY IS IT 120,000?!” (JPY). The good news: Excel can absolutely be your
calm, mathy friend here. With the right setup, you can build a currency converter in Excel that updates exchange
rates, converts amounts instantly, and keeps your sanity intact.

In this guide, you’ll learn two easy ways to create a currency converter:
(1) the built-in Currencies data type (fastest, lowest effort), and
(2) Power Query (more control, great for teams and repeatable reporting).
Along the way, you’ll get practical tips (drop-downs, formatting, error-proofing) plus the “don’t do this at 2 a.m.”
lessons people learn the hard way.

What You’re Building (In Plain English)

A currency converter spreadsheet is basically three pieces:

• Inputs: amount, “from” currency, “to” currency
• Rate: a cell (or table) that pulls the exchange rate
• Output: amount × rate (plus optional rounding/fees)

The magic is in how you get the rate. Let’s do it two waysone “built-in easy,” one “power-user easy.”

Way 1: Use Excel’s Built-In Currencies Data Type (Fastest)

If you’re using Excel for Microsoft 365 (desktop, web, or Mac), you may already have a feature that
can fetch exchange rates by turning a currency pair like USD/EUR into a linked data type.
Translation: Excel goes online, grabs the current rate, and you can pull it into your sheet with a couple clicks
or a simple formula.

Step-by-Step: Create a Live Rate From a Currency Pair

1. In a cell (say A2), type a currency pair in this format:
   USD/EUR (use ISO currency codes).
2. Select the cell, then go to the Data tab and choose Data Types > Currencies.
   (If you don’t see it, jump to Way 2.)
3. Once it converts, you’ll see a small icon in the cellExcel’s way of saying, “Yep, I’m linked to live data.”
4. To extract the exchange rate, click the cell, then use the Insert Data button and choose
   Price. Or use a formula like:

   =A2.Price

   (Depending on your Excel version, you can also use FIELDVALUE as a more explicit option.)

Turn It Into a Real Converter (Drop-Downs + One Formula)

Now let’s make it user-friendly so you’re not retyping currency codes like it’s 1998.

1) Create your input area

• B2: Amount (example: 100)
• B3: From currency (example: USD)
• B4: To currency (example: EUR)

2) Build the currency pair automatically

In B5, create the pair:

=B3&"/"&B4

3) Convert that pair into the Currencies data type

Select B5 → Data → Data Types > Currencies.

4) Pull the exchange rate

In B6, get the rate:

=B5.Price

5) Calculate the converted amount

In B7, multiply:

=B2*B6

Congratulationsyour spreadsheet is now doing currency conversion in Excel with live exchange rates.
You can change the amount, switch currencies, and the result updates.

Make It Look Like Money (So Humans Don’t Panic)

Excel can calculate perfectly and still look terrifying if your numbers aren’t formatted. Two quick wins:

• Use Currency or Accounting formatting for outputs (Home > Number group).
  Accounting is great for lining up decimals; Currency is more casual.
• If you’re converting into multiple currencies, consider showing the currency code next to the
  result (e.g., “EUR”) so you don’t accidentally celebrate the wrong number.

Troubleshooting Way 1 (Because Excel Has Moods)

• No “Currencies” option? You might be on a version that doesn’t support it, or your organization
  has connected experiences disabled. Use Way 2.
• Rate won’t refresh? Check your internet connection, then try Data > Refresh All.
  Sometimes the data provider can have temporary hiccups.
• Wrong result? Confirm the pair direction (USD/EUR is not the same as EUR/USD), and make sure
  you’re using ISO codes.

Way 2: Use Power Query to Pull Exchange Rates (More Control)

Power Query (also called “Get & Transform”) is Excel’s built-in data pipeline tool. Instead of relying on a
linked data type, you connect to a web source (often an API), transform the data cleanly, and load it into a table.
The big perks: repeatability, refresh control, and the ability to build a small “rates database” for reporting.

Why Power Query is Perfect for Currency Conversion

• Reliable structure: rates land in a table you can reference with XLOOKUP
• Refresh on demand: click Refresh All and update everything
• Scales well: one query can feed multiple converters and reports

Step-by-Step: Pull Rates From a Simple Exchange-Rate API

Many exchange-rate APIs exist. For an easy demo, you can use a free endpoint that returns JSON data.
One example is the Frankfurter API (ECB-based reference rates) which provides “latest” rates in a clean format.

1. Go to Data > Get Data > From Other Sources > From Web
   (wording varies slightly by Excel version).
2. Paste a URL that returns JSON. Example (latest rates with a base currency):

   https://api.frankfurter.dev/v1/latest?base=USD

3. When prompted for access, choose an appropriate method (often Anonymous for public endpoints).
4. In the Navigator/preview window, choose Transform Data to open Power Query Editor.
5. Expand the JSON fields until you get a nice table of currency codes and rates. You want something like:

   Currency | Rate

6. Click Close & Load to load the table into your workbook (preferably as an Excel table).

Use the Power Query Table to Convert Any Amount

Once you have a table of rates (example table name: Rates with columns Currency
and Rate), conversion becomes predictable spreadsheet math:

Example: Convert USD to EUR using a lookup

• B2: Amount in USD (e.g., 100)
• B4: To currency code (e.g., EUR)

If your table lists rates as “1 USD = X EUR,” then:

=B2*XLOOKUP(B4, Rates[Currency], Rates[Rate])

If your API returns rates with a different base (like EUR), you have two options:
(1) change the API call to set the base currency, or (2) do a two-step conversion (from base to USD, then USD to target).
For most people, setting the base in the query is cleaner and less error-prone.

Refresh Like a Pro (Without Rebuilding Anything)

After the table is loaded, updating rates is simple:

• Right-click the rates table > Refresh
• Or use Data > Refresh All to update everything connected

Pro tip: If you’re building a budgeting model, you might refresh once daily or weekly rather than every time you open the file.
Real-time is cool, but so is stability.

Quality Checks (Because Exchange Rates Aren’t Just “A Number”)

1) Know what rate you’re using

Most public sources provide a mid-market/reference rate. Banks and card networks may add a spread or fee.
If you’re modeling real costs, add a “fee %” input:

Converted = Amount * Rate * (1 + Fee%)

2) Handle weekends and holidays

FX markets don’t update the same way every single day. Some “latest” endpoints return the last working day’s rate.
That’s normaljust label your rate date in a cell so nobody assumes it’s minute-by-minute.

3) Error-proof your sheet

Wrap lookups so your converter doesn’t explode when someone types “USDD”:

=IFERROR(B2*XLOOKUP(B4, Rates[Currency], Rates[Rate]), "Check currency code")

Which Method Should You Use?

• Use Way 1 (Currencies data type) if you want the quickest setup and you’re on Microsoft 365.
• Use Way 2 (Power Query) if you want a reusable rates table, more transparency, or a model that others will maintain.

Conclusion

Building a currency converter in Excel doesn’t have to involve a maze of random websites, manual copy-paste, or
“I think this rate is from 2021?” panic. If you have Microsoft 365, the Currencies data type is the
simplest path to live conversion. If you need control and repeatability, Power Query turns exchange
rates into a clean, refreshable data source you can trust (and audit).

Pick the approach that matches your workflow, add a couple guardrails (formatting, error handling, date labels),
and you’ll have a converter that’s fast enough for daily use and sturdy enough for real reporting.

Experiences: What People Run Into After Building an Excel Currency Converter (Extra ~)

Once a currency converter is built, the first week feels like magic. You type “USD,” pick “EUR,” and Excel calmly
spits out a number like a well-trained accountant. Then real life shows up with a clipboard and starts asking
annoying questions. One of the most common experiences is discovering that “the” exchange rate isn’t a single
universal truth. People compare their spreadsheet result with what their bank charged and assume Excel is wrong.
Usually, Excel is using a reference rate, while the bank uses a rate plus a spread (and sometimes a fee wearing a
fake mustache labeled “service charge”). The fix is simple: add an optional fee percentage and make the sheet show
both “market estimate” and “estimated after fees,” so users see the difference instead of starting a spreadsheet
conspiracy theory.

Another classic moment happens on weekends. Someone refreshes on a Sunday, sees yesterday’s date, and concludes the
workbook is “broken.” It’s not brokenmany sources update on business days, and “latest” can mean “latest working
day.” The best experience-driven tweak is adding a visible “Rate Date” field next to the rate, so users trust what
they’re looking at. It’s a tiny cell that prevents big arguments.

Teams also run into version mismatches. One person builds the converter using the Currencies data type on Microsoft
365, shares the file, and a coworker on an older perpetual version opens it and sees sad little placeholders where
the rates should be. This is where Power Query shines: if the team agrees on a workflow and the query loads a
regular table, the model becomes easier to maintain. The “experience lesson” is to choose the method based on who
will use the workbook, not just who builds it.

There’s also the “I need 12 currencies, 200 rows, and I need it now” stage. People start by converting one amount,
then suddenly they’re doing international pricing, travel reimbursement, or multi-currency sales reporting. The
smooth upgrade is creating a small currency list for data validation (so inputs stay clean), a dedicated rates
table, and formulas that reference structured table columns. That’s when the converter stops being a neat trick
and becomes real spreadsheet infrastructure.

Finally, the most underrated experience is learning that clarity beats cleverness. A converter that’s 5% less
fancy but clearly labeled (“Base currency,” “Rate source,” “Last refreshed,” “Includes fees: Yes/No”) gets used and
trusted. A converter that’s technically brilliant but mysterious gets copied into a new file and rewritten with
worse formulasbecause humans. The win is building for the user you’ll be in three months: tired, rushed, and very
grateful for labels.