You can use Microsoft Excel not only to create regular spreadsheets. There is a way to make your currency converter. In other words, you can make your currency exchange rates if you need to show some financial data report, for example. A special Currencies function is used for this purpose in Microsoft Excel.
You can get the time of the last trade, highs and lows, percent change, and more by entering a couple of ISO currency codes. Then simply select the details you want to include and update the data as needed.
You should also know that the Currencies option is only available now to Microsoft 365 subscribers. Well, there’s nothing complicated about getting currency exchange rates in Microsoft Excel. Here’s how you can do it.
How to use the Currencies option to calculate exchange rates in Microsoft Excel
With the Currencies option, you can easily obtain and compare currency rates from around the world. So, follow these steps:
- First of all, enter a currency pair in the cell using this format: From Currency / To Currency with ISO currency codes. For example, enter “USD/EUR” to get the exchange rate of one U.S. dollar to one euro.
- Then highlight the cells and click “Insert”.
- Select “Table” from the list. Although creating a table is optional, it will make it much easier to insert data from the data type later on.
- After that, go to the “Data” tab and select the “Currencies” data type.
If the program finds a match between the currency pair and the data provider, your text will be converted to the data type and the Currencies icon for the stock will appear in the cell.
If you see a question mark instead of a Currencies icon in a cell, Excel is having trouble matching text to data. Fix any errors and press Enter to try again. Alternatively, select the icon to open the Data Selector, where you can find the currency pair or specify the data you want.
Next, you need to:
- Select one or more converted cells and choose the Insert Data button that appears, or press Ctrl/Cmd + Shift + F5.
- You will see a list of all available fields to select from. Choose the fields to add a new data column.
Now, you can use it in formulas or calculations. To make sure your data is up to date, you can click “Data” and select “Refresh All” to get an updated quote. If you want to update only one pair you can select it and click “Refresh”.
How to convert currencies in Microsoft Excel
There is a way to convert currencies in Microsoft Excel without using a currency converter. To do this, you only need to use one formula.
To convert one currency to another, you must use 3 columns on the Excel sheet. The first column is for the target currency, the second is for the exchange rate, and the third is for the converted currency.
The formulas look like this: [Location of the first cell]*$[Column for exchange rate]$[Row for exchange rate]
Where [Location of the first cell] is the location of the first cell in the column of cells with the monetary values of the target currency. [Column for exchange rate] and [Row for exchange rate] are the column letter and row numbers for the cell in which the exchange rate is mentioned.
Essentially, this formula is designed to simply multiply the target currency by the exchange rate.
Read Also:
- How to add today’s date in Microsoft Excel
- How to switch the X and Y-axis in Microsoft Excel
- How to build a bubble chart in Microsoft Excel
How to calculate the exchange rate between two currencies in Microsoft Excel
Finding an exchange rate between two currencies is easy. Open Google and simply type in the abbreviations of the two currencies you want to compare. It will automatically display the exchange rate per unit of the first currency.
Suppose you have a column of values in dollars with values located from cell A2 to A6. You need the corresponding values in euros in column C, starting in cells C2 through C6.
- Write the exchange rate in cell B2.
- Now write the formula = A2*$B$2 in cell C2 and press Enter.
- Click anywhere outside of cell C2 and then again on cell C2 to highlight the Fill button.
- Pull down cell C2 to cell C6, and it will display all the euro values in sequence.
Adding a unit of currency is one of the difficulties of this method. Nevertheless, it’s better than buying a special tool specifically for this.
