The rich data types make it easy to get a live currency exchange rate in Excel. You can then convert currencies with an Excel formula that updates when the exchange rate changes.
Define the Currency Exchanges
Firstly, you need to enter the currency codes for the currencies that you want to convert separated by either a colon “:”, a forward slash “/”, or simply a space.
In the following image, currency codes have been entered to convert British pounds (GBP) into euros, US dollars, Danish krone and more. A forward slash was used to separate the currency codes to convert.
Select the range containing the currency codes and apply the Currencies data type by clicking Data > Currencies.
The Currencies data type uses information from the Stocks data type. So, if you cannot see Currencies in your data types gallery, use the Stocks data type instead.
A disclaimer is shown with a link to learn more about the source this information. And the range of currency codes now have the Stocks data type icon beside the text.
You have converted the text to a rich data type. This means that the single cells that contain the codes are now rich with many fields of information.
Beyond the scope of this tutorial, you can also create data types in Power Query, Power BI, and there is a Geography data type.
Get the Currency Exchange Rate in Excel
You can now easily insert the currency exchange rate to Excel ready for use by a formula or some other feature of Excel.
Select the range of data types, click the Insert Data icon and click Price.
The prices are inserted for all currency codes. They are even formatted appropriately. Awesome!!
You can also use the following simple formula to return the Price field from the data type in cell B3 and fill the formula to the other cells in the range.
B3.Price
Convert Currency in Excel
The following formula is used in column E to convert the currencies in Excel.
=C3*D3If the exchange rate was not inserted as a column in Excel, you can still access the Price field using the following formula in column E.
=B3.Price*D3Set When the Currency Exchange Rate Updates
You can specify when the currency exchange rates update in Excel by modify the refresh settings of the data type.
Right-click the range of currency codes and click Data Type > Refresh Settings.
By default, the data type refreshes automatically every 5 minutes. So, you would have a live currency exchange rate in Excel.
There are options to change this setting to update when the workbook is opened, or to switch to a manual refresh for more control over when it updates.
Have you used the rich data types in Excel before?
Leave a comment and let us know.
Leave a Reply