– Advertisement –
Users who create spreadsheets using Google’s web service often face the need to perform financial transactions, including currency conversion. For this purpose, some independently find the current exchange rate and create formulas based on it. However, the task can be automated by entrusting the receipt of the current rate directly to Google. This way, you will get a dynamically updated table with constant correct metric unit conversions.
– Advertisement –
We’ve all had situations where we needed to convert one unit of measurement to another. For example, the mass of objects was measured in ounces rather than kilograms, the lumber you ordered is measured in meters rather than feet, or someone is planning a trip to the US or UK unit conversions practice and wants to know the speed limits in miles per hour (mph).), while they measure in kilometers per hour (km/h). Luckily, there is an easy way to calculate these measuring unit conversions using the free online OneConvert calculator, which will convert units from one measurement system to another. All you have to do is enter the number and measurement system!
Definition of alphabetic currency codes
You only need to find out the letter codes of the currencies of different countries that will participate in your conversion. To do this, enter the appropriate query in the search engine for a specific currency or all at once.
Google Sheets provides codes for stock exchanges, but we must understand the complex operations of quotes and shares in this article. We will focus solely on using the basic unit conversions, which will be helpful to many users rather than just narrowly focused specialists.
Creating a help sheet
You can display the current exchange rate on the same table sheet where you perform the conversion by selecting a separate block for the data. However, this design is not always suitable, so first, you should figure out how to create an auxiliary sheet where the current rates of all used financial unit conversions will be displayed.
- Create a new sheet and give it an appropriate name. Create a list of cells with the names of exchange rate ratios. For example, if you are interested in the ratio of the ruble to the dollar, write that in the cell title. This is necessary to understand what information will be displayed nearby.
- Now, let’s start creating the function itself. Declare it next to the first-course name by writing =GOOGLEFINANCE().
- Inside the parentheses, open the double quotes and enter the currency ratio. For example, the entry of the ruble to the dollar will look like “USD.”
- Do the same with the remaining cells, changing the currency ratio. Remember the main thing – the letters must fit inside double quotes. The final formula looks like =GOOGLEFINANCE(“USD”).
- A separate sheet, which we will refer to during further conversion, is ready. Now you know that, for example, 1 ruble equals 0.01256 dollars (at the time of writing). This data will be automatically updated approximately every 20 minutes, so the course can always be considered up-to-date. All that remains is multiplying the available amount to understand how much it will be in another currency.
Converting currencies in Google Sheet
There are many different situations when you need to convert one currency to another. For example, I took a table with employees, an available payment in rubles, and the need to display how much they will receive in another currency. This is relevant, for example, when employees work in other countries, and there is an agreement with them on payment in a specific currency. You can see the original table itself in the following screenshot.
The conversion is done by multiplying the current amount of one currency by the rate in another, which is how standard money calculators work. To do this, you do not need to use any special functions; you only need to specify two cells and a mathematical operation between them.
- Write =B2*, where B2 is the cell with the amount in the current currency.
- Switch to the auxiliary sheet and select the cell with the rate of the currency into which you want to convert the amount.
- Return to the previous sheet and press Enter. You will see the amount in a common numeric format, as indicated by the number of decimal places.
- If we stretch this function now, it will not work correctly due to the offset of the cells, so put a constant ($) between the column letter and the rate line. As a result, you will get the function =B2*’AuxiliarySheet’!B$4, where B2 is the original amount, ‘AuxiliarySheet’! is the name of the sheet with the exchange rate, and B$4 is the pinned cell with the selected rate.
- Expand the function by dragging the right edge of the cell down, ensuring that all converted amounts are displayed correctly.
- To display numbers more clearly, let’s change the cell format. Highlight the converted values, open the Format menu, hover over Numbers, and select Currency.
- Find the required currency in the list and left-click on it.
- Instead, you can write the letter designation yourself and click “Apply.”
- Please note the following image. You can see that the amounts are now displayed more clearly, showing the currency used.