How to Use Real Time Currency Converter in Excel

A real-time currency converter in Excel is a tool that updates exchange rates automatically and converts amounts based on the latest data. Instead of entering rates manually, Excel connects to a free online source and fetches the current rates for you.

This is very helpful when working with payments, budgets, or sales in different currencies. It saves time, reduces errors, and keeps your numbers up to date.

In this article, we’ll learn how to create a real-time currency converter in Excel using some simple methods and a free exchange rate API. Once it’s set up, Excel will do the conversion for you with just a click.

In the following dataset, we have a simple currency conversion log that tracks order amounts between different currencies.
Column A shows the From Currency (USD), Column B lists the To Currency (such as CAD, EUR, CNY), and Column C contains the Order Values in the target currency.

Our goal is to convert each order value back to USD using real-time exchange rates, and display the result in Column D under Converted Value.

We will use this dataset to show how to create a real-time currency converter in Excel that converts any currency from the US dollar to another currency using lookup and calculation techniques.

The best way to get live exchange rates in Excel is by using Power Query to pull data from an external API. In this method, we’ll connect to frankfurter.app, a free and reliable source for up-to-date currency conversion rates.

We’ll then use XLOOKUP to match the customer’s currency with the current exchange rate and calculate the USD equivalent for each order.

This method works in Excel 365, Excel 2019, and Excel 2016 (with Power Query installed).

Here’s how to apply this method:

Step 1: Open Power Query and Connect to Exchange Rate API

➤ Open your Excel spreadsheet.
➤ Go to the Data tab on the Excel ribbon.
➤ Click Get Data >> From Web.

➤ In the dialog box, paste the following API URL:
https://api.frankfurter.app/latest?from=USD
➤ Click OK.

➤ This will open the Power Query Editor.
➤ You’ll see a record labeled rates. Click on it.

➤ Now you’ll see the real time exchange rate in two columns.
➤ Click the Into Table button from the top.

➤ Now you’ll the exchange rate table with two columns: Currency Name and Value
➤ Click Home >> Close & Load >> Close & Load To to load this table into your existing tab of Excel worksheet.

➤ In the pop-up, click on the Existing worksheet if you want the currency exchange rate table in your existing sheet. Next, click OK.

➤ You’ll now have a list of currencies and their exchange rates relative to USD.

Step 2: Use XLOOKUP to Return Exchange Rates

Now we’ll return to our main dataset and use XLOOKUP to match the currency code with its corresponding exchange rate.

Here’s how to apply this method:

➤ Click on cell D2 in the Converted value column.
➤ Enter the following formula:

➤ Press Enter. The formula multiplies the local Order Value by the latest Exchange Rate to calculate the required currency equivalent.

➤ Drag the fill handle down to copy the formula for all rows.

Step: 3 Auto-Refresh Exchange Rates to Keep Data Up to Date

Once your real-time currency converter is set up, it’s important to make sure the exchange rates stay current. Excel allows you to automatically refresh the data at regular intervals or every time the workbook is opened.

This step helps you keep the converted values updated without manually clicking the refresh button each time.

Here’s how to do it:

➤ Right-click on any cell inside the exchange rate table that was loaded using Power Query.
➤ From the menu, choose Query >> Properties.

➤ In the dialog box that appears, check the box that says Refresh data when opening the file.
➤ If you want to update it regularly, also check Refresh every and set a time, like every 60 minutes. Now, click OK.

➤ Now your Excel file will automatically pull the latest exchange rates every time it opens or on a schedule you choose.

Now that we have the exchange rates loaded into Excel, we can use a simple formula to convert the amounts in our dataset. In this method, we’ll use the VLOOKUP function to match the correct exchange rate and multiply it by the original amount.

Here’s how to calculate the converted values step by step:

➤ Click on cell D2 in your Excel dataset.
➤ Enter the following formula:

➤ Press Enter. You’ll see the converted value based on the live exchange rate.

➤ Drag the fill handle down from D2 to apply the formula to the rest of the rows.

Frequently Asked Questions

How do I build a real time currency converter in Excel?

You can build a real time currency converter in Excel by using Power Query to connect with a free exchange rate API like Frankfurter.app. Once you import the live rates, use formulas like VLOOKUP or XLOOKUP to calculate the converted values.

Can Excel automatically update currency exchange rates?

Yes. When you use Power Query to load data from a currency API, Excel can refresh the exchange rates automatically. You can set it to update every few minutes or refresh when the file opens. This keeps your real time currency converter in Excel accurate and up to date.

Wrapping Up

Setting up a real time currency converter in Excel is a powerful way to simplify international transactions, reporting, and financial planning. With the help of Power Query and a free API, you can pull live exchange rates directly into your spreadsheet and calculate converted values instantly.

We explored how to connect to an API, match rates using formulas like VLOOKUP and XLOOKUP, create dynamic queries, and format results for clarity. Once everything is in place, Excel becomes a smart, real-time currency tool that updates itself with just a refresh.