How to Convert Currency in Microsoft Excel
Image Credits: Howtogeek
Microsoft Excel does not include in-built tools to convert currency. However, you can make use of an external data source to provide up-to-date rates. A basic multiplication formula will then convert them from one currency to another. Here’s how to do it!
Adding an External Data Source to Excel
An external data source is always the best way to get up-to-date currency exchange rates. You can make use of this data to convert from one currency to the other in Excel.
First of all, you would be needing a very suitable online data source (in the XML format) that you can import into your spreadsheet. FloatRates has several XML feeds that is based around different currencies that you can make use of use of .
After you find the one you that want to use, open your Excel spreadsheet. In Excel 2019 or Office 365, click DGet Data > From File > From XML. In much older versions of Excel, click Data > Get External Data > From Other Sources > From XML Data Import instead.
Image Credits: Howtogeek
For our example, we are using the FloatRates USD data feed, so we would 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 on the “Import” button.
Image Credits: Howtogeek
If you have Microsoft office Office 2019 or 365, you would see a preview of how the data will be imported into your sheet. If you are using FloatRates data, you will have to convert it in the Excel Power Query Editor to use it.
To do just that, click on the “Transform Data” button.
Image Credits: Howtogeek
The Excel Power Query Editor would come up appears. Scroll down to the “Item” column, and then double-click on the “Table” to load the up-to-date currency rates.
Image Credits: Howtogeek
The Power Query Editor preview would now update itself and display the currency data from Floatrates. Click on the “Close and Load” button that is in the top-left corner to add the data to your spreadsheet.
Image Credits: Howtogeek
All the imported data would appear in a new worksheet, to which you can now look at whenever you need to convert currency.
Most external data sources usually their prices hourly, but FloatRates only updates after every 12 hours. If you want to update your data manually, click on Data > Refresh All.
Image Credits: Howtogeek
Converting Currency in Microsoft Excel
You can use the up-to-date data tha you imported to convert currency figures across with a simple multiplication formula.
Click the worksheet that has your imported currency rates. If you’re using FloatRates data, look at the exchange rates that is under the “exchangeRate” column. Note the cell that has the rate of the currency to which you want to convert.
Image Credits: Howtogeek
Return to your already worksheet, and type in the USD price from which you would want to convert into a cell. In a second cell, make use of the formula =A2*Sheet2!$I$3, and replace the “A2” with the cell that contains your USD price.
Replace the second part of the formula with an absolute reference to the cell that is in the “exchangeRate” column on your imported data worksheet that contains the exchange rate to which you want to convert.
Image Credits: Howtogeek
In the example that is seen above, Column A lists U.S. dollars.
When you change the absolute cell reference and make use of alternative data sources such as (the FloatRates GBP data source to convert from GBP to other currencies), you can convert from any currency to another.
You can also make use of 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).
Image Credits: Howtogeek
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.
