Exchange Rate support in Excel 365
The latest global exchange rates can go directly into Excel 365 for Windows and Mac. No plugins or extras are necessary because it’s in Excel 365 though it’s hiding away.
Foreign exchange rates are part of the Stock data type. They weren’t part of the original Stocks data feed but have been added very quietly.
Excel 365 can show the latest exchange rates for a very wide range of currencies. About 160 currencies are supported by our count. Here’s a selection to give you some idea of the range.
The currency symbol can be formatted correctly for each currency, in the above image see the UK Sterling, Euro, Russian Rouble prices among others. As you’ll see, it’s a little fiddly but is better than what we had in the past (i.e. nothing).
Real Time Excel
Real-Time Excel – get live stock, fund and bond prices, currency rates and more includes working spreadsheets for this tip and many other examples of getting live information into Excel. Buy and get it today (just a few minutes from now) .
How to get an exchange rate
Type in a currency pair using the common three-letter currency codes, separated by either a colon : a slash / or nothing e.g. USD/CAD USD:CAD and USDCAD will all work.
If you’ve used the Stock or Geography data types, then this is exactly the same. All you’ll need is the currency code pairs (see below).
For newbies here’s the step by step.
- Type a currency pair into an Excel cell, separated by a slash / then press Enter
We’ll use USD (US Dollar) and GBP (UK Sterling) – USD/GBP (best) USD:GBP or USDGBP - Go to Data | Data Types and click the Stock icon.
- Wait for a few seconds while Excel gets the data from Microsoft’s servers.
- The cell changes to show a little ‘stock’ icon at left.
- Use the little field picker icon to select the field to add into the next empty cell to the right of the main data cell. The Exchange Rate is labelled ‘Price’.
- Repeat step 5 for each field you want to display.
Currency Pairs
The format is:
FROM currency : TO currency
The first currency is the one that buying the second currency.
Another way express it: using ‘From Currency’ to buy the second named currency.
Microsoft calls the two [From currency] and [Currency]
USD/CAD – US dollars to Canadian dollars
GBP/EUR – UK Pounds Sterling to Euro
INR/SGD – Indian Rupee to Singapore Dollars
HKD/CNY – Hong Kong Dollars to China Renminbi
JPY/RUB – Japanese Yen to Russian Ruble
It’s best to enter currency pairs with a slash separator like EUR/USD . That seems to reduce the risk of a mixup with a similar stock/fund/bond codes (which can be more than three characters and can include a colon).
Exchange Rate Card
A pop-up card shows all the details for that exchange rate pair.
Display Data Cards by:
- Clicking on the icon to the left of the main data cell.
- Right-click on the data cell and choose ‘Show Card’.
- Shortcut: Ctrl + Shift + F5 (Cmd + Shift + F5 for Mac)
Currency Symbol Formatting
Excel 365 has a nifty little trick up it’s sleeve which makes the rate currency symbol correct for each currency.
Look at the sample table above, each ‘Rate’ has the correct currency symbol (Euro, Rupee etc.).
This should happen automatically if the cell is empty or has the General cell format.
If not, a warning icon appears. Click on the pull-down list and choose ‘Update format’.
If there are many wrong formats, select the column and then choose Warning | Update Format to correct them all.
Warning about rates
The quoted rates are the spot rates for very large transactions. Individuals and businesses aren’t able to get those rates for changing money.
Generally speaking, you need to adjust the Excel given ‘spot’ rate to give something like what you get from a bank or foreign exchange desk.
Try these broad ‘rule of thumb’ adjustments.
ATM Withdrawal +2%
Credit Card +3-4%
Cash exchange +5%
Always check the quoted rate and fees confirming a currency exchange. Many places ‘no commission’ because they adjust the exchange rate to include their margin. Others impose a flat fee and a generous exchange rate (generous to them – not you).
We’ve had good experience and rates from CurrencyFair.
Who gets Exchange Rates in Excel?
Any up to date Excel 365 program or app has support for live ‘real time’ prices for stocks, funds and currency exchange rates. Look for the Stock icon on the Data tab.
This feature is NOT in Excel 2019, Excel 2016 or earlier versions. But there are workarounds for older Excels, Real-Time Excel – get live stock prices, currency rates and more has details with sample worksheets to get you started.
Fix Excel when Stock and Geography data types go missing
Stock data type search tips in Excel
Stock and Geography Data Types coming to Office for Mac