In the world we live in where almost every country has its own distinct currency and value, we almost always feel a need to convert one currency to another. These days it is easy as we can look for them over the internet and calculate how much a dollar is in terms of Pakistan Rupee. Instead of looking anywhere else we can make it in Excel and it doesn’t take much effort either. So lets toss the coins!
Understanding the concept
For a currency converter to work we don’t need the conversion rates of every currency for every other currency in the world. As the world economics is relative therefore every currency is linked to another and we can find out the conversion rate of two currencies even if we don’t know it directly.
For example if I know the conversion rate of 1 USD to PKR and 1 USD to AED then I can easily find out the conversion rate between 1PKR to AED. Let me explain how it works:
1 USD = 99.8 PKR [Equation 1]
And 1 PKR = 0.01002 USD (1/99.8 = 0.01002)
1 USD = 3.674 AED [Equation 2]
And 1 AED = 0.27218 USD (1/3.674 = 0.27218)
As left hand side of equation 1 and 2 is same therefore we can safely say that right hand sides are also equal to each other. Therefore:
99.8 PKR = 3.674 AED
Now if I want to know how much 1 AED is in terms of PKR I simply have to move 3.674 to the left hand side of the equation:
99.8 PKR x 1/3.674 = 1 AED
As we know 1/3.674 = 0.27218 so putting this value in the equation we get:
99.8 PKR x 0.27218 = 1AED
1 AED = 27.163 PKR
So we understood that even if we have exchange rates of just one currency for all the others we can still find the exchange rate between any two currencies of the world. And this is exactly the concept I will be using in our currency converter in Excel. So lets open the cash box!
Currency Converter Step-by-step
We need updated currency exchange rates that we can use. There are number of ways to get it done but the one that worked for me best is by using Excel’s built-in features. If this fails then the rest of the process is futile. So to have a reliable workbook I will be going with Excel’s existing connections.
Step 1: Open Excel and go to Data tab and click Existing connections button.
Step 2: From the popup select MSN MoneyCentral Investor currency rates and click open button
Step 3: Excel will ask if you want to pull the information in the same worksheet or a new one. Let it be existing worksheet with the cell address being the default too.
Step 4: Before you click OK button click properties.
Step 5: Activate the option Refresh every _____ minutes and type in the number of minutes after which Excel will refresh the sheet automatically to get the latest rates. Lets make it 5 minutes.
Step 6: Tick the option just under it that says Refresh data when opening file. This way file will have latest data every time you open it at the start of the day. Click OK button to close the box. It will take some time to fetch the data depending upon internet speed.
Step 7: Once the data is imported rename the worksheet to Exchange rates.
Following animation walks you through all the 7 steps explained:
Step 8: Select cell A5 to A46 and type Curname in the name box to give name to this range. It will make writing formulas much easier later.
Step 9: Go to cell J5 in the same worksheet and put the following formula and drag the fill handle down to cell J46:
This will fetch the currency names leaving the unnecessary string of ” – United states dollar”.
Step 10: Select J5 to J46 and type Curlist in the name box to give name to this range.
Step 11: Go to cell K5 and type =B5. Hit enter and double click the fill handle. It will fetch the values from column B.
Step 12: Go to cell L5 and type =C5. Hit enter and double click the fill handle. It will fetch another set of values.
Step 13: Select all the cells from J5 to L46 and name it Curtable by typing it in the name box and hitting Enter key on the keyboard.
Step 14: Select columns J K and L. Right click on column J and hide all of them to keep the worksheet clean.
Creating currency converter
Now that we have all the basics done, we can proceed with the converter itself to start number crunching
Step 1: Open another worksheet and name it Currency Converter. Type Convert in cell A2 and To in cell A3.
Step 2: Go to cell B2 and click Data tab > Data validation button in data tools group.
Step 3: Under Allow select list and in source type in =Curlist and click OK. This will put all the names of currencies in the form of drop down menu for easy selection.
Step 4: Go to cell B3 and repeat Step 3 to get the list.
Step 5: Go to Currency Converter worksheet and in cell C3 put this formula:
This is the formula based on the concept we discussed in the beginning. Now let make final few touches and its all done.
Step 6: Go to cell D3 and put this formula:
This will help you calculate the total amount for any given amount in another currency you want to convert.
So lets summarize what we learnt:
- We learnt how finance and currency works even if we have one base currency to calculate other rates.
- We successfully imported conversion rates using built-in feature that was hidden under the less known group.
- We learn how to separate part of text out of the whole string in the cell using LEFT and FIND functions.
- We used named ranges, learnt how to make them and to use them in formula to make the whole process easier.
- And lastly we used VLOOKUP function to get the converter running and munching numbers!
Thank you for the information. I have been able to use this, but the problem I am having is that I have set up formulas to convert from Euro to USD to track daily spend. I can get the converter to work, but every time it updates, it changes the conversion for prior days/weeks to today’s rate. Is there a way to not update prior entries but just have today’s entries use today’s conversion rates, tomorrow’s data use tomorrow’s rates, etc? What I would like is a table of conversion rates for only Euro to USD that adds the newest rate in. Then I can use a VLookup function for the formula to use that day’s rate.
After following the first 7 steps, I did not get the Currency rates. Instead I got something that looks like only the text of the webpage.I tried signing in to msn and it still doesnt work. What must be wrong. Could you please help.
Not sure if I have understood the problem correctly. But by the looks they are like simple texts. Have you tried pressing F9 key to see if the rates update or not?
I have same problem. what I get after 7 steps is not similar to what is shown in tutorial. It is like as follows:
Skip To Navigation
Skip To Content
Skip To Footer
Change language & content:
Switch to Latino (Español)
Choose a language
United States (English) – en
United States (español) – es
and list goes on…………….
Excellent work done by you. I can see, you have provided all basic and detailed steps used to convert currency in excel. Basically, I never try this as I was directly use online converters. Thanks For sharing. I found very helpful information in your blog.