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

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:

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:

• Press Enter.

• Next, drag the Fill handle icon.
• As a consequence, you will get the following Convert USD to different 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:

• Press Enter.

• Next, drag the Fill handle icon.
• As a consequence, you will get the following Convert USD to different 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:

• Press Enter.

• Next,drag the Fill handle icon.
• As a consequence, you will get the following Convert USD to different 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:

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:

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:

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:

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.

Related Articles

• How to Convert Text to Currency in Excel
• How to Get Exchange Rate by Date in Excel