How to Create a Currency Converter with Microsoft Excel

Table of contents:

How to Create a Currency Converter with Microsoft Excel
How to Create a Currency Converter with Microsoft Excel
Anonim

Have you ever needed to know how many pesos a dollar is? Or how many yen does one pound equate to? Here is an easy way to create a currency converter within Excel that automatically fetches exchange rates from the Internet!

Steps

Create a Currency Converter With Microsoft Excel Step 1
Create a Currency Converter With Microsoft Excel Step 1

Step 1. Start Microsoft Excel and create a new workbook

Create a Currency Converter With Microsoft Excel Step 2
Create a Currency Converter With Microsoft Excel Step 2

Step 2. Alternatively, go to https://www.tlookup.com and download the Excel file generated daily with the exchange rates of the 34 major currencies over the last 90 days

Create a Currency Converter With Microsoft Excel Step 3
Create a Currency Converter With Microsoft Excel Step 3

Step 3. Starting in column D, enter the cell contents as shown here:

Create a Currency Converter With Microsoft Excel Step 4
Create a Currency Converter With Microsoft Excel Step 4

Step 4. Instead of typing all the names of the world currencies, import them with the current exchange rates from External Data Source

Start by clicking on Data> Import External Data> Import Data.

Create a Currency Converter With Microsoft Excel Step 5
Create a Currency Converter With Microsoft Excel Step 5

Step 5. Import the MSN MoneyCentral Investor Currency Rates file found in the Personal Data Source folder as follows:

Create a Currency Converter With Microsoft Excel Step 6
Create a Currency Converter With Microsoft Excel Step 6

Step 6. Import the data from the file into a new worksheet like this (but don't press "OK" yet):

Create a Currency Converter With Microsoft Excel Step 7
Create a Currency Converter With Microsoft Excel Step 7

Step 7. Before importing, click the Properties button and make the following changes:

click on the "Update every …" checkbox, set it to whatever value you want and click "Update on Opening", which will give you the new changes every time you open the document.

Create a Currency Converter With Microsoft Excel Step 8
Create a Currency Converter With Microsoft Excel Step 8

Step 8. Click OK in the Properties window and Import dialog box

Create a Currency Converter With Microsoft Excel Step 9
Create a Currency Converter With Microsoft Excel Step 9

Step 9. Once the data is imported into the new worksheet, change the sheet name to Conversion

Create a Currency Converter With Microsoft Excel Step 10
Create a Currency Converter With Microsoft Excel Step 10

Step 10. We now have all the exchange and names of the most important currencies in the world, but, to make this work, we need the names of the currencies in our first spreadsheet

First click on cell B5 and then, with the arrow keys on your keyboard, press the left arrow once.

Create a Currency Converter With Microsoft Excel Step 11
Create a Currency Converter With Microsoft Excel Step 11

Step 11. Copy all cells A5 to A46

Create a Currency Converter With Microsoft Excel Step 12
Create a Currency Converter With Microsoft Excel Step 12

Step 12. Paste the cells in the initial worksheet into column B as shown here:

Create a Currency Converter With Microsoft Excel Step 13
Create a Currency Converter With Microsoft Excel Step 13

Step 13. Now that we have all the currencies, we should create a drop down box to make sure we get the correct currency name

Click on cell D5, click on the Data menu and then Validate.

Create a Currency Converter With Microsoft Excel Step 14
Create a Currency Converter With Microsoft Excel Step 14

Step 14. In the Data Validation screen, choose List as the type of allowed values; the source is the range of cells that contain the names of the currencies

Make sure List in Cell is selected.

Create a Currency Converter With Microsoft Excel Step 15
Create a Currency Converter With Microsoft Excel Step 15

Step 15. Repeat the previous step in cell D12

In cell E6, enter the following formula: = Sum (VLOOKUP (D5, Conversion! $ A $ 5: $ C $ 56, 2, false) * E5) This will use a search function which will find the value corresponding to whatever currency is set in cell D5 and multiply it by the quantity found in E5. To get what a dollar is worth in a currency x, we need to tweak our formula a bit. write = Sum (VLOOKUP (D12, Conversion! $ A $ 5: $ C $ 56, 3, false) * E11)

Step 1. You are all done

Step 2. You can now customize the spreadsheet to reflect the base currency of your choice

Create a Currency Converter With Microsoft Excel Step 20
Create a Currency Converter With Microsoft Excel Step 20

Step 3. Decide your base currency (in this example we will use the South African Rand)

Create a Currency Converter With Microsoft Excel Step 21
Create a Currency Converter With Microsoft Excel Step 21

Step 4. In our example, the South African Rand is found on the Conversion Sheet on line 37

Create a Currency Converter With Microsoft Excel Step 22
Create a Currency Converter With Microsoft Excel Step 22

Step 5. Select the starting worksheet and insert an additional column between the currency names and the calculator

The calculator will now move to columns E & F.

Create a Currency Converter With Microsoft Excel Step 23
Create a Currency Converter With Microsoft Excel Step 23

Step 6. Highlight column A; from the top menu click Edit> Find

Create a Currency Converter With Microsoft Excel Step 24
Create a Currency Converter With Microsoft Excel Step 24

Step 7. Type "in dollars", select the Replace tab and click on Replace All

Closes the Find pop-up menu. Note that cells F6 and F12 will become N / A - don't worry about that! After completing this exercise, reselect the currencies in cells E5 and E12 and everything will be fine.

Select cell C4 (the first currency row) and enter the formula: = Conversion! B6 * Conversion! C $ 37. Select cell D4 and enter the formula: = Conversion! C6 * Conversion! B $ 37.

Create a Currency Converter With Microsoft Excel Step 27
Create a Currency Converter With Microsoft Excel Step 27

Step 1. Select cells C4 and D4 and drag the formula to the last currency row

Change the formula in cell F6 to: = SUM (VLOOKUP (E5, B4: B45, 2, FALSE) * F5) Change the formula in cell F12 to: = SUM (VLOOKUP (E12, B4: B45, 3, FALSE) * F11)

Create a Currency Converter With Microsoft Excel Step 30
Create a Currency Converter With Microsoft Excel Step 30

Step 1. On the currency calculator, change all dollar references to the currency you have selected

Create a Currency Converter With Microsoft Excel Step 31
Create a Currency Converter With Microsoft Excel Step 31

Step 2. You now have a currency calculator in a base currency of your choice

Now you can proceed to format the calculator with your preferences. We hide columns A, B, C & D and format the calculator with our corporate colors.

Advice

  • Exchange rates are constantly changing: you can have confirmation on the currency website at
  • In step 6, do not go below 30 minutes, as quotes can be delayed by 20 minutes or more due to network traffic.
  • In step 8, if you click on the currency name, a link to MSN Money will automatically open to give you additional information, such as annual comparisons.
  • There is a limit to this spreadsheet as you can only do calculations against the dollar, but given that most of the world's currencies are measured against it, this choice makes sense.

Warnings

  • MSN Money and many other websites only report wholesale exchange rates. The wholesale value (also known as the "interbank" rate) is only for large banks when they trade with each other; the minimum amounts are usually over US $ 5 million. Individuals cannot use this rate and get a per-minute rate instead. It is almost never as favorable as the wholesale one. To find out which retail exchange you will get, you will need to contact the institution you actually buy the currency from. For example, if you shop from Wells Fargo, call Wells Fargo and ask for their rate.
  • Sometimes the data and formulas just don't work. Be patient!

Recommended:

How to Create a Calendar with Microsoft Excel

How to Create a Calendar with Microsoft Excel

Although its fame is due to completely different uses, Microsoft Excel can also be used to create and manage a calendar. The ready-made and freely customizable calendar templates are numerous, a real advantage in terms of time for those who do not want to create and format their own calendar from scratch.

How to Create an Electronic Register with Microsoft Excel

How to Create an Electronic Register with Microsoft Excel

The electronic register on Microsoft Excel is a great tool to use. It is a very useful spreadsheet containing data and formulas which, compared to traditional ways, minimizes both the time and effort to save the grades and calculate them. This guide contains a fairly detailed description of the procedures that will allow you to learn this new tool.

How to Buy and Sell Currency: 11 Steps

How to Buy and Sell Currency: 11 Steps

Nowadays the financial markets have become easily accessible even to medium and small investors, who in a few clicks can buy or sell almost any currency in the world. Most of these currency investments are made through the Forex market (the world market for trading currencies).

How to Change Currency: 5 Steps (with Pictures)

How to Change Currency: 5 Steps (with Pictures)

Many travelers obtain the currency of the country of destination before departure, so that they already have the money for the taxi and for the expenses to be made immediately after arrival in their pockets. Exchange offices are almost always found in airports, ports and hotels, but these usually ask for higher commissions than banks (the percentage requested can reach up to 7% of the amount you intend to change).

3 Ways To How To Protect Your Currency From Risks

3 Ways To How To Protect Your Currency From Risks

The Hedge (or Cover) is like an insurance policy; if you carry out transactions abroad, or simply if you hold foreign currency for investment purposes, a currency fluctuation can quickly cause you large losses. The hedge is a method to protect yourself against these effects: