Categories
Device

How to convert currency in google sheets

How to convert currency in google sheets

Rather than finding currency conversion rates manually, you can import up-to-date and historical currency exchange rates using the GOOGLEFINANCE function in Google Sheets. Using these rates, you can convert any currency to another in your spreadsheet.

As the name suggests, the GOOGLEFINANCE function takes advantage of accurate financial data available from Google itself.

Real-Time Currency Conversion Using GOOGLEFINANCE

The GOOGLEFINANCE function is capable of pulling in current, real-time data about the financial markets. That includes exchange rates, which, when inserted into your spreadsheet using GOOGLEFINANCE, will update every 20 minutes.

The format for a GOOGLEFINANCE formula to find the current exchange rate is =GOOGLEFINANCE(“Currency:USDGBP”) , where you can replace USD and GBP with other suitable three-letter currency codes.

How to convert currency in google sheets

The example above shows the then-current USD to GBP rate. In the example above, the currency codes are used within the formula itself, but you can also refer to these separately.

To do that, type your currency codes in two individual cells (for instance, “USD” in cell A1 and “GBP” in cell B1).

In a third cell, type =GOOGLEFINANCE(“Currency:”&A1&A2) , replacing A1 and A2 with the appropriate cell references for your spreadsheet.

How to convert currency in google sheets

These rates above show the exchange rates listed in column A to column B. The GOOGLEFINANCE formula used in C2, for instance, shows the rate from U.S. Dollars to British Pounds.

This value (0.7691) shows one U.S. Dollar converted to British Pounds. To convert a larger currency figure, you can multiply the larger value against this rate.

For instance, to convert $100 to British Pounds, you would multiply that figure ($100) against the exchange rate value (0.7691), generated using the GOOGLEFINANCE function.

How to convert currency in google sheets

The example above shows three different USD figures in column B converted to GBP. The GOOGLEFINANCE function is used in cells A3 to A5, returning the then-current USD to GBP rate.

By multiplying the USD figure in column B against the USD to GBP exchange rate in column A, the converted GBP amount is returned in column C.

Finding Historical Currency Conversion Rates

The GOOGLEFINANCE function can also be used to provide historical exchange rates. It will list the rate at the end of each day, for a period specified by you. This could be for a single day, a week, a month, or longer.

To do this, click on an empty cell and type =GOOGLEFINANCE(“Currency:USDGBP”, “price”, DATE(YYYY,MM,DD), DATE(YYYY,MM,DD) , where the first nested DATE function is the start date, and the second DATE function is the end date.

Replace YYYY with the year, MM with the month, and DD with the day for both nested DATE functions. You’ll also need to replace the currency codes to match the currencies you’re looking to exchange.

How to convert currency in google sheets

If you only want to display a single date, you can use =GOOGLEFINANCE(“Currency:USDGBP”, “price”, DATE(YYYY,MM,DD)) instead.

How to convert currency in google sheets

Using other functions like TODAY in place of the DATE function, you can also have a rolling list. This means your list will update every day. You can display the last seven days, for instance.

To do that, you can use =GOOGLEFINANCE(“Currency:USDGBP”, “price”, TODAY()-7, TODAY()) .

How to convert currency in google sheets

The TODAY function is used as the end date, meaning your list always updates to show currency exchange rates (in this instance, from USD to GBP) for the past seven days.

If you wanted to do this over a longer (or shorter) period, simply change the number used with the first nested TODAY function from seven to another number.

How to convert currency in google sheets

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?

How to convert currency in google sheets

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:

How to convert currency in google sheets

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.

How to convert currency in google sheets

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 convert currency 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 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.

How to convert currency in google sheets

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.

Related:

Simon Batt is a Computer Science graduate with a passion for cybersecurity.

How to convert currency in google sheets

Joey Karczewski

Jan 5 · 2 min read

Working with various currencies in a Google sheet? Need to convert them into a single currency? This built-in Google Finance formula pulls real-time conversion rates from Google Finance itself, using these to convert your currencies directly, even automatically updating these conversions according to the latest rates. Keep reading to find out how you can use it.

Here is our sample data:

Here, we want to convert the data in th e Amount column (all in different currencies) into Euros in Column D. The formula below identifies the currency to be converted (in Column B), sources the conversion rates from Google Finance (live-updated automatically), and multiplies by the Amount (in Column C) to provide the conversion in your target currency.

Base Formula: =GOOGLEFINANCE(“CURRENCY: ”)*C2

However, this base formula does not convert any figures that are already in your target currency.

To fix this and ensure all data appears in this column, we also need to nest our Google Finance formula in an IF statement:

Here we are specifying that if the currency equals EUR, return the amount without converting, if the currency is not in EUR, perform our Google finance formula to convert it.

Copy the new formula all the way down using the drag function like above, and there we go! We now have a currency converting column in our spreadsheet!

Bonus Points: Change the amount of decimal places shown in column D (Value in Euros) by pressing the increase or decrease decimal places button in the menu above.

Updated on December 28, 2020 by Swayam Prakash

Do you know that it is possible to change the currency in Google Sheet.? In this guide, I will explain to you how to do this. The online spreadsheet app comes with the function GOOGLEFINANCE. It can convert the currencies of one type to another based on the latest exchange rates. Here lies the twist. You do not have to find out or know the exchange rates. This function does that job for you.

The exchange rates on Google Sheets keep updating every 20 minutes. Now, that’s just Google’s way of doing things. I have explained the format of the GOOGLEFINANCE function and how to use it. Also, I have demonstrated how to find the exchange rate when trying to change from one currency to another. Let’s get into the guide.

How To Change Currency In Google Sheets

First, let’s see how to get the exchange rate for comparing or converting two currencies. Though Google Sheet provides the latest exchange rate, it mentions that exchange rates may be delayed. Its quotes are not sourced from all the financial markets of the world. It disclaims all the data they provide is solely for information purposes. People are advised not to use it for trading or similar activities.

Finding the Exchange rate on Google Sheets

Here are the steps. Let’s check for exchange rates for USD and INR. USD is US Dollar and INR is Indian currency.

  • Open Google Sheets
  • In the fx section put the following format =GOOGLEFINANCE(“Currency:USDINR”)
    How to convert currency in google sheets
  • You can use any currency you want to instead of the ones that I have used
  • As you press enter you should see the current exchange value between the two currencies
  • For USD and INR you will see the exchange rate is 73.7383

There is another way to do this where we will mention the USD and INR in the sheet. Then use their cell numbers in the function. It is useful when you are exchanging multiple currencies in the Google Sheet.

let us again take the example of USD and INR.

  • In the fx section put the formula =GOOGLEFINANCE(“Currency:”&cell1&cell2)
  • Replace cell 1 with the cell number in which you have put the first currency
  • Similarly, cell 2 will consist of the cell number in which the second currency is mentioned
    How to convert currency in google sheets
  • Then hit enter and you will see the exchange rate

Steps to Change Currency

This is pretty easy. Once you know the exchange rate, simply multiply that with the existing currency to find the value in the second currency. Sound’s confusing.? Wait, let me demonstrate the same by taking the USD to INR example.

You can do this for any currencies out there. The conversion value will depend upon the exchange rate which varies from region to region.

  • Go to Google Sheets
  • Mention the exchange rate [for the two currencies that you wish to convert the values]
  • Then mention an amount of the 1st currency
  • Now, simply multiply the value of the 1st currency by the exchange rate
  • We know the exchange rate between USD and INR is 73.7378.
  • Let’s say we wish to convert US$50 to INR then check out the screenshot below
    How to convert currency in google sheets
  • The value in INR will be 3686.9. It will display under the C5 cell as you press enter.

I even checked directly in Google Search. The value of USD 50 when converted to INR shows the same value 3686.9 INR.

How to convert currency in google sheets

So, that’ how you can change currency value in Google Sheets. I hope that this was informative.

By now we know how easy it is to convert currency in Microsoft Excel, but what about Google Sheets? The tool may not be as feature-rich as Excel, but it is capable of doing quite a few things, and that includes converting currency, no less.

Convert Currency and get Stock Data in Google Sheets

To get this done on Google Sheets, we will be using the GOOGLEFINANCE function. It will take advantage of accurate financial data taken directly from Google itself.

We like the GOOGLEFINANCE function because it can deliver real-time and current financial data from markets around the world. If you want to know the latest exchange rate, then using Google Sheets shouldn’t be a problem.

Before going forward, we need to point out that the function will update the Sheet at an interval of every 20 minutes.

1] The formula to find the current exchange rate

How to convert currency in google sheets

OK, so when it comes down to knowing the current exchange rate, you must first determine which currency pairs will be used. Were going to focus on American and Jamaican dollars as our primary pair.

To get this done, please type the following into the function section and then hit the enter key:

Depending on your needs, you can replace either currency pair with another. Now, the example above shows the current USD to JMD rate.

2] Get historical exchange rate data

Yes, it is possible to use Google Sheets to gain access to exchange rate information from back in the past. Were not sure how far we can go back and were not about to find out.

The function in question to gain access to historical exchange rate data is as follows:

Make changes to the function to fit your needs where possible.

3] Get live stock prices

How to convert currency in google sheets

Should there ever be a time when you need access to live stock prices, then this can be done from within Google Sheets with ease.

In order to showcase live stock data, use the following function:

As you can see, NSE is used as the stock exchange market of choice, and it means the National Stock Exchange of India Ltd, and the ticker is RELIANCE. And as you can see, the attribute is limited to just price.

4] Get historical stock price data

How to convert currency in google sheets

Now, just knowing the price for the day might not be enough, so how about learning of the historical data relating to the ticker?

Get everything done by running the following command. Be sure to edit to your liking:

The above function will show data from the period of 2018/3/10 to 2018/10/3 on the daily. If you want, DAILY can be changed to WEEKLY or even MONTHLY. Just work with whatever works for you.

What we’ve laid out here are just the basics. Maybe in the future, we will go a little bit deeper for you to understand.

You may think in what scenario will I need to convert text to numbers in a Google document or in Google sheets.

Think about it this way: if you are given a spreadsheet with numbers, it will be easy to pull off some of the formulas required to get a proper calculation of whatever it is that you are trying to analyze.

On the other hand, the “text” format of those numbers can make it very difficult and sometimes even impossible to pull these same formulas off.

When you are dealing with measurements like currency in which a number is needed for the analysis, formulas are extremely useful and can save a lot of time.

In this article, we’ll go over how you can convert the “text” format into “number” format in Gooogle Sheets to more easily satisfy the formula constraints.

This Article Covers:

Converting Text To Numbers Using VALUE Function

There are a few formulas you can use to convert text to numbers in Google Sheets

When looking to convert text into a number, the first option you have is to use the “Value” function as such:

Replace Dots with Comma to Convert Text to Numbers

Unfortunately, this will not always work when you receive data from a file in which you need to convert the “text” format into a “numbers” format.

Perhaps you are given files in which the commas have been replaced by “.” or “periods” and are being read in the “text” format.

This is sometimes the case when you get from countries that use dot instead of a comma.

In this case, you will want to use another formula:

How to convert currency in google sheets

This works in a very similar way to the “REGEXTRACT()” function in that it finds and replaces the “text” format of the number into the “number” format of the number.

This works by replacing the “.” with a “,”. This will help convert the number “143.244” to “143,244” which is the same as “143244” that can be used in a formula for a calculation.

You can actually test out whether or not this works by using the number generated from the “text” format of that number by using it in a calculation.

How to convert currency in google sheets

Converting Data with Currency Text to Number

When you import data from another document into Google Sheets, you may find yourself with a “$” symbol or another currency format in front of the number as a “text”. You can do this by using the “To Pure Number” formula as such:

How to convert currency in google sheets

Extracting Numbers From Text

Sometimes, you might import a piece of text from another data set. This text could be an entire sentence that has numerical values written inside of it.

For example cell A1 contains the following text:

Last month I paid $200 for blog maintenance. This month I am expected to only pay $150 for fewer services.

Let me show you two formulas to get this done:

Formula Method 1

In order to extract the numbers from this text, we will use both “Split” and “Concatenate” functions at the same time.

As you can see, the “$200” part did not get rid of the sign. If this is important, we can easily go back and use the “Pure Number” formula mentioned earlier.

Formula Method 2

We can also use the “Split” function in conjunction with the “Regexreplace” function.

As you can see, both methods do the same thing in regards to extracting the number from the text.

So these are a few methods you can use to convert text to numbers in Google Sheets.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

There are many different currencies in the world, and you can format each of them in different ways. For example, for US dollars, you can format them to have the dollar sign or the letters “US” in front of the dollars. Please check here if you work with Microsoft Excel.

Step 1: Select the numbers you want to format as currency;

How to convert currency in google sheets

Step 2: Click the “Format” tab from the ribbon, and click the “Number” command from the drop-down list;

How to convert currency in google sheets

Step 3: In the extended list, click “More formats” at the bottom and then “More currencies“;

How to convert currency in google sheets

Step 4: In the “Custom currencies” window, select the currency type, e.g., US dollar;

How to convert currency in google sheets

Step 5: Click the arrows after the currency and choose one currency type from the list, e.g., “USD $1,000.00”;

How to convert currency in google sheets

Step 6: You will see the numbers are now in the currency format with “USD $” in front of the numbers.

How to convert currency in google sheets

Step 7: If you want to format the dollars into the accounting format, please click the “Format” tab from the ribbon and then select “Number” from the list, the same as in step 2; then in the extended list, select “Accounting” from the drop-down list;

How to convert currency in google sheets

Step 8: The currencies are now in the accounting format with the dollar sign align on the left and numbers align on the right.

How to convert currency in google sheets

Joey Karczewski

Jan 5 · 2 min read

Working with various currencies in a Google sheet? Need to convert them into a single currency? This built-in Google Finance formula pulls real-time conversion rates from Google Finance itself, using these to convert your currencies directly, even automatically updating these conversions according to the latest rates. Keep reading to find out how you can use it.

Here is our sample data:

Here, we want to convert the data in th e Amount column (all in different currencies) into Euros in Column D. The formula below identifies the currency to be converted (in Column B), sources the conversion rates from Google Finance (live-updated automatically), and multiplies by the Amount (in Column C) to provide the conversion in your target currency.

Base Formula: =GOOGLEFINANCE(“CURRENCY: ”)*C2

However, this base formula does not convert any figures that are already in your target currency.

To fix this and ensure all data appears in this column, we also need to nest our Google Finance formula in an IF statement:

Here we are specifying that if the currency equals EUR, return the amount without converting, if the currency is not in EUR, perform our Google finance formula to convert it.

Copy the new formula all the way down using the drag function like above, and there we go! We now have a currency converting column in our spreadsheet!

Bonus Points: Change the amount of decimal places shown in column D (Value in Euros) by pressing the increase or decrease decimal places button in the menu above.

By now we know how easy it is to convert currency in Microsoft Excel, but what about Google Sheets? The tool may not be as feature-rich as Excel, but it is capable of doing quite a few things, and that includes converting currency, no less.

Convert Currency and get Stock Data in Google Sheets

To get this done on Google Sheets, we will be using the GOOGLEFINANCE function. It will take advantage of accurate financial data taken directly from Google itself.

We like the GOOGLEFINANCE function because it can deliver real-time and current financial data from markets around the world. If you want to know the latest exchange rate, then using Google Sheets shouldn’t be a problem.

Before going forward, we need to point out that the function will update the Sheet at an interval of every 20 minutes.

1] The formula to find the current exchange rate

How to convert currency in google sheets

OK, so when it comes down to knowing the current exchange rate, you must first determine which currency pairs will be used. Were going to focus on American and Jamaican dollars as our primary pair.

To get this done, please type the following into the function section and then hit the enter key:

Depending on your needs, you can replace either currency pair with another. Now, the example above shows the current USD to JMD rate.

2] Get historical exchange rate data

Yes, it is possible to use Google Sheets to gain access to exchange rate information from back in the past. Were not sure how far we can go back and were not about to find out.

The function in question to gain access to historical exchange rate data is as follows:

Make changes to the function to fit your needs where possible.

3] Get live stock prices

How to convert currency in google sheets

Should there ever be a time when you need access to live stock prices, then this can be done from within Google Sheets with ease.

In order to showcase live stock data, use the following function:

As you can see, NSE is used as the stock exchange market of choice, and it means the National Stock Exchange of India Ltd, and the ticker is RELIANCE. And as you can see, the attribute is limited to just price.

4] Get historical stock price data

How to convert currency in google sheets

Now, just knowing the price for the day might not be enough, so how about learning of the historical data relating to the ticker?

Get everything done by running the following command. Be sure to edit to your liking:

The above function will show data from the period of 2018/3/10 to 2018/10/3 on the daily. If you want, DAILY can be changed to WEEKLY or even MONTHLY. Just work with whatever works for you.

What we’ve laid out here are just the basics. Maybe in the future, we will go a little bit deeper for you to understand.

Proper formatting in a spreadsheet can make it much easier for your audience to interpret the data that they are looking at. When the data in a row or column is uniform, it’s easier to spot problems or errors. This is particularly useful when you have monetary values in cells, as some of the values may have different numbers of decimal places, making it difficult to properly evaluate the data.

Google Sheets has a number formatting option that lets you tell the spreadsheet that values in certain cells are currency. Those values will then be preceded by a dollar sign, and will all have a uniform number of decimal places, thereby making it much easier to read the data. Our guide below will show you how to select cells and apply currency formatting to those cells.

See also

  • How to merge cells in Google Sheets
  • How to wrap text in Google Sheets
  • How to alphabetize in Google Sheets
  • How to subtract in Google Sheets
  • How to change row height in Google Sheets

How to Format Values as Money in Google Sheets

The steps in this guide were performed in the Web browser version of Google Sheets, specifically Google Chrome. This article assumes that you currently have cells in a Google Sheets spreadsheet that are not formatted as monetary values, but that you would like to be.

Do you need to display an important currency amount in a prominent location? Find out how to merge cells in Google Sheets and add that value to a cell that is much larger than the other ones in the spreadsheet.

Step 1: Go to Google Drive at https://drive.google.com/drive/my-drive and open the spreadsheet containing the cells that you would like to format.

Step 2: Select the cells. Note that you can select an entire column by clicking the column letter, or you can select an entire row by clicking the row number.

How to convert currency in google sheets

Step 3: Click the $ sign in the gray toolbar above the spreadsheet.

How to convert currency in google sheets

Alternatively you can format cells as currency by clicking the Format tab at the top of the window, clicking Number, then selecting one of the currency formats there.

How to convert currency in google sheets

Do you have a spreadsheet in Google Sheets that has unwanted or incorrect fill colors? Learn how to remove fill colors from a spreadsheet if they are causing problems, or if you would simply prefer not to have them.

Share this:

  • Facebook
  • Twitter
  • LinkedIn
  • Pinterest
  • Reddit

How to convert currency in google sheets

Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.