Name a cell with an excel Range name

In this tutorial. We’re going to set the Excel Range name for the cells in worksheet. Let’s have a look on the worksheet i’ll be using.

Name a cell with an excel Range name

In this worksheet, we’ll change the price of wines from US Dollars to British Pounds, Euros and Japanese Yen. I’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.

Name a cell with an excel Range name

Step#1:

Let’s calculate the currency amount in British Pounds for the “Chateau Lafite”. So, we’ll use the following way:

Name a cell with an excel Range name

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.

Name a cell with an excel Range name

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.

LINK EXCEL WORKSHEET TO WORD DOCUMENT

We’re going to rewrite the formula. So i’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 move to “Formulas” in Ribbon Tab and Select “Create from Selection”.

Name a cell with an excel Range name

Step#4:

A pop-up box will appear on the screen.Name a cell with an excel Range name

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.

Name a cell with an excel Range name

HOW TO DO DATA VALIDATION IN EXCEL

Step#6:

Now moving to the main Worksheet to apply the formula again. Again selecting box under “British Pounds”, and applying formula:

Name a cell with an excel Range name

It’ll give same value but with different Formula Description that is clearly understandable.

Name a cell with an excel Range name

In the same way, we’ll do for the Euros and Japanese Yen.

Name a cell with an excel Range name

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.

Name a cell with an excel Range name

Step#8:

Select the three boxes, and drag them down to get the values of all the prices in currencies.

Name a cell with an excel Range name

In this way, we can define and use the range names in formulas. That makes it easier for user to understand the formula.

Share your reviews in the comment section.

Leave a Reply

Your email address will not be published. Required fields are marked *