Currency in Google Sheets

Instead of typing “Euro to USD” in Google search, you can check the same exchange rates inside Google Sheets using the GOOGLEFINANCE function.

With this function, you can convert many types of currencies to another.

Currency conversion using GOOGLEFINANCE

Let’s try out the following experiment to find out whether the function works the same as typing the phrase inside google search.

If you type “100 usd to eur”, this is the result I’m going to get from today (7/14/2020).

This value changes every day. The GOOGLEFINANCE will return more current results, that’s why there is a very slight difference between them.

The following formula returns the current exchange rate:

If you want to convert a certain number (100 in our case), you have to multiply this number by cell A2.

Here, are few other examples of exchange codes:

Currencies	Code
Euro to US Dollar	EURUSD
US Dollar to British Pound	USDGBP
Swiss Franc to US Dollar	CHFUSD
Polish Zloty to Euro	PLNEUR

You can find the whole list of currency codes on the IBAN website.

Formatting currencies

A currency has two decimal numbers, but sometimes we need bigger precision. In our case, we are going to format the number to have two decimal places.

To do it, click cell B2 and click Decrease decimal places twice.

If you deal with currencies, you probably want a currency symbol in front of a number.

To add this number, select both cells and navigate to Format >> Number >> Currency.

In both cases, Sheets inserted the pound symbol. What we need are a dollar and euro.

Let’s change the dollar first. Click cell A3 and go to Format >> Number >> More Formats >> More Currencies.

You can type the dollar sign ($), or type US Dollar and click a choice from the list. In this case, it’s only one option.

Click apply to change currency symbol to a dollar.

Do the same for the Euro. In this case, you probably don’t have the euro sign on your keyboard, so typing euro and choosing from the list is the only option.

After these changes, you are going to have the correct currency symbols.

Sometimes, you may want to display currencies differently. Instead of using a sign, you can use code.

Let’s take a look at the Polish currency. Polish currency has a sign at the end of the number, but sometimes you can find a notation with the code in front of a number.

If you want to find it on the currency list, there is no such option, but you can type PLN plus space to display it this way.

Next time you try to add this code, it will be already saved in your list. This is how it looks like:

Remove the currency symbol

To remove a currency sybol, choose Format >> Number >> Number.

Accounting formatting

Apart from the currency format, there is also an accounting format.

This format contains two decimal points, a thousands separator, and a currency sign locked to the far left side of a cell. Negative numbers are inside parenthesis and don’t have a negative sign in front of them.

If you try to change currency formatting to accounting (Format >> Number >> Accounting), it will display the pound sign, instead of a dollar. If you try to change it to a dollar, the accounting format is changed to a currency format.

Before you can use this format, you have to set your locale.

You can change this in File >> Spreadsheet settings.

Now, you can change the formatting to accounting. There is a dollar sign, instead of the pound.

You can do it in many other languages.

Apply conditional formatting

To quickly see the lower and higher numbers in your worksheet, you can use conditional formatting. Just select numbers, go to Format >> Conditional formatting. Click the Color scale and choose one of the format rules.

Now, you can easily see the amount of money different people earn.

Display a currency trend

You can display a trend of currency by using the SPARKLINE function. In this example, the US Dollar is the base currency.

Let’s calculate exchange rates for Euro and Pound for the following periods: 7, 30, and 365 days.

Use this formula in cell C5:

Drag the result to the remaining cells. This is the result: