CHAPTER 8 Flashcards
Range Names
A worksheet consists of rows and columns. We can refer to any cell on a worksheet by specifying the row and column at the intersection of which we have the cell.
In addition to providing an address for a cell, Excel gives us the option to provide a name to a cell or a group of cells. The easiest way to give a name to a cell is to enter the name in the ‘Name Box’ in a worksheet.
Giving range names to multiple cells
If we need to name only one range of cells, we can do this easily by selecting the appropriate range and typing the name in the Name Box. However, if we need to give names to a large number of cells, naming each cell one by one can be a tedious process.
The way to do this is to select the data, including the row and column labels. Then:
Mouse:
FORMULAS ribbon -> Defined Names group -> Create from Selection
Excel shows us an option to create names from the selection in the top row and from the left column. We click OK and we are done. We have just named all the cells.
Ranging individual cell
Excel accomplishes the task of ranging each cell by giving a name to each row (from the labels in the left column) or by giving a name to each column (from the labels in the top row). When both row labels and
column labels are available, Excel uses the intersection of the row and column to identify the intersecting cell. Suppose, the range B5:E5 has been named “MAR” and the range B3:B14 has been named “NORTH”. The intersection of these selections is cell B5, or 46. We get the intersecting cell by naming both ranges. That is, we type ‘= mar north’ as a formula in a cell to get the value in the intersecting cell – in this case it is 46.
Note that the range names for the selections are not case sensitive and that the two names are separated by a space.
Advantages of range names
Range names generate several advantages:
a) Range names are absolute references. We could go to any cell in the worksheet and enter “=mar north” and we would get a reference to cell B5 or 46. Once we have defined a range name, we need not be concerned about using dollar signs to make our reference absolute.
b) By default, a range name in one sheet in a workbook can be used in another worksheet in the same workbook without having to specify the
name of the worksheet where the range name resides. In other words, a range name is absolute not only with reference to the row and the column, but also with reference to the worksheet.
c) We can update data easily by referring to the range name
d) By providing logical names to ranges, it is easy for us to use a value in a formula. For instance, we could call a cell having the value 5% “RateOfInterest”. Then we can apply the “RateOfInterest” to our loan amount to calculate the interest amount.
e) Range names facilitate navigation. We can quickly go to a named cell. (Pressing F5 or CTRL-G invokes the GoTo command and leads us to the named cell).
As we have seen, range names have many advantages. However, it is neither necessary nor advisable to have a range name for each variable
in our spreadsheet. We need to define range names only for key variables, and not for each variable.
Rules for naming ranges
Range names must have to follow certain rules:
a) A name must begin with a letter as long as we do not use “R” or “C” by itself (either in upper or the lower case)
b) A name starting with “R” or “C” followed by a number is also not valid if it appears to be a cell address (e.g., “R5000” can be interpreted as
column “R” row 5000 and hence is not valid. However, “R5000000” is valid as there is no row 5000000
c) A name can also start with an underscore (_) or a backslash ().
d) Remaining characters in the name can be letters, numbers, periods, and underscore characters.
e) A cell reference such as G5 cannot be used as a range name. When we type G5 in the Name Box, Excel assumes we want to go to G5.
f) The length of the range name can vary from 1 to 255 characters.
g) Spaces are not allowed as a part of a name.
Scope of a range name
By scope of a range name, we refer to the location in which the name is recognized. For instance, if we create a range name in Sheet1 in Book1, the range name will be recognized in any worksheet in Book1.
However, it will not be recognized in Book2 (unless we specify the complete file path to the workbook containing the range name). Even within a single workbook, we can restrict the scope of a range name to a single worksheet by specifying the scope when creating a new name.
Displaying Range names
Once we have named ranges in our workbook, it would be helpful to list all the range names and the cells to which they refer.
How to paste range names:
Keyboard: F3, ALT-l (= lower case L)
Mouse: FORMULAS ribbon -> Defined Names group -> Use in formula -> Paste Names
We need to use the ‘Paste List’ command after we have created all the required range names. Once a list is pasted, it does not update to reflect new range names or changes to existing range names.
On occasion, we may find that, instead of creating a new workbook, it is more expedient to modify an existing workbook as it already has a model or data that we can use. If we decide to do this, a prudent
practice would be to clear the existing workbook of old range names. The simplest way to do this is to use the Name Manager and delete the range names in the Name Manager.
What are the advantages of range names?
a) Range names are absolute references
b) We can update data easily by referring to the range name
c) Range names facilitate navigation
The scope of range names can be set to:
a) A worksheet
b) A workbook
Valid range name:
JamesBond007
How would you give a name to each cell in a large number cells?
Select the desired cells, formula -> Define names group -> create from selection
We can define a range name from the following:
a) ‘Name Box’ on the formula bar
b) ‘Create from selection’
c) ‘Define Name’
