Yes, it’s easy with Excel’s Euro Currency tool.
tech journalist, PCWorld |
Today’s Best Tech Deals
Picked by PCWorld’s Editors
Top Deals On Great Products
Picked by Techconnect’s Editors
Excel can convert currency given the right tools. While there are dozens of currency conversion websites or other conversion tools that you can use online or purchase, they work best for looking up one currency transaction at a time. If, on the other hand, you need a table of multiple transactions or currencies for multiple countries, you can use Excel to accomplish this task with the Excel Add-In called EUROTOOL.XLAM.
Note that to keep up with currency fluctuations, your usage with Excel should include an Internet connection, so you can receive updates. We’ve provided a worksheet for you to practice using the currency conversion techniques in this story:
Install / Load the Euro Currency tool
If you try to use this function and receive an error, you must first install and load this tool.
1. Select File > Options > Add-Ins
2. Scroll down to the Add-In called EUROTOOL.XLAM
3. Click to select it, then click OK.
JD Sartain / IDG Worldwide
01 An online currency converter can take you only so far, which is why you might want to install Excel’s Euro Currency tool.
4. The syntax for this command is: =EUROCONVERT(number,source,target,full_precision,triangulation_precision).
5. To locate, select Formulas (tab) > Insert Function (button in the Function Library group). In the Insert Function dialog window, click the down arrow beside the Or select a category field box, then scroll down and select EUROCONVERT.
6. Click OK and the Function Arguments dialog opens.
7. All of the arguments shown here (number,source,target,full precision,triangulation precision) in this command are required and defined as follows:
a. Number: This is the currency value you want to convert (can also be a cell address for the location of this value).
b. Source: This is the three-letter ISO code for the source currency; e.g., Spain equals ESP. This can also be a cell address for the location of this code.
c. Target: This is the three-letter ISO code for the target currency; e.g., France equals FRF. This can also be a cell address for the location of this code.
d. Full Precision: Either TRUE or FALSE. True means all significant digits (from the calculation) are displayed. False means the result is shown with the currency-specific rounding rules applied; that is, the rules that determine how the results are rounded—up or down to the nearest cent; to the nearest unit or sub-unit; or to a multiple or fraction of the unit or sub-unit. Note that for a result that is exactly halfway, the sum is rounded up. False is the default if the Full Precision argument is omitted. In other words, if you don’t enter anything (true or false), Excel assumes the answer is FALSE.
e. Triangular Precision: Excel store 15 significant digits of precision. In some situations, this can be a problem. For this argument, you can set the number of digits equal to or greater than 3. Confused? See examples in screenshot images.
8. Enter the appropriate arguments, then click OK.
JD Sartain / IDG Worldwide
02 Select EUROCONVERT and enter appropriate arguments.
NOTE: The EUROCONVERT function in Excel does not convert United States dollars, just the following countries to and from one another: Euros, Austria, Belgium, Finland, France, Germany, Greece, Ireland, Italy, Luxembourg, Netherlands, Portugal, and Spain.
I have no idea why Microsoft would create a function that does not include the United States! However, there is a workaround.
1. Copy or download the EUROCONVERT table from the Internet and paste it into your spreadsheet, or download this spreadsheet and use ours.
2. Because the EUROCONVERT function won’t convert dollars, the work-around is to select a cell at the top of the spreadsheet (e.g., C2) and enter the current conversion rate for one Euro that equals one dollar (that is, $1.14).
3. Next, use the EUROCONVERT function to convert 90 Euros to dollars ($102.60). To do that, enter this formula in cell D20: =SUM(C20*$C$2). C2 is the cell that contains the dollar amount for one Euro (that is, $1.14). See spreadsheet or figure 03.
4. In D21, enter this formula: =EUROCONVERT(C21,$D$17,D8,FALSE,10) where C21 is the location of the number of Euros you want converted; $D$17 is the location of the Euro code; D8 is the location of the code for French Francs; FALSE is the Full Precision code for specific rounding rules applied; and 10 is Triangular Precision (a safe number for the significant digits of precision).
5. In summary, 90 Euros equals $102.60 dollars, and 90 Euros equals 590.36 Francs; so $102.60 equals 590.36 Francs.
JD Sartain / IDG Worldwide
03 EUROCONVERT table plus examples with formulas
JD Sartain is a technology journalist from Boston. She writes the Max Productivity column for PCWorld, a monthly column for CIO, and regular feature articles for Network World.
Microsoft Excel doesn’t include built-in tools to convert currency. However, you can use an external data source to provide up-to-date rates. A basic multiplication formula will then convert from one currency to another. Here’s how you do it!
Adding an External Data Source to Excel
An external data source is the best way to get up-to-date currency exchange rates. You can use this data to convert from one currency to another in Excel. The process is similar to converting currency in Google Sheets.
First, you need a suitable online data source (in the XML format) that you can import into your spreadsheet. FloatRates has various XML feeds based around different currencies that you can use.
After you find the one you want to use, open your Excel spreadsheet. In Excel 2019 or Office 365, click Data > Get Data > From File > From XML. In older versions of Excel, click Data > Get External Data > From Other Sources > From XML Data Import instead.
For our example, we’re using the FloatRates USD data feed, so we import that into Excel.
In the “Import Data” window, paste the URL to your XML data feed in the “File Name” box, and then click “Import.”
If you have Office 2019 or 365, you see a preview of how the data will be imported. If you’re using FloatRates data, you have to convert it in the Excel Power Query Editor to use it.
To do so, click “Transform Data.”
The Excel Power Query Editor appears. Scroll to the “Item” column, and then double-click “Table” to load the up-to-date currency rates.
The Power Query Editor preview updates and shows the FloatRates currency data. Click “Close and Load” in the top-left corner to add the data to your spreadsheet.
The data you import appears in a new worksheet, to which you can now refer when you need to convert currency.
Most external data sources update hourly, but FloatRates only updates every 12 hours. If you want to update your data manually, click Data > Refresh All.
Converting Currency in Microsoft Excel
You can use the up-to-date data you imported to convert currency figures with a simple multiplication formula.
Click the worksheet with your imported currency rates. If you’re using FloatRates data, look at the exchange rates under the “exchangeRate” column. Note the cell that contains the rate of the currency to which you want to convert.
Using our FloatRates U.S. dollar data, we see that to convert from U.S. dollars to British pounds, we need to use the GBP exchange rate in cell I3.
Return to your existing worksheet, and type the USD price from which you want to convert into a cell. In a second cell, use the formula =A2*Sheet2!$I$3 , and replace “A2” with the cell that contains your USD price.
Replace the second part of the formula with an absolute reference to the cell in the “exchangeRate” column on your imported data worksheet that contains the exchange rate to which you want to convert.
In the example above, Column A lists U.S. dollars. Column B lists the converted currency rates from U.S. dollars to British pounds (1 USD to GBP is in cell B2).
When you change the absolute cell reference and use alternative data sources (like the FloatRates GBP data source to convert from GBP to other currencies), you can convert from any currency to another.
You can also use a manual rate instead of an external data source to convert currency rates. Just set the exchange rate manually in a cell (in our example, cell B2), and the price in another (cell A3).
The same multiplication formula converts your currency. However, if you’re not using an external data source, you’ll have to update the rate manually to see the correct price.
Microsoft Excel doesn’t include built-in tools to convert currency. However, you can use an external data source to provide up-to-date rates. A basic multiplication formula will then convert from one currency to another. Here’s how you do it!
Adding an External Data Source to Excel
An external data source is the best way to get up-to-date currency exchange rates. You can use this data to convert from one currency to another in Excel. The process is similar to converting currency in Google Sheets.
First, you need a suitable online data source (in the XML format) that you can import into your spreadsheet. FloatRates has various XML feeds based around different currencies that you can use.
After you find the one you want to use, open your Excel spreadsheet. In Excel 2019 or Office 365, click Data > Get Data > From File > From XML. In older versions of Excel, click Data > Get External Data > From Other Sources > From XML Data Import instead.
For our example, we’re using the FloatRates USD data feed, so we import that into Excel.
In the “Import Data” window, paste the URL to your XML data feed in the “File Name” box, and then click “Import.”
If you have Office 2019 or 365, you see a preview of how the data will be imported. If you’re using FloatRates data, you have to convert it in the Excel Power Query Editor to use it.
To do so, click “Transform Data.”
The Excel Power Query Editor appears. Scroll to the “Item” column, and then double-click “Table” to load the up-to-date currency rates.
The Power Query Editor preview updates and shows the FloatRates currency data. Click “Close and Load” in the top-left corner to add the data to your spreadsheet.
The data you import appears in a new worksheet, to which you can now refer when you need to convert currency.
Most external data sources update hourly, but FloatRates only updates every 12 hours. If you want to update your data manually, click Data > Refresh All.
Converting Currency in Microsoft Excel
You can use the up-to-date data you imported to convert currency figures with a simple multiplication formula.
Click the worksheet with your imported currency rates. If you’re using FloatRates data, look at the exchange rates under the “exchangeRate” column. Note the cell that contains the rate of the currency to which you want to convert.
Using our FloatRates U.S. dollar data, we see that to convert from U.S. dollars to British pounds, we need to use the GBP exchange rate in cell I3.
Return to your existing worksheet, and type the USD price from which you want to convert into a cell. In a second cell, use the formula =A2*Sheet2!$I$3 , and replace “A2” with the cell that contains your USD price.
Replace the second part of the formula with an absolute reference to the cell in the “exchangeRate” column on your imported data worksheet that contains the exchange rate to which you want to convert.
In the example above, Column A lists U.S. dollars. Column B lists the converted currency rates from U.S. dollars to British pounds (1 USD to GBP is in cell B2).
When you change the absolute cell reference and use alternative data sources (like the FloatRates GBP data source to convert from GBP to other currencies), you can convert from any currency to another.
You can also use a manual rate instead of an external data source to convert currency rates. Just set the exchange rate manually in a cell (in our example, cell B2), and the price in another (cell A3).
The same multiplication formula converts your currency. However, if you’re not using an external data source, you’ll have to update the rate manually to see the correct price.
To convert from a given currency to other specific currencies, you can use the VLOOKUP function. In the example shown, the formula in E5 is:
which converts the amount in column B from US Dollars (USD) to the currency indicated in column D, where the named range “xtable” refers to G5:H10.
The formula in this example converts amounts in USD to other currencies using currency codes. Available currencies and exact conversion rates can be adjusted by editing the values in the table on the right.
The core of this formula is the VLOOKUP function, configured like this:
Here, the lookup value comes from column D, table array is the named range “xtable”, column index is 2, and match type is set to exact match.
In this configuration, VLOOKUP finds the currency in the table, and retrieves the conversion rate from column H. If the currency code
Nested IF equivalent
With a formula based on nested IFs, the solution looks like this:
Note: VLOOKUP is generally a better solution, since values are easy to see and edit at any time, the formula is easier to read, and exchange rate values are not stored redundantly in many different formulas.
INDEX and MATCH equivalent
With the INDEX and MATCH funcions, the solution looks like this:
With the Currency data type you can easily get and compare exchange rates from around the world. In this article, you’ll learn how to enter currency pairs, convert them into a data type, and extract more information for robust data.
Note: Currency pairs are only available to Microsoft 365 accounts (Worldwide Multi-Tenant clients.)
Use the Currency data type to calculate exchange rates
Enter the currency pair in a cell using this format: From Currency / To Currency with the ISO currency codes.
For example, enter “USD/EUR” to get the exchange rate from one United States Dollar to Euros.
Select the cells and then select Insert > Table. Although creating a table isn’t required, it’ll make inserting data from the data type much easier later.
With the cells still selected, go to the Data tab and select the Currency data type.
If Excel finds a match between the currency pair and our data provider, your text will convert to a data type and you’ll see the Currency icon in the cell.
Note: If you see in the cell instead of the Currency icon, then Excel is having difficulty matching the text with data. Correct any mistakes and press Enter to try again. Or, select the icon to open the Data Selector where you can search for a currency pair or specify the data you need.
To extract more information from the Currency data type, select one or more converted cells and select the Insert Data button that appears or press Ctrl/Cmd+Shift+F5.
You’ll see a list of all the fields available to choose from. Select the fields to add a new column of data.
For example, Price represents the exchange rate for the currency pair, Last Trade Time represents the time the exchange rate was quoted.
Once you have all the currency data that you want, you can use it in formulas or calculations. To ensure your data is up to date, you can go to Data > Refresh All to get an updated quote.
Caution: Currency information is provided “as-is” and may be delayed. Therefore, this data should not be used for trading purposes or advice. See About our data sources for more information.
Many Excel reports on revenue may reflect data in various currencies. And then, if you live in one country and want to convert these currencies under one currency, that’s when the problems begin. The Professor Excel Currency Converter add-in for Excel can help you make exchange rate calculations with ease.
Often, the exchange rate you’re using may be outdated, and it can be hard to figure which exchange rate to use when converting your financial data. In all these, you run the risk of putting in the wrong figures and coming up short or worse, inaccurate, which can cost your business more in the long run. This is why Currency Converter can be handy to keep an eye on your exchange rate using Excel spreadsheets.
Convert Data Quickly with Professor Excel Currency Converter
While there are many sources for getting the latest exchange rates, the trouble is, you still have to go to a separate website or go to a separate app to figure out the exchange rate. With your workbooks, this can be easy if you have a fixed exchange rate that you will use across all your worksheets. However, it can be difficult if you need to base your financial calculations on daily exchange rates. This, though more accurate and more appropriate, can make working with currencies so much more difficult.
If you’re working with daily rates, you have to go over each of your cells with currencies and input the appropriate formula with the right exchange rate. Sure, this can be done, but it will be time-consuming. And if you have to do everything all over again the next day when the exchange rates are different, then it just isn’t worth all the painstaking time and effort that you put into it.
And that’s why Professor Excel Currency Converter can do wonders with your financial reports and all other worksheets requiring currencies and exchange rates.
With Professor Excel Currency Converter, you significantly cut your time working on your currencies and financial data.
Get the Professor Excel Currency Converter
While there are many apps that allow you to convert currency easily with just a few clicks of a button, integrating this in Excel can be an altogether different matter. You need something that is seamlessly compatible with Excel so you can use the formula and formatting easily for all kinds of computing functions.
So, the Professor Excel Currency Converter add-in is your best bet when dealing with currencies in Excel. Aside from being a free add-in, it works well with all versions of Excel. This way, you don’t have to learn a new software or download a separate app and toggle between this and Excel. You work right within Excel with the Currency Converter just like it’s always been there from the get-go. You get fewer distractions and you work smoothly.
Microsoft Excel does not include built-in tools for converting currencies. However, you can use an external data source to provide updated rates. A basic multiplication formula will then be converted from one currency to another. Here’s how you do it!
Adding an external data source to Excel
An external data source is the best way to obtain up-to-date exchange rates. You can use this data to convert from one currency to another in Excel. The process is similar to currency conversion in Google Sheets.
First, you need a suitable online data source (in XML format) that you can import into your spreadsheet. FloatRates offers different XML feeds based on different currencies that you can use.
After finding the one you want to use, open your Excel spreadsheet. In Excel 2019 or Office 365, click Data> Get Data> From File> From XML. In older versions of Excel, click Data> Get external data> From other sources> From importing XML data instead.
For our example, we use the FloatRates USD data feed, so we import this into Excel.
In the “Import data” window, paste the URL into your XML data flow in the “File name” area, then click “Import”.
If you have Office 2019 or 365, you see an overview of how the data will be imported. If you are using FloatRates data, you must convert it in the Excel query editor to use it.
To do this, click on “Transform data”.
The Excel Power query editor appears. Scroll down to the “Item” column, then double-click “Table” to load the updated exchange rates.
The Power Query Editor preview updates and displays the FloatRates currency data. Click “Close and Load” in the upper left corner to add the data to your spreadsheet.
The data you import appears in a new spreadsheet, which you can now refer to when you need to convert currencies.
Most external data sources are updated hourly, but FloatRates only updates every 12 hours. If you want to update your data manually, click Data> Refresh All.
Currency conversion in Microsoft Excel
You can use the up-to-date data you imported to convert currency figures with a simple multiplication formula.
Click on the spreadsheet with your imported exchange rates. If you are using FloatRates data, see the exchange rates in the “exchangeRate” column. Note the cell that contains the rate of the currency into which you want to convert.
Using our US dollar FloatRates data, we see that to convert US dollars to British pounds we have to use the GBP exchange rate in cell I3.
Go back to your existing spreadsheet and type the price in USD from which you want to convert to cell. In a second cell, use the formula = A2 * Sheet2! $ I $ 3 and replace “A2” with the cell that contains your price in USD.
Replace the second part of the formula with a absolute reference to the cell in the “exchangeRate” column of your imported data worksheet that contains the exchange rate at which you want to convert.
In the example above, column A lists the US dollars. Column B lists the converted exchange rates from the US dollar to British pounds (1 USD to GBP is in cell B2).
When you change the absolute cell reference and use other data sources (such as the FloatRates GBP data source to convert GBP to other currencies), you can convert any currency to another.
You can also use a manual rate instead of an external data source to convert exchange rates. Simply set the exchange rate manually in one cell (in our example, cell B2) and the price in another (cell A3).
The same multiplication formula converts your currency. However, if you are not using an external data source, you will need to update the tariff manually to see the correct price.
Microsoft Excel is a great spreadsheet software program for computing and sorting out data. But has a limitation.
- 123 to “one hundred and twenty-three.”
- $123 to “one hundred and twenty-three dollars.”
- $123.50 to “one hundred and twenty-three dollars and fifty cents.”
- Php 123.00 to “one hundred and twenty-three pesos.”
- Php 123.50 to “one hundred and twenty-three pesos and fifty centavos.”
4. Copy the following code and paste on that module window
Take note: the currency is a dollar, therefore, depends on your location, you need to change the text above. The function yields to these results:
Notice that if there is no decimal point, it will add “No Cents.” In real-world, there is no need to put such words. Ideally, the “only” word shall put at the last of those sentences.
You can still edit it in the code above.
Tip: No need to manually type over again that function. All you need is to point and drag down until it displays the result. See below in action.
You will notice that it is not capitalized. If you wish to convert it to ALL UPPER CASE then right thing, Excel has built-in ALL CAPS. Thanks to function UPPER().
5. Then you close that window. In your Excel window, try to type =SpellNumber you will notice if successfully embedded, display the function.
By the way, the function is called SpellNumber(Cell) (try to see above code)
6. There you have it! After accomplishing the desired output, you need to SAVE it to a macro-enabled format. At first, it will prompt something like this:
As per instruction, you need to save it in a macro-enabled to include the function code. Therefore, click NO, and start choosing a .xlsm file format.
Final Thoughts
If you have comments and suggestions, you may cast it below. Thank you!
With the Currency data type you can easily get and compare exchange rates from around the world. In this article, you’ll learn how to enter currency pairs, convert them into a data type, and extract more information for robust data.
Note: Currency pairs are only available to Microsoft 365 accounts (Worldwide Multi-Tenant clients.)
Use the Currency data type to calculate exchange rates
Enter the currency pair in a cell using this format: From Currency / To Currency with the ISO currency codes.
For example, enter “USD/EUR” to get the exchange rate from one United States Dollar to Euros.
Select the cells and then select Insert > Table. Although creating a table isn’t required, it’ll make inserting data from the data type much easier later.
With the cells still selected, go to the Data tab and select the Currency data type.
If Excel finds a match between the currency pair and our data provider, your text will convert to a data type and you’ll see the Currency icon in the cell.
Note: If you see in the cell instead of the Currency icon, then Excel is having difficulty matching the text with data. Correct any mistakes and press Enter to try again. Or, select the icon to open the Data Selector where you can search for a currency pair or specify the data you need.
To extract more information from the Currency data type, select one or more converted cells and select the Insert Data button that appears or press Ctrl/Cmd+Shift+F5.
You’ll see a list of all the fields available to choose from. Select the fields to add a new column of data.
For example, Price represents the exchange rate for the currency pair, Last Trade Time represents the time the exchange rate was quoted.
Once you have all the currency data that you want, you can use it in formulas or calculations. To ensure your data is up to date, you can go to Data > Refresh All to get an updated quote.
Caution: Currency information is provided “as-is” and may be delayed. Therefore, this data should not be used for trading purposes or advice. See About our data sources for more information.
To show numbers as currency, you can apply either the Currency format or the Accounting format.
(You’ll find a comparison of the two formats in the Currency or Accounting section, below.)
Select the cells that you want to format and then, in the Number group on the Home tab, click the down arrow in the Number Format box.
Choose either Currency or Accounting.
Excel for the web applies the default currency symbol and appropriate decimal places in the cells that you selected.
Currency or Accounting?
While both the Currency format and the Accounting format can be used to correctly display monetary values, there are some differences between them, as shown in the following table.
With this format:
The decimal points appear aligned in the column.
The currency symbol appears next to the first digit in the cell. (That is, it is not aligned with the other currency symbols in the column.
With this format:
Both the currency symbols and the decimal points appear aligned in the column.
Cells that contain only zeros are identified with a hyphen.
Negative numbers appear in parentheses.
Change the default currency symbol
You can’t change the default dollar currency symbol in Excel for the web just yet; but if you have the Excel desktop application, you can click Open in Excel and follow the instructions here to specify the default currency symbol that you want. After you apply the currency symbol, save your worksheet and then reopen it in Excel for the web.
For news about the latest Excel for the web updates, visit the Microsoft Excel blog.
