Free Practice WorkbookWhen your scenario requires currency conversion on a daily basis, you can do it inside Excel as it is able to get real-time conversation data for you. We have multiple methods below to show how you can do this in Excel!
Key Takeaways:
- Inside Excel, you can integrate currency conversion with financial analysis, automation, and reporting.
- Use Excel’s Currency Data Type for live exchange rates.
- Data Validation makes selecting currency pairs quick and error-free.
Table of Contents
Real-Time Exchange Rates in Excel
Setting Up the Basic Structure
Create a table with your data:
Now enter the exchange rate. For example, if $1 = ₹83.20, I placed that into the “Exchange Rate” column and created a “Converted Amount” column using a basic formula:
=A2 * D2
Making It Dynamic with Data Types
I discovered that Excel has a Currency Data Type feature (available in Microsoft 365), which pulls real-time exchange rates.
All I had to do was:
- Enter USD/INR in a cell.
- Go to the Data > Data Types > Currency.
Excel converted my text into a linked data type. Now get the rate with:
=D1.Price
Enabling Currency Selection
I added dropdown lists for currencies using Data Validation. This way I could pick any currency pair without typing.
- I list common currencies like USD, EUR, GBP, INR, and JPY.
- Created dropdowns in the From Currency and To Currency columns.
Tips & Tricks
Dealing with Currency Decimals and Rounding
You can round off to 2 decimal places for currencies like USD or EUR:
=ROUND(A2 * D2, 2)
Formatting Currency
Once I had my converter working, I realized numbers alone weren’t enough. Seeing 1000 doesn’t tell me if I’m dealing with ₹1,000 or $1,000.
I used Excel’s Number Formatting to make sure every converted amount displayed the correct currency symbol.
Select the “Converted Amount” column.
Right-click and chose Format Cells.
- In the dialog box, select Currency and then pick the appropriate symbol (₹, $, €, £).
For dynamic formatting, I use this formula:
=TEXT(A2*D2, IF(C2="USD","$#,##0.00", IF(C2="INR","₹#,##0.00", "#,##0.00")))
FAQs
How do I avoid too many decimal places in conversions?
Use the ROUND function to cap your results to a specific number of decimal places.
Can I switch between different currencies easily?
Yes. Data Validation lets you create a dropdown list, so you can pick a currency without retyping anything.
How do I display the correct currency symbol in Excel?
The simplest way is Currency formatting (so you’ll see symbols like ₹, $, €, or £). If you want the symbol to change automatically based on the currency you select, you can use the TEXT function with a bit of conditional logic.
How does Excel compare to online currency converters?
Excel is great if you want conversions that are automated, tied directly to your budget/financial sheets, and customized to your setup—without having to copy/paste values back and forth.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
