Nazmul Hossain Shovon
In this tutorial, I am going to share with you 2 effective ways to create a real-time currency converter in excel. You can easily apply these methods to convert between various currency types. To achieve this task, we will also see some useful functions that might come in handy in many other excel related tasks. Also, in the last section, I will show you how to create a currency exchange rate formula.
Download Practice Workbook
You can download the practice workbook from here.
2 Effective Ways to Create Real Time Currency Converter in Excel
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 4 columns. Initially, we are keeping all the cells in General format and the monetary values in Accounting format. For all the datasets, we have 5 unique columns which are Currency Pair, Price, From Currency and Currency. Although we may vary the number of columns later on if that is needed.
1. Using Currencies Data Type
In this first method, we will see how to create a real-time currency converter in excel using currencies data type.
Steps:
- First, select the cells from B5 to B10, go to the Data tab, and click on Currencies.
- Then, again select the previous cells and click on Insert Data.
- Here, click on Price.
- Next, again click on Insert Data and select From Currency.
- Now, click on Currency.
- Consequently, this will give you the data as follows.
- Now, go to another sheet and insert the following formula in cell E5:
=UNIQUE('Currency Pair'!E5:E10)- After that, press Enter and this will give a list of currencies.
- Now, select cells C4 and C5.
- Then, go to the Data tab and under Data Tools, select Data Validation.
- After that, select List from Allow drop-down and type in the Source as in the image below.
- Now, click OK.
- Then, select a suitable currency as you want for the From and To fields.
- Next, enter an amount and insert this formula in cell C7:
=XLOOKUP(C4&C5,'Currency Pair'!D5:D10&'Currency Pair'!E5:E10,'Currency Pair'!C5:C10)*C6
- Finally, press the Enter key to get the result in the new currency.
Read More: How to Automate Currency Conversion in Excel (5 Easy Methods)
Similar Readings
- How to Convert USD to Euro in Excel (3 Useful Methods)
- Convert GBP to USD in Excel (4 Suitable Methods)
- How to Convert Dollar into Rupees in Excel (2 Easy Methods)
2. Utilizing Web Query in Excel
If you are using an older version of excel, then this method will suit you to create a real-time currency converter quickly.
Steps:
- To begin with, navigate to the Data tab and click From Web.
- Then, paste the below link in the URL field and click OK.
- Now, click Connect.
- Next, select the option that has the table and click on Load.
- As a result, this will load the data in a table.
- Now, select cell C5 and click on Data Validation under Data Tools.
- Here, select List and insert the source range as in the image below.
- Then, click OK.
- Now, insert the following data as below and also type in the following formula in cell C7:
=VLOOKUP(C5,'Web Query'!B5:D10,2,FALSE)*C6
- Finally, press Enter to confirm.
Read More: Currency Conversion Using VLOOKUP in Excel (2 Suitable Examples)
Currency Exchange Rate Formula In Excel
In the following steps, we will learn about the currency exchange rate formula in excel.
Steps:
- For this, go to cell D5 and enter the following formula:
=B5*G5
- Finally, press Enter and copy this formula down using Fill Handle.
Read More: Formula to Convert Currency in Excel (4 Useful Examples)
How to Download Historical Exchange Rates in Excel
We may require the historical exchange rates in excel in some situations. You can accomplish this by following the steps below.
Steps:
- To start with, go to the Data tab and click on From Web.
- Then, insert the data source link in the URL field and click OK.
- Next, select Table 1 and click Load.
- Immediately, this will download the specific table data from the given link.
Conclusion
I hope that you were able to apply the methods that I showed in this tutorial on how to create a real-time currency converter in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
