How to Convert Currencies in Excel (7 Methods)

Currency conversion in Excel means converting the value of one currency into the equivalent value of another currency using Excel functions and features.

In this Excel tutorial, we’ll learn how to do currency conversion in Excel.

In the following overview image, we have some currencies in USD. Here, we have converted it to Euro and GBP using some formulas.

In this blog post, we will convert currency in Excel using a simple arithmetic formula; nested IF formula; VLOOKUP function; INDEX-MATCH formula; currency converter; web query; and VBA. We will also show how to change the default currency in Excel.

⏷Simple Arithmetic Formula for Currency Conversion
⏷Currency Conversion by Applying Nested IF Function
⏷Using the VLOOKUP Function
⏷Using INDEX MATCH to Convert Currency
⏷Currencies Data Type to Create Currency Converter
⏷Converting Currency Using Web Query
⏷Automate Currency Conversion Using VBA
⏷How to Change Default Currency in Excel?

1. Using Simple Arithmetic Formula for Currency Conversion in Excel

In this section, we will convert currency using a simple arithmetic formula in Excel.

Let’s see the dataset first. We have some amounts in USD in column E. We have a list of currencies in the range B7:B14 and their corresponding conversion rate with respect to USD in C7:C14. The conversion rate of USD to EUR is in cell C7. We will use it in our formula to get the converted currency.

Follow the steps below to convert USD to EUR in Excel.

Steps:

• Select cell F7 => Insert the formula:

• Press Enter. It will return the EUR currency equivalent to the USD currency value.
• Use the Fill Handle tool to copy the formula in the other cells below.

2. Currency Conversion in Excel by Applying Nested IF Function

In this part, we will use nested IF functions in Excel to convert currency. In the following dataset, we have currency amounts in USD in the range B6:B15. We have kept different currencies and their corresponding conversion rates from USD to other currencies in the range B18:C25.

Follow the steps below to accomplish the task.

Steps:

• Select cell D6 => Write the following formula with the IF function:

• Press Enter => Use the Fill Handle tool to copy the formula to the next cells below.

Here, we have converted USD to EUR, USD to CAD, USD to INR, and many more.

3. Using VLOOKUP Function to Convert Currency in Excel

In this section, we will perform currency conversion in Excel using the VLOOKUP function.

We have a list of currencies in column B and conversion rates in column C in the following dataset. In cell F7, we have made a drop-down list with the currency names. We will select a currency from the drop-down list and get the output in cell G7 with the converted currency amounts.

The formula we will use with the VLOOKUP function in G7 is:

The following GIF shows the result in cell G7 of converted currency values as we change the currency name and the currency amount.

4. Using INDEX MATCH Functions to Convert Currency in Excel

Here, we will combine the INDEX function and the MATCH function to do the currency conversion.

The formula for this method is:

The following GIF shows the result in cell G7 of converted currency values as we change the currency name and the currency amount.

Read More: How to Convert Text to Currency in Excel

5. Using Currencies Data Type to Create Currency Converter in Excel

In this section, we will create a real-time currency converter in Excel using the Currencies data type feature. Converting currencies using the Currencies data type feature is only available in Microsoft 365. Also, the data updates automatically as the currency rate changes. Here, the exchange rates were extracted on 11/29/2023 while writing this article.

In our dataset, column B contains currency in USD, and column C contains the currency conversion pair. They are in the form of currency before and currency after. For example, the USD/EUR pair means we will convert USD to EUR currency.

Follow the steps below to perform the work.

Steps:

• Select the whole range (B7:C15) => go to the Insert tab, => click on Table. (Alternatively, you can convert to a table by pressing CTRL+T together)

• The Create Table dialog box will appear. Check the box My table has headers => click OK.

• After that, select the range C7:C15 => Go to the Data tab => Click Currencies from the Data Types section.

We will find that a new symbol has appeared before all the pairs in Column C.

•  Select the range C7:C15 => Click on the Add Column option => Select Price to get the conversion rates in a new column.

• We will get the conversion rates in Column D.

You can add a new column that contains the last time a currency was updated.

• Select C7 => Click on the Add Column option => Select Last trade time. 

You will get the following output in column E, containing the last trade time of the currencies (11/29/2023, 7:09)

• Finally, we will apply the following formula in cell F7 to find the converted currency amounts.

Read More: How to Automate Currency Conversion in Excel

6. Converting Currency in Excel Using Web Query

In this part, we will use Power Query to get conversion rates from a website on a given date and bring them into a worksheet to convert currency.

In our dataset, Column B contains some amounts in USD, and Column C contains the desired currencies we want to convert into.

Follow the steps below to convert currency using a web query.

Steps:

• Click the Data tab => Select From Web in the Get & Transform Data group.

• In the From Web dialog box, paste the following website link => and click OK.

The Navigator window will open up. From here, you can choose the table that needs to be loaded.

• After selecting Table 1 in the left pane, click on Transform Data.

The table will be loaded in the Power Query Editor.

• In the Power Query Editor, click Close & Load => Close & Load To…

The Import Data dialog box will pop out in the worksheet.

• Select the option Existing worksheet => Select location in the sheet. => Press OK.

The table will appear in the desired location, as shown in the image below. The first column of the table contains the name of the currency. The second column contains the conversion rate from USD to the corresponding currency. The last column contains the reverse conversion rate, the rate from any currency to USD. The exchange rates were taken on 11/29/2023 while writing this article.

Click to Get Full View of the Image

• Add two new columns beside column C => Name column D as Conversion Rate and column E as Converted Currency.

• In cell D7, write the following formula to calculate the conversion rate:

• Finally, apply the following formula to get the converted currencies:

7. Automate Currency Conversion in Excel Using VBA

In this section, we will learn how to automate currency conversion in Excel using VBA Macro.

Follow the steps below.

Steps:

• Press Alt+F11 to open the VBA editor.
• Click Insert => UserForm to get a blank user form to edit.

• Use the Toolbox of the UserForm to insert various elements.
• Insert 2 ListBox in the upper portion of the UserForm and 2 TextBox in the lower portion of the UserForm.
• Also, insert the CommandButton in the lower right portion of the UserForm.

• Give an appropriate name for the CommandButton => Right-click on the button => Select View Code.

• Copy and paste the following code into the UserForm Code window: (This code contains the necessary conversion rates of one currency to another.)

Private Sub CommandButton1_Click()
' Conversion ex_rates matrix
Dim ex_rates() As Variant
ex_rates = Array(Array(1, 1.38475, 0.87452, 163.83), _
Array(0.722152, 1, 0.63161, 150.62), _
Array(1.143484, 1.583255, 1, 188.16), _
Array(1.143484, 0.0066, 0.0053, 1))
' Get selected indices from ListBoxes
Dim x As Integer, y As Integer
x = ListBox1.ListIndex
y = ListBox2.ListIndex
' Check if both indices are selected
If x >= 0 And y >= 0 Then
' Perform conversion and update TextBox2
TextBox2.Value = TextBox1.Value * ex_rates(x)(y)
End If
End Sub

• We also need a code for two list boxes. We will assign the associated code to the UserForm_Initialize section of the same window.

Private Sub UserForm_Initialize()
With ListBox1
.AddItem "Euro"
.AddItem "Us Dollar"
.AddItem "British Pound"
.AddItem "Japanese Yen"
End With
With ListBox2
.AddItem "Euro"
.AddItem "Us Dollar"
.AddItem "British Pound"
.AddItem "Japanese Yen"
End With
TextBox1.Value = "Enter Input"
TextBox2.Value = "Output"
End Sub

• Assign the following codes in the same code window to make the ListBoxes and TextBox1 dynamic.

Private Sub TextBox1_Change()
TextBox2.Text = ""
End Sub

Private Sub ListBox2_Change()
TextBox2.Text = ""
End Sub

Private Sub ListBox1_Change()
TextBox2.Text = ""
End Sub

We want that whenever we click on the TextBox that contains “Enter Input“, both the TextBox will be blank. Add the following code in the same window.

Private Sub TextBox1_Enter()
'This code runs when the user clicks on TextBox1
'Check if the current text is "Enter Amount"
  If TextBox1.Value = "Enter Input" Then
    'Clear the text and make it a null textbox
    TextBox1.Value = ""
    TextBox2.Value = ""
  End If
End Sub

Our UserForm is ready to use now.

We will insert a button in the worksheet.

• Go to Developer => Insert => Button (Form Control).

• To assign a Macro to the button, right-click on the button => Assign Macro => Paste the following code.

Sub Button1_Click()
UserForm1.Show
End Sub

We have completed our task of automation. In the following GIF, you will get an idea of how the button works in the worksheet.

Read More: Currency Conversion Using VLOOKUP in Excel

How to Change Default Currency in Excel?

When you apply the default currency in Excel, we will find that it automatically assigns the $ sign to the currency amount. But if we need a different currency, we need to change the default currency in Excel format.

The following image shows a currency amount in cell G7. As we said above, Excel has automatically considered it to be USD currency. But we want it to be in INR currency.

Follow the steps below to change the default currency.

Steps:

• Select the cell G7 => Click Number group in Home tab => Click the Number Format symbol.

It will open the Format Cells dialog box.

• In the dialog box, select Number => Currency => Select the desired currency from the Symbol: drop-down menu => Click OK.

You will see that the currency symbol has been changed to INR.

Read More: How to Change Default Currency in Excel

Download Practice Workbook

Download the workbook below to practice yourself.

This article has shown all the possible ways to convert currencies in Excel. Apply any one of the IF, VLOOKUP, INDEX-MATCH formulas, or Currency data types in Excel if you already have a currency conversion rate. However, if you want to get the real-time conversion rate from any external source, the web query conversion method would come in handy. Moreover, VBA can automate the currency conversion in Excel. Leave a comment for any further queries.

Currency Conversion in Excel: Knowledge Hub

<< Go Back to Learn Excel