Name Range in Excel

What is a Name Range ?

Name Range in Excel, a name  to any cell, a range of cell, selection of a area, after naming the cell or area or a range of cells we only need to remember the cell or area reference by its name not from the cell or range reference.

Benefits of Name Range

1.   After naming the cell or range or selected area we do not need to remember its cell address or      

      reference.

2.   After naming the cell or range or selected area, now it will becomes very easy to put name in    

      formula and function.

3.   Name Range data always remain constant and does not change its cell references.

Below are some methods of Name Range Data

  1.   Using the Name Box
  2.   Using the Create from Selection Command
  3.   Using the Define Name command

Below we will discuss the the each of above mentioned heading of Name Range in Excel

1. Using Name Box

In Excel, we can create a name range by selecting a cell or a range of cell and typing a appropriate name in the "name box" field.  See the image below

In the below given image we have data set, in column 1st we have "Salesman" and 2nd column we have "Sales figure" of "Sales_value". So we will name range in this data that from range "B2:B5" we give a name as "Sales_Value" and type this name in the field of "name box"

Image 01

See below image :-

   

Image 02

In above image we can see data range "A2:A5", so according to name range rule we can give a name to this entire range by selecting the whole range area and typing the name in the name box field "Student".

So whenever we need to write any formula, then we do not need to give the entire range, then just name range require, like the whole area = "Student"

We want to sum sales value of all the Student, so the formula will be :-

=Sum(Sales_Value) or =Sum(B2:B5) bot are same.

Without giving a sales range, we mention directly "Sales_Value" instead of "B2:B5" and get the same result.

Image 03

2.   Using Create from Selection Command

This is the second method of creating name range. If we have a data set then for creating a name is simple process by selecting the data 

First we need to select the data range and then 

Image 04

go to formula tab, and click  on "Create from Selection"  or short cut key is "CTR+SHIFT+F3" see below image 

Image 05

Once the "Click" the following image will appear :-

Image 06

In this image we can see that there are two image 1st image is "data range" and 2nd image is "how to create name range" image. in this 2 image "Top row" and "Left column" both are checked it is suggesting us that if we press simply "OK" then it will create a name range. All the heading of all "Row" and "Column" will be names of their respective rows or columns."Top row" heading "Salesman", "East", "West" etc. and "Left column" heading will be "Amit", "Rahul" etc. See below image :- 

Image 07

When we select a rows from "B3:E3" it gives a name of this range as "Rahul" as "Left heading" and in second image when we select "C2:C5" it gives the name "West" as the "Top row" heading

So this is another example of  Name Range" of cell or range of cells.

 3.   Using Define Name command

There is another method for creating a name range in  Excel. We have a set of data as given in the following image :-

Image 08

For creating the name range with the help of "Define Name" method we have to go "Formula Tab " and click on "Define Name" drop down and click on "Define Name"

 

Image 09

We will get the following image

Image 10

From starting, see "New Name" name is "West" and then scope "Workbook" or "Sheet no." and at the end "Refers to:" "C2:C5" and simply press enter. And the name range is set for range "C2:C5" is "West"

In the field of  "New Name" instead of West we can put any name according to out need but there are some condition for putting a name and the condition are as follows:-

   

•  Name should not contain more than 255 character and begin with a letter, an underscore( _ ), or backslash(\) and should not contain any space

• Name  should not conflict with cell reference like "A1" or "B21"  or so on.

• Single letters will be OK for name like "A", "B", "F", etc), but "R", and "C" are not allowed they are reserved and "R=Row" and "C=Column"

Edit a Name Range

Some time if we have to edit our name range then what to do?

So in this case we need directly go to "Name Manager", see image below :-

Image11

and simply click on it we will get the following pictures :-

With help of above image we can easily modify or edit our name range.

Delete a Name Range

Now if we want to delete a name range  in excel simply go to "Name Manager"  and click we will get the following image and just click delete on it, the name range will be deleted. See below :-

So in this tutorial we learned about name range in excel. Name range has many more uses if we will go into deep of name range, we will learn more about it.

Thanking you.

Regards

NarendrasExceltips.blogspot.com