2 Ways to Convert Currency in Excel That Many Users Still Don’t Know

Converting currency can be time-consuming and complex if you have to do it manually. But don’t worry, FPT Shop will guide you on how to perform this operation quickly and easily with Excel. By following the currency conversion methods in Excel below, you not only save time but also ensure the accuracy of financial data.

Benefits of Currency Conversion in Excel

Currency conversion is an important part of financial work, especially for businesses operating internationally or individuals regularly involved in daily transactions with different currencies. In addition, tracking and managing transactions and assets in multiple currencies is always a major challenge for anyone.

However, Excel is a powerful tool that you can take advantage of to easily perform currency conversions. Just knowing how to convert currency in Excel, you can accurately convert any currency in the world without needing to be a financial expert.

How to Convert Currency in Excel

There are many ways to convert currency in Excel. However, the general formula for these methods is:

Step 1: First, you need to open the Excel file that contains the currency data you want to convert.

Step 2: Select the cell(s) that you want to convert the currency. It can be a cell in a worksheet or a cell within a formula.

Step 3: Excel provides many functions for currency conversion. One of the most popular functions is VLOOKUP, but you can also use the INDEX function if necessary. The details of how to use these functions will be shared by FPT Shop in the following sections of the article.

Step 4: Enter the necessary information for the function. This includes the amount you want to convert, the original currency unit, and the currency unit you want to convert to.

Step 5: Excel will then perform the conversion and display the result in the selected cell(s).

With the built-in functions in Excel, you can confidently perform currency conversions without having to worry about complex formulas. This article will provide detailed instructions on how to use these functions and provide specific examples to help you easily and efficiently convert currency in Excel.

Convert Currency in Excel with VLOOKUP Function

Converting currency in Excel can become complex, especially when you have to do this for multiple transactions or different currencies in a day. However, with the help of Excel’s VLOOKUP function, this operation can be done easily.

Step 1: First, you need to have complete input data, including a reference table with exchange rates between the currency units you want to convert. For example, you can create a reference table like the one above.

Step 2: Use the VLOOKUP function to perform currency conversion. The general syntax of the VLOOKUP function is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where:

• lookup_value: The amount you want to convert.
• table_array: The range of the reference table (in this case, the table you created from G5:H9).
• col_index_num: The column index that contains the conversion rate (e.g., if the rate is in the 2nd column, then col_index_num is 2).
• [range_lookup]: Set this value to FALSE to find an exact match.

Suppose you want to convert 1,000 USD from cell A5 to EUR in cell A6, you can use the following formula in cell A6:

=VLOOKUP(“EUR”, $G$5:$H$9, 2, FALSE) * A5

Note: You need to fix the reference range (here, G5:H9) by adding the $ sign (press the F4 key) before the reference cell.

Step 3: When you Enter on the keyboard, the formula will return the result of 850 EUR in cell A6.

You can use the Excel copy formula tool to convert currency from USD to EUR in the cells below.

Step 4: For other currencies, you can also use the same formula. For example:

• GBP column (British Pound): =VLOOKUP(“GBP”, $G$5:$H$9, 2, FALSE) * A5
• JPY column (Japanese Yen): =VLOOKUP(“JPY”, $G$5:$H$9, 2, FALSE) * A5
• VND column (Vietnamese Dong): =VLOOKUP(“VND”, $G$5:$H$9, 2, FALSE) * A5

The result is that we will have a currency conversion table as shown in the image.

Convert Currency in Excel with INDEX Function

Similarly, you can also use the INDEX function to convert currency in Excel.

Step 1: First, you need to prepare the necessary data. You need a reference table that contains the exchange rates between the currency units you want to convert as shown in the image.

Step 2: Use the INDEX and MATCH functions to perform currency conversion. The general syntax of the INDEX and MATCH functions is as follows:

=INDEX (return_array, MATCH (lookup_value, lookup_array, [match_type]))

Where:

• return_array: The range that contains the values you want to convert (exchange rates in this case).
• lookup_value: The amount you want to convert (e.g., 100 USD).
• lookup_array: The range that contains the currency units you want to convert (the “Currency Unit” column in the reference table).
• [match_type]: Use 0 to find an exact match.

Suppose you want to convert 500 USD from cell A5 to EUR in cell A6, you can use the following formula in cell A6:

=INDEX($H$5:$H$9, MATCH(“EUR”, $G$5:$G$9, 0)) * A5

Note: You need to fix the reference range by adding the $ sign (press the F4 key) before the reference cell.

Step 3: When you Enter on the keyboard, the formula will return the result of 425 EUR in cell A6.

Step 4: You can use the Excel copy formula tool to convert currency from USD to EUR in the cells below.

Step 5: For other currencies, you can also use the same formula. For example:

• GBP column (British Pound): =INDEX($H$5:$H$9, MATCH(“GBP”, $G$5:$G$9, 0)) * A5
• JPY column (Japanese Yen): =INDEX($H$5:$H$9, MATCH(“JPY”, $G$5:$G$9, 0)) * A5
• VND column (Vietnamese Dong): =INDEX($H$5:$H$9, MATCH(“VND”, $G$5:$G$9, 0)) * A5

The result is that we will have a currency conversion table as shown in the image.

Conclusion

If you regularly need to convert currency in Excel, FPT Shop believes that this article will be helpful to you. We hope that you will be able to handle your financial data professionally and quickly, providing effective support for your work.

You may also like

Simple guide on how to use the ROWS function in Excel that not everyone knows

The ROWS function is a commonly used and familiar function in Excel for office workers. In addition to being used to calculate the number of rows, this calculation function can also be flexibly applied to support combinations with many other common functions such as INDIRECT, IF, SUM, MIN, MAX,…

What is Excel Macro? Unleash the hidden power of Excel Macro for optimizing work productivity on spreadsheets.

What is Macro Excel? Let’s explore the amazing power of Macro Excel in optimizing productivity and automating tasks on spreadsheets with FPT Shop.