/ Exchange Rates API / By

Use Official Exchange Rates in Excel

In this guide, we will see how to pull official exchange rates directly into Excel from the Global Exchange Rates API using Power Query. These rates come from trusted sources such as central banks and tax authorities, and are suitable for accounting, regulatory reporting, and internal dashboards.

Excel uses the same Power Query engine as Power BI, so if you have already followed our Power BI guide, the steps below will look familiar. We also have a version for Google Sheets.

First of all, we will need:

  • Microsoft Excel 2016 or later, or Microsoft 365 (Power Query is included by default).
  • A valid API key from Global Exchange Rates. You can find it in the Developer API portal after subscribing, or request one on the get-started page.
  • (Optional) A preferred provider (e.g. ECB, BoE) or the base currency you want to use (e.g. EUR).

1. Connect to the API with Power Query

  • Open Excel and go to Data → Get Data → From Other Sources → From Web (in some versions, Data → From Web).
  • In the URL field, enter:
https://api.globalexchangerates.org/v1/latest?subscription-key=YOUR_SUBSCRIPTION_KEY

Optionally, you can specify a base currency or a specific provider — for example, for the ECB (European Central Bank):

https://api.globalexchangerates.org/v1/latest?subscription-key=YOUR_SUBSCRIPTION_KEY&provider=ECB

Click OK to proceed.

2. Transform the response into a table

Excel opens the Power Query editor and parses the JSON response. We now need to flatten it into a table:

  • From the Source, select the exchangeRates record. This outputs the exchange rates as a key-value pair list.
  • On the Transform tab, choose Into Table.
  • Rename the Name column to Currency and the Value column to Rate.

3. Load the rates into your worksheet

  • On the Home tab, click Close & Load. The rates are added to your workbook as an Excel table.
  • Select the table and, on the Table Design tab, set the Table Name to Rates so you can reference it in formulas.

We now have a table with the most recent exchange rates published by official central banks.

4. Convert amounts to your base currency

Suppose you have a table of transactions with an Amount column and a Currency column. You can convert each amount into your base currency using the official rate with a single formula:

=[@Amount] / XLOOKUP([@Currency], Rates[Currency], Rates[Rate])

This looks up the official rate for each transaction’s currency and converts the amount into your base currency. You can wrap the formula with ROUND() if needed for presentation. On older versions of Excel without XLOOKUP, use VLOOKUP against the Rates table instead.

5. Keep the rates up to date

  • To pull the latest rates, go to Data → Refresh All, or right-click the table and choose Refresh.
  • To refresh automatically, open Data → Queries & Connections, right-click the query, choose Properties, and enable Refresh data when opening the file or set a periodic refresh interval.

It is also possible to use the /historical endpoint to fetch exchange rates by date.

Conclusions

With Power Query and a single API call, Excel now holds official central bank exchange rates that you can use for currency conversion and reporting, refreshed on demand.

For fully scheduled, unattended refresh at scale, consider connecting the same API in Power BI or a dedicated pipeline.

If you don’t have an API key yet, you can get one on the get-started page, with official rates from central banks and tax authorities worldwide.