Exchange Rate support in Excel 365

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

Exchange Rate support in Excel 365 - Exchange Rate support in Excel 365

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).

2330 Real time Excel small 212x300 - Exchange Rate support in Excel 365Real 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.

  1. 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
  2. Go to Data | Data Types and click the Stock icon.
  3. Wait for a few seconds while Excel gets the data from Microsoft’s servers.
  4. The cell changes to show a little ‘stock’ icon at left.
  5. 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’.
  6. Repeat step 5 for each field you want to display.

Exchange Rates in Excel 365 opt - Exchange Rate support in Excel 365

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

Exchange Rate support in Excel 365 2 - Exchange Rate support in Excel 365

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.

Exchange Rate support in Excel 365 1 - Exchange Rate support in Excel 365

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’.

Exchange Rate support in Excel 365 3 - Exchange Rate support in Excel 365

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.

Exchange Rate support in Excel 365 4 - Exchange Rate support in Excel 365

This feature is NOT in Excel 2019, Excel 2016 or earlier versions.  But there are workarounds for older ExcelsReal-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

subs profile e1563205311409 - Exchange Rate support in Excel 365
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address