Suppose you have financial statistics in prepared in USD, and need it converted to GBP.
Why not use Excel to solve your problem?
You can use currency pairs and currency exchange rates to convert currency.
Excel doesn’t come packed with in-built currency conversion features.
But you can easily convert your data to different currencies using exchange rates.
But you can easily convert your data to different currencies using either of the following methods discussed below.
Over the thousands of Excel courses we have delivered, this topic gets brought up non stop!
Why Convert Currencies In Excel?
Excel is a powerful tool when it comes to calculations and processing of big datasets.
Data that involves international currencies can get challenging in terms of conversion.
Although Excel doesn’t offer an in-built currency convertor, you can still use it to convert currency with ease. Often businesses perform their bookkeeping exercises using Excel.
They store data, including their sales and purchase records, in Excel.
When the same data is to be made accessible to multiple regions, it may need to be converted into the functional currency of that region.
Simply build a conversion tool of your own through Power Query or use Kutools.
Converting Currency With Formulas
Converting currency in Excel requires a smart blend of external data sources (the up-to-date exchange rates) and a Pivot Table.
The following is a data set that constitutes different products and their prices in USD.
The same needs to be converted into Euros. Here’s how to do it.
Step 1:
To convert currency using exchange rates found online, we need to import currency data into Excel from an external web page.
Data > Get & Transform data > From Web
This opens up the dialogue box as follows.
Paste the URL of the address from where you want the data imported and click ‘Okay’ to have the data loaded to Excel.
Once you have loaded the currency data, you may want to filter out a specific currency exchange rate only.
The same can be done using Advanced filters in Excel.
Step 2:
If the data is ready to be loaded into Excel with no adjustments required, click ‘Load data.’
Otherwise, choose ‘Transform Data’ as follows.
This launches the Power Query editor, where you can further edit the currency data to your choice, focusing on the currency pair you need.
We’ll take you through how to use it step by step here, but you can learn how to use it to take control of your spreadsheets on this course.
For instance, the data we’ve loaded consists of the rates as shown below.
However, we need the exchange rate in a separate column to apply the multiplication function for conversion.
So, we can split the column in Power Query editor before loading it into Excel.
To do so, select the Exchange rate column and go as below.
Transform > Split Column > By Delimiter
This takes you to the Split Column by Delimiter window, where you may select the delimiter.
As shown above, we have selected ‘Equals Sign’ as the Delimiter and ‘Right Most Delimiter’ for delimiting.
This is because we only want the exchange rate appearing on the rightmost to be separated.
This splits the columns into two, as shown below.
However, we only need the exchange rates and not the ‘U.S. Dollar’ written next to it.
Once again, split the column using the Delimiter ‘Space’ and the ‘Right Most Delimiter’ on the recently split column.
This separates the exchange rate into a separate column, as shown below.
Once your data is ready to be imported to Excel, click ‘Close and Load’ as appears on the top left of the Power Query Editor window.
Step 3:
Here you have your exchange rates from an external source ready for conversion using the data tab in Excel.
Let’s go back to the original dataset having the Euro Prices listed down.
To have them converted in Euros, under the Euro column, feed the formula as follows.
=B2*Sheet2!J2
- B2 represents the USD value that needs to be converted into Euros
- Sheet2!J2 represents an absolute cell reference from the Exchange Rate sheet loaded into Excel.
Drag and Drop to apply the same to all the products in the list.
Viola! There you have your currencies converted.
Step 4:
To make the conversion more prominent, pair the figures with their respective currency pair symbol.
For instance, to add the Euro symbol before to the currency listed under Column C, do as follows.
Select the figures > Number > Format Drop Down Menu > More Number formats
Select currency, select the Euro symbol from the dialogue box that opens up and restrict the decimal places to as many desired.
Symbols are added as follows.
Pro Tip: What if the exchange rates change in the future? Do we have to perform the entire exercise again? Obviously not.
Simply go to the sheet where the exchange rates are collated.
Go to Queries & Connections > Refresh All > Refresh All.
Excel would refresh the currency exchange rates imported from the web, and the formula applied to convert currency would also be updated automatically.
Want to know more about data modeling in Excel? Click here.
Kutools for Excel – Currency Tools
Kutools for Excel is not an in-built feature but an add-in that users would have to install for themselves.
This handy add-in comes with over 300 advanced features to enhance your Excel experience multiple folds.
These features include a variety of one-click operations such as merging of cells without loss of data.
In addition, kutools save the mass time of Excel users by saving time through smart functions.
Not only that, but it also enhances your work efficiency without you having to take specific Kutools training courses.
One of the most powerful features of Kutools is the conversion feature.
It makes it easy to convert currencies by allowing you to import currency exchange rates directly into your Excel worksheet.
As Kutools have the latest exchange rates fed in, you do not need to search for it from an online source or import it for conversion.
This enables the ‘Update exchange rate’ option and makes the conversion feature work efficiently.
Converting currency using Kutools
As Kutools have the latest exchange rates fed in, you do not need to search for it from an online source or import it.
To learn how to convert currency in Excel using Kutools, follow the example below.
Continuing the same example as above, we have the price list of different products where the prices are listed in USD.
Again, the same is to be converted into Euros.
Step 1:
Copy and paste the column containing the USD price to another adjacent column.
Change the column header to the target currency post-conversion.
We have copied and pasted the column’ Price (USD)’ and have changed the column name to ‘Price (Euros).’
This is to be done as Kutools would convert USD into Euros in the original column, and therefore, the USD values will be replaced with Euro values.
The original USD data will thus be lost.
If you want to simultaneously preserve both the USD and Euro columns in your worksheet, follow this step.
Step 2:
Select the column for the currency post-conversion i.e., Column C in our example, and launch the Kutools currency conversion tools.
Kutools > Content > Currency Conversion
Note: Kutool for Excel is an add-in that needs to be installed and enabled in Excel.
Step 3:
This opens up the following window.
The preceding window exhibits two currency boxes.
The first one represents the currency of the original data (i.e., USD in our example).
The second currencies’ box represents the conversion currency (i.e., Euros in our example).
After selecting both the currencies from the respective boxes, click the update rate button as shown above.
This fetches the latest USD to Euro exchange rate.
Step 4:
Next, click the ‘Fill Options’ button as it appears on the bottom left.
This opens up the following dialogues box, where you may select either of the three options.
Only Cells
Choose the option ‘Only Cells’ and press close.
Click ‘okay’ from the conversion window as follows.
This option replaces the original prices with the converted currency.
As evident from the screenshot above, Excel has replaced the USD Price values with Euro Price values.
Only Comments
Choose the option ‘Only Comments,’ check the options on the right, and press close as follows.
Click ‘okay’ from the conversion window to yield results.
This option doesn’t replace the original prices with the converted currency but only adds a comment that describes the conversion.
Excel hasn’t replaced the USD Price values with Euro Price values, but only comment is added to each converted value.
Cells and Comment
Choose the option ‘Cells and Comment’ and press close.
Click ‘okay’ from the currency conversion window as follows.
This option replaces the original prices with the converted currency and adds a comment that describes the conversion.
As evident from the screenshot above, Excel has replaced the USD Price values with Euro Price values and has added a comment to each converted value.
There you are – converting currencies using Kutools is only that simple and quick.
Kutools for Microsoft Excel enhances your work efficiency by offering an easy and efficient way of readily converting currency.
These rates are imported from reliable internet sources.
Using Kutools, you can instantly convert currencies to a different currency pair in your Excel worksheet.
For more Excel tips, read our guide here on Recovering Unsaved Files.
Troubleshooting
The currency that makes the foundation of a dataset can cause it to be erroneous if not rightly converted.
However, both the above-discussed methods would most likely not land you into problems when used for conversion in Microsoft Excel.
However, the following conversion errors are often confronted by users. Here’s how you can deal with them.
1. Inappropriate format of the imported data
If you are going with option one, i.e., to import an exchange rate from an online source for conversion, you need to pay special attention to quite a few things.
Often, an imported currency rate comes in an inappropriate format i.e., anything other than numbers.
When the same is used for multiplication and conversion, Excel fails to apply the operation and returns a #VALUE! Error.
You can avoid this problem by ensuring that an exchange rate isformatted as numbers.
We find data formatting is often the cause for most errors on our Excel courses!
2. Unnecessary characters in the imported data
Other times, data imported into Microsoft Excel comes together with a lot of fluff, be it empty spaces, unique characters, or parenthesis.
When you use the same to perform the conversion operation, Excel fails to do so and returns a #VALUE! Error or #REF! Error.
To cleanse the imported data of unwanted characters, you may use the ‘Find and Replace’ function.
For example, if the imported data consists of unnecessary parenthesis, you may select the column where the data is populated and perform the ‘Find and Replace’ function as follows.
Excel would thus replace the parentheses with nothing i.e., these would be removed.
Conclusion
Though Microsoft Excel doesn’t offer a conversion tool, you can still convert currencies in Excel using different methods.
Try applying these conversion techniques to your data to see how these methods make conversion in Excel a seamless experience.
