If you are looking for some special tricks to automate currency conversion in Excel, you’ve come to the right place. In Microsoft Excel, there are numerous ways to automate currency conversion in Excel. This article will discuss five methods to automate currency conversion in Excel. Let’s follow the complete guide to learn all of this.
How to Automate Currency Conversion in Excel: 5 Easy Ways
In the following section, we will use five effective and tricky methods to automate currency conversion in Excel. This section provides extensive details on five methods. You should learn and apply all of these to improve your thinking capability and Excel knowledge.
1. Automate Currency Conversion Using Multiplication
Here, we will demonstrate how to automate currency conversion in Excel. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. Here, we have a dataset where the product ID and the price of each product are shown in terms of USD. Now, we want to convert the price of each product from USD to EURO. Let’s walk through the steps to automate currency conversion.
📌 Steps:
- Firstly, we will use the following formula in the cell D5:
=C5*$G$6
Here, cell G6 is the conversion rate of USD to EURO.
- Press Enter.
- Next, drag the Fill handle icon.
- As a consequence, you will get the following Convert price of USD to EURO.
Read More: How to Change Default Currency in Excel
2. Utilizing Nested IF Formula to Automate Currency Conversion
Now, let’s walk through the steps to automate currency conversion by using the IF function. Here, we will use another method to automate currency conversion by utilizing the Nested IF Formula. In the following dataset, we want to convert USD to different currencies such as EUR, JPY, GBP, CHF, CAD, and AUD.
📌 Steps:
- Firstly, we will use the following formula in the cell D5:
=IF(C5="eur",0.94,IF(C5="jpy",127.4,IF(C5="gbp",0.8,IF(C5="chf",0.97,IF(C5="cad",1.28,IF(C5="aud",1.41))))))*B5
- Press Enter.
- Next, drag the Fill handle icon.
- As a consequence, you will get the following Convert USD to different currencies
🔎 How Does the Formula Work?
- Here, we define e condition whether cell C5 is equal to EUR, JPY, GBP, CHF, CAD, or AUD. Once this condition is met, the function will return a value and the value will be multiplied by cell B5.
- Finally, we will get the converted currencies.
Read More: How to Create Real Time Currency Converter In Excel
3. Using VLOOKUP Function to Automate Currency Conversion
Now, we will use another effective method to automate currency conversion by utilizing the VLOOKUP function. In the following dataset, we want to convert USD to different currencies such as EUR, JPY, GBP, CHF, CAD, and AUD. Let’s walk through the steps to automate currency conversion.
📌 Steps:
- Firstly, we will use the following formula in the cell D5:
=B5*VLOOKUP(C5,$F$5:$G$10,2,0)
- Press Enter.
- Next, drag the Fill handle icon.
- As a consequence, you will get the following Convert USD to different currencies
🔎 How Does the Formula Work?
- Here, the VLOOKUP function looks for a value in the table array F5:G10 and then returns a value in the row from the column we specify.
- Then, we multiply the returned value by cell B5.
- Finally, we will get the converted currencies.
Read More: Currency Conversion Using VLOOKUP in Excel
4. Combination of INDEX and MATCH Functions in Excel
Now, we will use another quickest method to automate currency conversion by utilizing INDEX and MATCH functions. In the following dataset, we want to convert USD to different currencies such as EUR, JPY, GBP, CHF, CAD, and AUD. Let’s walk through the steps to automate currency conversion.
📌 Steps:
- Firstly, we will use the following formula in the cell D5:
=INDEX($G$5:$G$10, MATCH(C5,$F$5:$F$10,0))*B5
- Press Enter.
- Next,drag the Fill handle icon.
- As a consequence, you will get the following Convert USD to different currencies
🔎 How Does the Formula Work?
- Here, the MATCH(C5,$F$5:$F$10,0) function returns the relative position of an item in the array F5:F10 that matches a specified value C5 in the specified order.
- Then, the INDEX function returns the 0.94 which is in the first row of the range F5:F10.
- Finally, multiply the whole combined functions by cell B5.
- As a result, we will get the converted currencies.
5. Using External XML Source in Excel
Here, we will link to an external source file in Excel and then, we will automate currency conversion. To do this you have to follow the following steps.
📌 Steps:
- Firstly, go to the Data tab, then select Get Data > From File > From XML.
- When the Import Data window appears, enter the URL: http://www.floatrates.com/daily/usd.xml in the File name. Then, click on Import.
- Next, click on Transform Data.
- When the Power Query Editor opens, go to the Item. Click on Table.
- Then, you have to click on the arrow. Click on OK.
- Next, click on OK.
- As a result, you will get the exchange rate columns.
- Next, select Home > Manage Columns > Choose Columns.
- Then, you have to check the required columns like the following and click on OK.
- Next, to return in Excel click on Close & Load.
- As a consequence, you will get the exchange rate in the spreadsheet.
- Now, you have to create a new worksheet,
- we will use the following formula in the cell C5:
=VLOOKUP($C$4,usd[[item.targetCurrency]:[item.exchangeRate]],3,FALSE)
Here, C4 is the GBP currency and the final argument is False.
- Press Enter.
- The [[item.targetCurrency]:[item.exchangeRate]] is the lookup data in the linked XML file.
- we will use the following formula in the cell C6:
<span style="color: #000000;">=VLOOKUP($C$4,usd[[item.targetCurrency]:[item.exchangeRate]],3,FALSE)*B6</span>
Here, C4 is the GBP currency and the final argument is False. The whole formula is multiplied by cell B6 to get the converted currency.
- Press Enter.
- Next, drag the Fill handle icon.
- As a result, you will be able to convert USD TO GBP.
- we will use the following formula in the cell D5:
<span style="color: #000000;">=VLOOKUP($D$4,usd[[item.targetCurrency]:[item.exchangeRate]],3,FALSE)</span>
Here, D4 is the EUR currency and the final argument is False. The [[item.targetCurrency]:[item.exchangeRate]] is the lookup data in the linked XML file.
- Press Enter.
- we will use the following formula in the cell D6:
=VLOOKUP($D$4,usd[[item.targetCurrency]:[item.exchangeRate]],3,FALSE)*B6
Here, C4 is the GBP currency and the final argument is False. The whole formula is multiplied by cell B6 to get the converted currency.
- Press Enter.
- Next, drag the Fill handle icon.
- As a consequence, you will get the following Convert USD to different currencies
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
That’s the end of today’s session. I strongly believe that from now you may automate currency conversion in Excel. If you have any queries or recommendations, please share them in the comments section below.