Microsoft Excel does not include built-in tools to convert currency. Despite this, you can use an external data source to provide up-to-date rates. After, a basic multiplication formula will be converted from one currency to another. This is how you do it!
Add 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 procedure is similar to converting currency in the Google Sheets search engine..
RELATED: How to convert currency in the Google Sheets search engine
First, you need a suitable online data source (in XML format) that you can import into your spreadsheet. FloatRates has several XML feeds based on different currencies that you can use.
After finding the one you want to use, open your excel spreadsheet. In excel 2019 u Office 365, click Data> Get Data> From File> From XML. In earlier versions of Excel, click Data> Get External Data> From Other Sources> From XML Data Import.
For our example, we are using the FloatRates USD data source, therefore we import it to Excel.
In the window “Import data”, paste the url in your XML data feed into the box “Filename” and then click “to import”.
If you have Office 2019 O 365, you will see a preview of how the data will be imported. If you are using FloatRates data, you must convert them in Excel Power Query Editor to use them.
To do it, click on “Transform data”.
The Excel Power Query Editor appears. Scroll to column “Element” and then double-click “Table” to load the updated exchange rates.
Power Query Editor preview updates to show FloatRates currency data. Click on “Close and load” in the upper left corner to add the data to your spreadsheet.
The data you import appears in a new worksheet, which you can now refer to when you need to convert currency.
Most external data sources are updated hourly, but FloatRates only updates every 12 hours. If you want to update your data manually, click Data> Update All.
Currency conversion in Microsoft Excel
You can use the updated data that you imported to convert currency figures with a simple multiplication formula.
Click on the worksheet with your imported exchange rates. If you are using FloatRates data, look at the exchange rates in the column “exchangeRate”. Note the cell that contains the rate of the currency you want to convert to.
Using our FloatRates data in US dollars, we see that to convert from US dollars to British pounds, we need to use the GBP exchange rate in cell I3.
Go back to your existing worksheet and type in the USD price you want to convert to a cell. In a second cell, use the formula =A2*Sheet2!$I$3
and replace “A2” with the cell containing your price in USD.
Replace the second part of the formula with an absolute reference to the cell in the column “exchangeRate” in your imported data worksheet containing the exchange rate you want to convert to.
In the example above, column A lists US dollars. Column B lists the exchange rates converted from US dollars to British pounds. (1 USD to GBP in cell B2).
When you change the absolute cell reference and use alternate data sources (as the FloatRates GBP data source to convert from GBP to other currencies), can convert from any currency to another.
You can also use a manual rate instead of an external data source to convert exchange 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. Despite this, if you are not using an external data source, you will need to update the rate manually to see the correct price.