In this tutorial. We’re going to set the Range names for the cells in worksheet. Let’s have a look on the worksheet we’ll be using.
In this worksheet, we’ll change the price of wines from US Dollars to British Pounds, Euros and Japanese Yen. We’ll be using currency conversion rates in a table so the values will be used in this worksheet. Let’s see the exchange rate table.
Step#1:
Let’s calculate the currency amount in British Pounds for the “Chateau Lafite”. So, we’ll do it in the following way:
Where C4 is the reference to price in US Dollars and “ExchangeRates!B3” is the reference to conversion rate of USD to GBP. “ExchangeRates” is the name of the worksheet containing table of currency conversion rates. We’ll get the following value in box.
Step#2:
Wouldn’t it be nice if instead of “ExchangeRates!B3”, it is something “=C4/USD_GBP”. So it’d be completely clear that what formula is representing. This can be done by using Range Names. Range Names point to the value in a cell that can be used in formulas, constants and tables.
We’re going to rewrite the formula. So we’ll delete the existing formula.
Step#3:
First of all, we’ll create the names of the currency conversion rates. For that we’ll select both columns in “ExchangeRates” worksheet. And we’ll go to “Formulas” in Ribbon Tab and Select “Create from Selection”.
Step#4:
A pop-up box will appear on the screen.
Select the “Left Column” and Click OK. So, it’ll create the range names point to the values in Column B.
Step#5:
You can check the defined range names by clicking box in left up corner of worksheet table.
Step#6:
Now moving to the main Worksheet to apply the formula again. Again selecting box under “British Pounds”, and applying formula:
It’ll give same value but with different Formula Description that is clearly understandable.
In the same way, we’ll do for the Euros and Japanese Yen.
Step#7:
There is another way to get a list of range names to be used in formula or constants. For that you need to select box, write formula structure and go to “Formulas” in Ribbon Tab” and select “Use in Formula”. It’ll give a list of range names, so you can select one to use.
Step#8:
Select the three boxes, and drag them down to get the values of all the prices in currencies.
In this way, we can define and use the range names in formulas. That makes it easier for user to understand the formula.