If you work with multiple currencies, it might be a huge pain to hunt for all of the currency conversion rates on a single day. Fortunately, if you’re working within Google Sheets, you can leverage a built-in function that automatically fetches and changes the current change rate. Let’s see how easy it is to change currencies in Google Sheets.

How to Use Google Sheets to Create a Currency Conversion Rate

For the purposes of this book, let us assume that we are paid in US dollars to write articles for a database. However, because we live in the United Kingdom, any money we receive will be converted to Great British Pounds when it reaches our bank account. Therefore, how much will we earn in GBP when we are paid at the end of the month?

Sheets Currency Conversion Start

To address this, we can make use of Google’s Finance feature. This is a highly detailed feature that is mostly used to compare the inventory values of two businesses. However, if we put two foreign currency codes in place of firm codes, we obtain a foreign currency conversion!

First, choose an empty cell the place you need the foreign money conversion charge to go. Then, kind =GOOGLEFINANCE("CURRENCY:EXAEXB") into it. Replace “EXA” with the foreign money code you need to convert from and “EXB” with the foreign money code you’re changing to.

In our spreadsheet, we wish to convert US {dollars} to Great British kilos. The code for US {dollars} is “USD,” and the code for Great British kilos is “GBP.” As a consequence, our last perform will seem like this:

When we press Enter, the cell is filled to the brim with a prolonged value:

Sheets Currency Conversion Formula

This is referred to as the conversion fee. That is the amount of kilograms you would receive if you converted a dollar to GBP in our spreadsheet. It’s not very useful on its own, but with a little maths, we can determine how much we’ll be paid in kilos.

How to Calculate the Currency Exchange Rate

To determine how much we will likely be paid, we multiply the amount we will receive in USD by the currency conversion fee. So, in cell C2, we kind =B2*D2. Remember, you possibly can click on cells as a substitute of manually getting into their coordinates. This tells us how much we’ll earn on a $100 labor cost.

Sheets Currency Conversion Calculation

Notice the blue field on the proper backside of the cell we’ve chosen? This can be dragged down to apply the same technique to the rest of the desk. However, if we do so, Google Sheets will infer that we want to compare the cell under the original fee to the cell under the currency conversion cell, which will result in an error!

To repair this, we want to edit the system so it reads =B2*$D$2. The green dollar signs instruct Google Sheets to never increase the letter or number in that cell, ensuring that it will always equal our conversion rate. Now that we’ve dragged the blue field down, we can see how much we’re likely to earn.

Sheets Currency Conversion Drag

How to Calculate a Currency’s Historical Conversion Rate

Assume that the spreadsheet we’re currently using represents each month. If we save the foreign currency conversion function in its current state and review this month’s sheet three months later, the foreign currency conversion charge adjusts to the current value on that day, resulting in inaccurate logs. As such, if you’re producing a monthly spreadsheet, it’s a good idea to “freeze” the speed at the end of the month to ensure an accurate report.

To do this, we’ll add a date. If we needed to determine the speed again on July 31, 2020, we might do it by use the following system:

When you press Enter, the spreadsheet will abruptly replace the “Date” and “Close” disciplines with a “Date” and “Close” discipline. The date discipline indicates the specific time of the speed, whereas the close discipline indicates the current closing charge. You may then utilize this older fee to obtain an accurate representation of how it was in the past.

Sheets Currency Conversion Histori

You need not panic if you enter this system and your conversions abruptly stop. This is because employing the date feature shunts the conversion charge across multiple cells. As such, you’ll want to rebuild the cell system to align it with the new placement of the speed.


Write A Comment