If you work with different currencies, it can be a huge pain to look up all the currency conversion rates for a specific day. Fortunately, if you plan on working within Google Sheets, you can use a built-in function that automatically retrieves and updates the current exchange rate. Let’s find out how you can easily convert currencies in Google Sheets.
How to Set Up a Currency Conversion Rate in Google Sheets
For the sake of this article, let’s use a database where we are paid in US dollars to write articles. However, we live in the UK, so whatever we’re paid will convert to Great British Pounds when it hits our bank account. So how much will we earn in GBP when we’re paid at the end of the month?
To answer this, we can invoke Google’s Finance feature. This is a really in-depth feature that’s mostly used to compare stock values of two companies. However, if we enter two currency codes in lieu of company codes, we get a currency conversion instead!
First, select an empty cell where you want the currency conversion rate to go. Then, type
=GOOGLEFINANCE("CURRENCY:EXAEXB") into it. Replace “EXA” with the currency code you want to convert from and “EXB” with the currency code you’re converting to.
In our spreadsheet, we want to convert US dollars to Great British pounds. The code for US dollars is “USD,” and the code for Great British pounds is “GBP.” As a result, our final function will look like this:
When we hit Enter, the cell fills with a long number:
This is the conversion rate. In our spreadsheet, this is how many pounds you’d get if you converted a dollar into GBP. It’s not too useful by itself, but we can do a little mathematics to work out how much we’ll be paid in pounds.
How to Use the Currency Conversion Rate
To work out how much we will be paid, we multiply the amount we’ll get in USD by the currency conversion rate. So, in cell C2, we type
=B2*D2. Remember, you can click cells instead of manually entering their coordinates. This shows us how much we’ll be paid for $100 worth 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 we want to compare the cell below the first payment with the cell below the currency conversion cell, which will give us an error!
To fix this, we need to edit the formula so it reads
=B2*$D$2. The dollar signs tell Google Sheets to never increment the letter or number of that cell, so it will always point to our conversion rate. Now we can drag the blue box down and see how much we will be paid.
How to Set a Historic Currency Conversion Rate
Let’s assume that the spreadsheet we’re using right now represents each month. If we kept the currency conversion function as is and revisit this month’s sheet three months down the line, the currency conversion rate updates to what it is on that day, thus giving incorrect logs. As such, if you’re doing a spreadsheet a month, it’s a good idea to “lock” the rate in at 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 rate was back on July 31, 2020, we can do so by using the following formula:
When you press Enter, the spreadsheet will suddenly update with a “Date” and a “Close” field. The date field shows the exact time for the rate, and the close field shows what the closing rate was at that time. You can then use this older rate to get an accurate representation of what it was like in the past.
If you enter this formula and your conversions suddenly break, you need not worry. This is because using the date feature shunts the conversion rate across a few cells. As such, you’ll need to update the cell formula to point to the rate’s new location.
Keeping Current Over Currency
If you work in multiple currencies, you’ll know the pain of converting them. Fortunately, you can easily convert currencies in Google Sheets with the above tricks. Just enter a few formulas, and Google Sheets automatically updates your spreadsheet with current rates. If you have difficulty typing various currency signs on your Mac, check out our guide on that.