DEFINE A NAME FOR A CELL OR CELL RANGE ON A WORKSHEET

DEFINE A NAME FOR A CELL OR CELL RANGE ON A WORKSHEET
TYPE OF NAMES

There are several types of names that you can create and use

DEFINE NAME

A name that represents a cell, range of cell, formula, or constant value. you can create your own define name, and Microsoft Office Excel  sometimes create a defined name for you, such as when you set a print area. 

TABLE NAME

A name for an Excel table, which is collection of data about a particular subject that is stored in records (rows) and fields (columns). Excel creates a default Excel table name of Table1, Table2, Table3, and so on, each time that you insert an Excel table, but you can change a table's  name to make it more meaningful.


LEARN ABOUT SYNTAX RULES FOR NAMES

The following is a list of syntax rules that you need to be aware of when you create and edit names.

  • VALID CHARACTERS
The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Note You cannot use the uppercase and lowercase characters "D", "d", "S", or "s" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

  • CELL REFERENCE DISALLOWED
Names cannot be the same as a cell reference, such as A$100 or R1C1. 
  • SPACES ARE NOT VALID
Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
  • NAME LENGTH
A name can contain up to 255 characters.
  • CASE SENSITIVITY
Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

DEFINING AND ENTERING NAMES
You define a name by using the:

  • NAME BOX ON THE FORMULA BAR
This is best used for creating a workbook level name for a selected range.
  • CREATE A NAME FROM SELECTION
You can conveniently create names from existing row and column labels by using a selection of cells in the worksheet.
  • NEW NAME DIALOG BOX 
This is best used for when you want more flexibility in creating names, such as specifying a local worksheet level scope or creating a name comment.

Note By default, names use absolute cell references.

You can enter a name by:

  • TYPING
Typing the name, for example, as an argument to a formula.
  • USING FORMULA AUTOCPMPLETE
Use the Formula AutoComplete drop-down list, where valid names are automatically listed for you.
  • SELECTION FORM THE USE IN FORMULA COMMAND
Select a defined name from a list available from the Use in Formula command in the Defined Names group on the Formulas tab. 

Dailymotion


AUDITING NAMES

You can also create a list of defined names in a workbook. Locate an area with two empty columns on the worksheet (the list will contain two columns, one for the name and one for a description of the name). Select a cell that will be the upper-left corner of the list. On the Formulas tab, in the Defined Names group, click Use in Formula, click Paste and then, in the Paste Names dialog box, click Paste List. 



Download Sample File Click Here
 


EmoticonEmoticon