Named Ranges in Excel


B.Com, ACA, CISA, CIMA

 

One of the most common utilities of MS-EXCEL is its capability to handle complex formulae and calculations. These formulae need to be documented, otherwise a subsequent user may not be able to understand the formula. One of the most handy ways to do that would be to use range names. Wouldn’t a formula like =Income*35% be much more appealing than +C35*35% ? In MS-EXCEL, one can define a range name for a single cell or a group of cells. Such named ranges can be used in formulae and functions.

Named Ranges offer several advantages :

1. They help in documenting formulae

2. They circumvent the problems associated with relative referencing since named ranges are always absolute

3. They facilitate quick worksheet navigation. In fact, once a particular range has been named, reaching that particular cell is very easy. Just choose the range name from the Name Box (appears at the top left side of the screen below the font name) and you are taken there.

To name a range :

1. Select the range to be named.

2. Click the Name box on the left side of the formula bar

3. Type the range name (up to 255 characters). Valid names cannot use spaces and the first character must not be a number. Also, the name cannot look like a cell address such as B14.

4. Press Enter.

OR

1. Select the range

2. Select Insert/Name

3. Choose Define

4. Type the name of the range.

The entire task of creating range names from a list can be automated by selecting the range of cells along with the column and row labels and selecting Insert/Name/Create.

Based on where the labels are situated, one can automatically create the range name definitions by selecting the particular option from the four given above. In case of a two-dimensional table, it is very easy to create range names very quickly using the above method. Consider for example, the quarterly — regionwise sales table shown below :

  January February March
Mumbai 100 6534 345
Kolkata 123 56 67
Delhi 33 45 567
Chennai 4325 345 456

In the above table, one can select the entire range and automatically create seven range names. One can then refer to a range of values by reference to that range. For example, Mumbai would return {100; 6534; 345}. To refer to a particular cell, one can use range intersection formula. For example, = Mumbai Mar would return 345.

In case a formula is created before the name has been defined, it is possible to automatically change all the formulae to reflect the name rather than the cell address. For this purpose, select Insert Õ Name Õ Apply.

To delete a name, select the range name from the define name box and choose Delete. One can also define constants using the range name concept. For example, the TaxRate constant could refer to 35%. For this purpose, define TaxRate in the name box and the formula i.e. 35% in the Refers to Box at the bottom and click Add.

To have the list of all the range names in the workbook, select Insert Õ Name Õ Paste and choose Paste List from the options.

 
Reply   
 

LEAVE A REPLY

Your are not logged in . Please login to post replies

Click here to Login / Register  

 


×