Microsoft Excel doesn’t include built-in tools to convert currency. However, you can use an external data source to provide up-to-date rates. A basic multiplication formula will then convert from one currency to another. Here’s how you do it!
Adding an External Data Source to Excel
An external data source is the best way to get up-to-date currency exchange rates. You can use this data to convert from one currency to another in Excel. The process is similar to converting currency in Google Sheets.
RELATED: How to Convert Currency in Google Sheets
First, you need a suitable online data source (in the XML format) that you can import into your spreadsheet. FloatRates has various XML feeds based around different currencies that you can use.
After you find the one you want to use, open your Excel spreadsheet. In Excel 2019 or Office 365, click Data > Get Data > From File > From XML. In older versions of Excel, click Data > Get External Data > From Other Sources > From XML Data Import instead.
For our example, we’re using the FloatRates USD data feed, so we import that into Excel.
In the “Import Data” window, paste the URL to your XML data feed in the “File Name” box, and then click “Import.”
If you have Office 2019 or 365, you see a preview of how the data will be imported. If you’re using FloatRates data, you have to convert it in the Excel Power Query Editor to use it.
To do so, click “Transform Data.”
The Excel Power Query Editor appears. Scroll to the “Item” column, and then double-click “Table” to load the up-to-date currency rates.
The Power Query Editor preview updates and shows the FloatRates currency data. Click “Close and Load” in the top-left corner to add the data to your spreadsheet.
The data you import appears in a new worksheet, to which you can now refer when you need to convert currency.
Most external data sources update hourly, but FloatRates only updates every 12 hours. If you want to update your data manually, click Data > Refresh All.
Converting Currency in Microsoft Excel
You can use the up-to-date data you imported to convert currency figures with a simple multiplication formula.
Click the worksheet with your imported currency rates. If you’re using FloatRates data, look at the exchange rates under the “exchangeRate” column. Note the cell that contains the rate of the currency to which you want to convert.
Using our FloatRates U.S. dollar data, we see that to convert from U.S. dollars to British pounds, we need to use the GBP exchange rate in cell I3.
Return to your existing worksheet, and type the USD price from which you want to convert into a cell. In a second cell, use the formula =A2*Sheet2!$I$3, and replace “A2” with the cell that contains your USD price.
Replace the second part of the formula with an absolute reference to the cell in the “exchangeRate” column on your imported data worksheet that contains the exchange rate to which you want to convert.
In the example above, Column A lists U.S. dollars. Column B lists the converted currency rates from U.S. dollars to British pounds (1 USD to GBP is in cell B2).
When you change the absolute cell reference and use alternative data sources (like the FloatRates GBP data source to convert from GBP to other currencies), you can convert from any currency to another.
You can also use a manual rate instead of an external data source to convert currency rates. Just set the exchange rate manually in a cell (in our example, cell B2), and the price in another (cell A3).
The same multiplication formula converts your currency. However, if you’re not using an external data source, you’ll have to update the rate manually to see the correct price.
