How to Convert Currency in Microsoft Excel.
Microsoft Excel does not include built-in tools to convert currency. However, you can use an external data source to provide updated rates. Then 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 process is similar to converting currency in Google Sheets.
First, you need a suitable online data source (in XML format) that you can import into your spreadsheet. it has several XML feeds based on 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 earlier versions of Excel, click Data > Get External Data > From Other Sources > Import XML Data.
For our example, we are using the so we import it into Excel.
In the “Import Data” window, paste the URL to your XML data feed in the “File Name” box, then click “Import”.
If you have Office 2019 or 365, you’ll see a preview of how the data will be imported. If you’re using FloatRates data, you must convert it in the Excel Power Query Editor to use it.
To do so, click on “Transform Data”.
The Excel Power Query editor appears. Scroll down to the “Item” column and then double click on “Table” to load the updated exchange rates.
The Power Query Editor preview refreshes and displays the FloatRates currency data. Click “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 update hourly, but FloatRates only update 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 you imported to convert currency figures with a simple multiplication formula.
Click on the worksheet with your imported exchange rates. If you are using data from FloatRates, look at the exchange rates in the “exchangeRate” column. Note the cell that contains the rate of the currency you want to convert to.
Using our US dollar FloatRates data, 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 the USD price you want to convert from in a cell. In a second cell, use the formula =A2*Sheet2! $I$3, and replace “A2” with the cell that contains your price in USD.
Replace the second part of the formula with an absolute reference to the cell in the “exchangeRate” column in your imported data worksheet that contains 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 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 use a manual rate instead of an external data source to convert exchange rates. Simply set the exchange rate manually in one cell (in our example, cell B2) and the price in another (cell A3).
The same multiplication formula converts your currency. However, if you are not using an external data source, you will need to update the rate manually to see the correct price.
