Microsoft Excel does not include any built-in tools to convert currency. But, you can use an external data source to provide up-to-date rates. A simple multiplication formula will then convert from one currency to another. Here’s how to Convert Currency in Microsoft Excel.
How to Convert Currency in Microsoft Excel :
Add an External Data Source to Excel
An external data source is usually the best way to get up-to-date currency exchange rates. You can use this data to convert from one currency to another in Microsoft Excel. The process is quite the same as converting currency in Google Sheets.
First of all, 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 various currencies that you can use.
After you find the one you want to use then, open your Excel spreadsheet. In Excel 2019 or Office 365, tap on Data > Get Data > From File > From XML. In older versions of Excel, tap on Data > Get External Data > From Other Sources > From XML Data Import instead.
We are using the FloatRates USD data feed for this example, 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 tap on “Import.”
In case you have Office 2019 or 365 then, you see a preview of how the data will be imported. In case you’re using FloatRates data then, you have to convert it in the Excel Power Query Editor to use it.
To do so, simply tap on Transform Data.
When the Excel Power Query Editor appears then scroll to the “Item” column. Then double-click “Table” to load the up-to-date currency rates.
The Power Query Editor preview usually updates and shows the FloatRates currency data. Tap on “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 however, FloatRates only updates every 12 hours. In case you want to update your data manually then, tap on Data > Refresh All.
Convert Currency in Microsoft Excel
You can also use the up-to-date data you imported to convert currency figures with a simple multiplication formula.
Tap on the worksheet with your imported currency rates. In case you’re using FloatRates data then, look at the exchange rates under the “exchangeRate” column. Kindly note that 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.
Now return to your existing worksheet, and type the USD price from which you want to convert into a cell. Then 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 which 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 in place 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, in case you’re not using an external data source then, you’ll have to update the rate manually to see the correct price.