If you are working with different currencies, it can be very difficult to find all the currency conversion rates for a specific day. Fortunately, if you plan to work in Google Sheets, you can use a built-in function that automatically retrieves and updates the current exchange rate. Let’s find out how to easily convert currencies in Google Sheets.
How to set up a currency conversion rate in Google Sheets
For the purposes of this article, let’s use a database where we get paid in US dollars to write articles. However, we live in UK. Therefore, anything we receive will be converted to pounds sterling when it arrives in our bank account. So how much will we earn in GBP when we get paid at the end of the month?
To answer this, we can invoke the Finance feature of Google. This is a very in-depth feature that is mainly used to compare the stock values of two companies. However, if we enter two currency codes instead of company codes, we get a currency conversion instead!
First, select a blank cell where you want to apply the currency conversion rate. Then type
=GOOGLEFINANCE("CURRENCY:EXAEXB") in it. Replace “EXA” with the currency code you want to convert from and “EXB” with the currency code you are converting to.
In our spreadsheet, we want to convert US dollars to British pounds. The code for US dollars is “USD” and the code for British pounds sterling is “GBP”. As a result, our final function will look like this:
When we press Enter, the cell fills with a long number:
This is the conversion rate. In our spreadsheet, this is the number of pounds you would get if you converted a dollar to GBP. It’s not very helpful on its own, but we can do a little math to figure out how much we will be paid in pounds.
How to use the currency conversion rate
To determine how much we will be paid, we multiply the amount we will receive in USD by the currency conversion rate. So in cell C2 we type
=B2*D2. Remember that you can click on cells instead of manually entering their coordinates. This shows us how much we will be paid for $ 100 of work.
See the blue box at the bottom right of the cell we selected? We can drag this down to apply the same formula to the rest of the table. However, if we do this Google Sheets will think that we want to compare the cell under the first payment with the cell under the currency conversion cell, which will give us an error!
To solve this problem, we need to modify the formula so that it reads
=B2*$D$2. Dollar signs tell Google Sheets to never increment that cell’s letter or number, so it will always show our conversion rate. Now we can drag the blue box down and see how much we will be paid.
How to set a historical currency conversion rate
Suppose the worksheet we are currently using represents each month. If we kept the currency conversion function as is and revisit that month’s sheet three months later, the currency conversion rate is updated to what it is that day, which is incorrect logs. As such, if you are creating one worksheet per month, it is a good idea to “lock in” the rate to what it was at the end of the month for an accurate record.
To do this, we need to add a date. If we wanted to see what the return rate was as of July 31, 2020, we can do so using the following formula:
When you press Enter, the worksheet suddenly updates with a “Date” field and a “Close” field. The date field indicates the exact time of the rate and the closing field indicates what the closing rate was at that time. You can then use that older rate to get an accurate representation of what it looked like in the past.
If you enter this formula and your conversions abruptly stop, don’t worry. Indeed, the use of the date function derives the conversion rate on a few cells. As such, you will need to update the cell formula to indicate the new tariff location.
Stay up to date on the currency
If you are working in multiple currencies, you will know the pain of converting them. Fortunately, you can easily convert currencies in Google Sheets with the tips above. Just enter a few formulas and Google Sheets will automatically update your spreadsheet with the current rates. If you’re having trouble entering various currency signs on your Mac, check out our guide to that.
Is this article useful?