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.
Also read:The Essential VLOOKUP Guide for Excel and Google Sheets
How to Set Up a Currency Conversion Rate in Google Sheets
For the sake of this guide, 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 and type =GOOGLEFINANCE("CURRENCY:EXAEXB"). 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:
=GOOGLEFINANCE("CURRENCY:USDGBP")
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.
Also read:How to Type Euro, Cent, Yen and Other Currency Symbols on Your Mac
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 that 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.
There are two ways you can do this: either add the dollar signs manually or highlight the cell in your formula and hit the F4 key. Now simply drag the blue box down to see how much we will be paid.
Also read:How to Link Data Between Spreadsheets in Google Sheets
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 every 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 September 30, 2021, we can do so by using the following formula:
=GOOGLEFINANCE("CURRENCY:USDGBP", "price", DATE(2021,10,1))
When you press Enter, the spreadsheet will 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, as 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.
Also read:How to Automatically Change Cell Color in Google Sheets
Frequently Asked Questions
1. Can Microsoft Excel convert currencies?
To put it simply, the answer is “sort of.” Microsoft Excel doesn’t have a feature quite as handy as Google Finance that automatically pulls currency data from an already specified source and constantly keeps it updated.
Instead, you would need to use Excel’s “Import Data” feature and manually add currency information in the form of a table – this data can be obtained from currency exchange websites. Once you manage that, you’ll be able to use the rates from the table to accomplish the same thing we did in this guide.
In a crunch, you can always rely on unit converter apps for instant currency conversion.
2. How do I return historic currency conversion rates over a period of time?
In this article, we showed you how to “lock” in currency rates at specific intervals in case you want to make a historic record of your past conversions. You can make a small tweak to the same formula to return a range of currency conversion rates over a specific span of time. Here’s the syntax you’ll want to utilize:
=GOOGLEFINANCE("CURRENCY:<source_currency_symbol><target_currency_symbol>", [attribute], [start_date], [number_of_days|end_date], [interval])
Instead of setting one date as you did before, you’ll have to set start and end dates for your range. Moreover, you’ll need to set an “interval” value which can either be “1” for daily values or “7” for weekly values. Using the prior conversion of U.S. dollars to Great British pounds, your string should look something like this:
=GOOGLEFINANCE("CURRENCY:USDGBP", "price", DATE(2021,1,11), DATE(2021,6,11), 7)
3. Is there any delay when GOOGLEFINANCE fetches values?
While one of the major uses of GOOGLEFINANCE is to return values in real time, it often incurs a delay of up to 20 minutes when retrieving the most recent figures. Furthermore, the values displayed on your spreadsheet will be the values fetched at the time the sheet was opened. To update values, you’ll have to either reopen your sheet or refresh the page.
Our latest tutorials delivered straight to your inbox
