Imagine a situation in which you are presented with an Excel sheet in which a number of monetary values are placed in a certain column. You might need to convert the currency for all those values in the column and display them in a separate column. This could be important for financial organizations and those in import-export businesses. While many free and paid tools are available, which could help in converting currencies across columns directly, we will discuss the way to convert currencies in Microsoft Excel using a basic formula in this article.
Convert currencies in Excel
You can convert currencies like Dollar, Euro, Pound, INR, etc., in Microsoft Excel, without using a currency converter. Use this formula to get the work done.
To convert one currency to another currency, you would need to use 3 columns on the Excel sheet. The first column for the target currency, the second for the exchange rate and third for the converted currency.
The syntax of the formula to be used for currency conversion is as follows:
= <Location of the first cell>*$<Column for exchange rate>$<Row for exchange rate>
Where <Location of the first cell> is the location of the first cell in the column of cells with the monetary values of the target currency. <Column for exchange rate> and <Row for exchange rate> are the column letter and row number for the cell in which the exchange rate is mentioned.
Basically, this formula is for simple multiplication of the target currency with the exchange rate.
How to find the exchange rate between two currencies
Finding the exchange rate between two currencies is easy. Open Bing.com and search “first currency to second currency.” It would automatically display the exchange rate per unit of the first currency. Eg. If I search for “Dollar to Euro” in the Bing search bar, the converted value for $1 would be the exchange rate from Dollar to Euro.
Eg. Let us assume the following case. Suppose you have a column of values in dollars with the values placed from cell A3 to A7. You need the corresponding values in Euro in column C, starting from cell C3 to C7.
First, find the exchange rate using the currency convertor in Bing. Write the exchange rate in cell B3.
Now, write the formula = A3*$B$3 in the cell C3 and press Enter.
Click anywhere outside cell C3 and then back on cell C3 to highlight the Fill button. Pull down cell C3 till cell C7, and it would display all the values in Euro sequentially.
One of the difficulties with this method would be adding the currency unit. However, it is better than buying a paid tool specifically for the purpose.
Hope it helps!