How to Automate Currency Conversion in Excel (5 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Automate Currency Conversion Using Multiplication

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

Automate Currency Conversion Using Multiplication


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.

Utilizing Nested IF Formula to Automate Currency Conversion

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

How to Automate Currency Conversion in Excel

  • Next, drag the Fill handle icon.
  • As a consequence, you will get the following Convert USD to different currencies

Utilizing Nested IF Formula to Automate Currency Conversion

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

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.

Using VLOOKUP Function in Excel

  • Next, drag the Fill handle icon.
  • As a consequence, you will get the following Convert USD to different currencies

Using VLOOKUP Function in Excel

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

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.

Combination of  INDEX and MATCH Functions to Automate Currency Conversion

  • Next,drag the Fill handle icon.
  • As a consequence, you will get the following Convert USD to different currencies

Combination of  INDEX and MATCH Functions to Automate Currency Conversion

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

Using External XML Source in Excel

  • When the Import Data window appears, enter the URL: http://www.floatrates.com/daily/usd.xml in the File name. Then, click on Import.

How to Automate Currency Conversion in Excel

  • Next, click on Transform Data.

Using External XML Source in Excel

  • When the Power Query Editor opens, go to the Item. Click on Table.

How to Automate Currency Conversion in Excel

  • Then, you have to click on the arrow. Click on OK.

Using External XML Source in Excel

  • Next, click on OK.

How to Automate Currency Conversion in Excel

  • As a result, you will get the exchange rate columns.

Using External XML Source in Excel

  • Next, select Home > Manage Columns > Choose Columns.

  • Then, you have to check the required columns like the following and click on OK.

Using External XML Source in Excel

  • Next, to return in Excel click on Close & Load.

How to Automate Currency Conversion in Excel

  • As a consequence, you will get the exchange rate in the spreadsheet.

Using External XML Source in Excel

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

How to Automate Currency Conversion in Excel

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

How to Automate Currency Conversion in Excel

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

How to Automate Currency Conversion in Excel

  • Next, drag the Fill handle icon.
  • As a consequence, you will get the following Convert USD to different currencies

Using External XML Source in Excel


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.


<< Go Back to Currency Conversion in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo