How to Convert Currencies in Excel (7 Methods)

We have some currencies in USD. We’ll convert them to Euro and GBP.

Overview Image of Currency Conversion 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?


Method 1 – Using a Simple Arithmetic Formula for Currency Conversion in Excel

We have some amounts in USD in column E, a list of currencies in the range B7:B14 and their corresponding conversion rate with respect to USD in C7:C14. Let’s convert USD to EUR in Excel.

Dataset of Currency Conversion in Excel

Steps:

  • Select cell F7 and insert the formula:
=E7*C$7

Applying Simple Arithmetic Formula


Method 2 – Currency Conversion in Excel by Applying a Nested IF Function

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.

Dataset to Apply Nested IF Formula

Steps:

  • Select cell D6.
  • Insert the following formula:
=IF(C6="USD",C$28,IF(C6="EUR",C$18,IF(C6="GBP",C$19, IF(C6="INR",C$23,IF(C6="CAD",C$21,IF(C6="JPY",C$27))))))*B6
  • Press Enter and use the Fill Handle tool to fill the column.

Applying Nested IF Formula to Convert Currency


Method 3 – Using the VLOOKUP Function to Convert Currency in Excel

We have a list of currencies in column B and conversion rates in column C. In cell F7, we 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.

Dataset to Apply the VLOOKUP Formula

  • Insert the following formula in G7:
=VLOOKUP(F7,B7:C15,2,0)*E7

Applying VLOOKUP Formula to Convert Currency

  • Changing the currency from the drop-down changes the result.

Changed Output for Changed Currency


Method 4 – Using INDEX-MATCH to Convert Currency in Excel

  • Use the following formula in G7:
=(INDEX(B7:C15,MATCH(F7,B7:B15,0),2))*E7

Applying INDEX-MATCH Formula to Convert Currency

  • If you change the values in E7 (amount) and F7 (currency), the result will change.

INDEX-MATCH Formula for Different Currencies

Read More: How to Convert Text to Currency in Excel


Method 5 – Using the Currencies Data Type to Create a Currency Converter in Excel

Converting currencies using the Currencies data type feature is only available in Microsoft 365.

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.

Dataset to Convert Currency Using Live Currency Converter

Steps:

  • Select the whole range (B7:C15).
  • Go to the Insert tab and click on Table (Alternatively, press Ctrl + T).

Converting Range into Table

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

Modifying Create Table Dialog Box

  • Select the range C7:C15.
  • Go to the Data tab.
  • Click Currencies from the Data Types section.

Using Currencies Option in Data Tab

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

Adding New Column Containing Conversion Rate

  • We will get the conversion rates in Column D.

Conversion Rate Added to New Column

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. 

Adding New Column Containing Last Rate Changing Time

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

Column Contains Time of Last Update

  • Apply the following formula in cell F7 to find the converted currency amounts.
=[@Amount]*[@Price]

Converted to Another Currency from USD

Read More: How to Automate Currency Conversion in Excel


Method 6 – Converting Currency in Excel Using Web Query

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

Dataset for Web Query Currency Conversion

Steps:

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

Choosing Get Data From Web Option

  • In the From Web dialog box, paste the following website link and click OK.
https://www.x-rates.com/table/?from=USD&amount=1

Pasting the Website Link for Extraction Currency Conversion Table

  • The Navigator window will open. Choose the table that needs to be loaded.
  • After selecting Table 1 in the left pane, click on Transform Data.

Transform Data from Navigator Window

  • The table will be loaded in the Power Query Editor.
  • In the Power Query Editor, click Close & Load and choose Close & Load To…

Loading Conversion Rate Table into Worksheet

  • The Import Data dialog box will appear.
  • Select the option Existing worksheet.
  • Select a location in the sheet.
  • Press OK.

Setting the Location for Conversion Rate Table

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.

Conversion Rate Table Loaded

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

Adding Columns to Table

  • In cell D7, use the following formula to calculate the conversion rate:
=VLOOKUP([@[ Currency to Convert]],Table_1,2,0)

Applying VLOOKUP Formula to Get Conversion Rate

  • Apply the following formula in column E to get the converted currencies:
=[@[Amount (USD)]]*[@[Conversion Rate]]

USD Currency Converted to Another Currency


Method 7 – Automate Currency Conversion in Excel Using VBA

Steps:

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

Inserting UserForm in VBA Editor

  • Use the Toolbox of the UserForm to insert various elements.
  • Insert two ListBoxes in the upper portion of the UserForm and two TextBoxes in the lower portion of the UserForm.
  • Insert a CommandButton in the lower right portion of the UserForm.

Inserting Elements from ToolBox

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

Right Click on Element to Assign Code

  • Copy and paste the following code into the UserForm code window:
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
  • 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
  • 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.

Final Look of the UserForm

We will insert a button in the worksheet.

  • Go to Developer and select Insert, then choose Button (under Form Controls).

Inserting Button for UserForm in Worksheet

  • To assign a Macro to the button, right-click on the button, select Assign Macro, and paste the following code.
Sub Button1_Click()
UserForm1.Show
End Sub

Assigning Macro to Button

  • Click on the button and you’ll get a UserForm as a calculator.

Both UserForm and Button Working


How to Change the Default Currency in Excel?

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

Default Excel Currency

Steps:

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

Opening Format Cells Dialog Box

  • This will open the Format Cells dialog box.
  • Select Number then Currency.
  • Select the desired currency from the Symbol: drop-down menu.
  • Click OK.

Changing Default Currency of Excel

  • The currency symbol has been changed to INR.

Default Currency Converted to Rupees

Read More: How to Change Default Currency in Excel


Download the Practice Workbook


Currency Conversion in Excel: Knowledge Hub


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Junaed-Ar-Rahman
Junaed-Ar-Rahman

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo