Using Named Cells Creating Deleting and Ranges in Formulas

»Choosing Names for Cells and Ranges

Instead of referring to cells and ranges by their location or cell reference, you can use the name of the cell or range. The Define Name dialog box helps you manage the Names that are being used. Instead of referring to cells and ranges by their location or cell reference, you can use the name of the cell or range. When choosing a name for cells and ranges, you should consider the following:

 1-  Use descriptive names for easy identification.

2- Begin names with a letter or the underscore character.

3-  Do not use hyphens or spaces.

4-  Choose names that have less than 255 characters.

5-  Keep names to 10-15 characters so that they are visible in drop-down menus.

»Naming Cells or Ranges

1- Select the cell or range you want to name. 

2- From the main menu, choose Insert    →  Name →   Define to display the Define

Name dialog box. 

3- Enter the name you want, click on the Add button to add the name. 

4-Click OK.

►OR click in the Name Box on the Formula bar, enter the name you want, and press the

Enter key: 

 

»Navigating Workbooks Using Cell or Range Names

1-  You can navigate to cells and ranges in a Workbook using their defined names.

2-  Click the down arrow beside the Name box, and select the named cell or range you want

from the drop-down list: 

 

  

»Creating Named Ranges Based on Cell Values

With Excel, you can name cells or ranges using existing text labels in the Worksheet. This feature works only if the text labels are adjacent to the cells or ranges you want to name.

1-Select the cells/ranges you want to name along with the text labels: (In the following example, we want to  name each cell in column B with the corresponding text labels in column A): 

 

  

 

2-  From the main menu, choose →     Insert    →   Name  Create to display the Create Names dialog box.

3-  Choose the location of the text labels in relationship to the cells/ranges.(In our example, you would choose Left column for the text label location):

 

4- Click OK.

5- You can use the Name box and its drop-down list to check that the cells/ranges are named properly:

»Deleting Named Cells or Ranges 

1-  From the main menu, choose →Insert    →  Name   Define to display the Define

Name dialog box.

2-  Select the cell/range name you want to delete, click Delete:

    

3-  When finished, click OK.

  

»Using Named Cells and Ranges in Formulas

Once you have named cells and ranges, you can use the cell or range name in your formulas. Formulas using named cells and ranges can be entered using the same methods as cell references.

Using descriptive names can make your formulas easier to understand, and can also save you from looking up the cell reference.

  

For example, you can just type the following formula:

=  unit_price*units_sold

Whereas you would probably have to locate the cells to enter the same formula using cell

reference: =A20*B8.