Complete Guide on Range-Naming Operations in MS Excel

Complete Guide on Range-Naming Operations in MS Excel

  • Post author:
  • Post category:Excel Tips

Excel is one of the many spreadsheets that manipulates data through the use of Ranges. A range in Excel is usually a group of cells. In most cases, these cells are usually adjacent cells that are referenced or selected so as to perform a common action on them. With Ranges, one can: name, adjust their Sizes or even apply formulas onto them. These groups of cells are thus the fundamentals of most Operations in Excel. Excel has thus noted the great significance of ranges and therefore provides a name manager and name Box. These tools help users of Excel follow up names, addresses, and key operations involving ranges. Some of the operations performed on ranges include:

 

I.) Naming Ranges.

There are various ways we can name a Range. Naming a range helps us refer to it in Formulas and functions. It, therefore, becomes easier to keep track of named ranges rather than actual cell references. Any given range can be named according to one’s wish. However, the name chosen should not be greater than 255 characters in length. Another thing to note is that an underscore or period is used for compound names and hence no spaces should be left or made in the name assigned to a specific range. To name a range:

 

Method 1: Using the name Box

Procedure:

1. Open Excel.


2. Select your workbook.

3. Navigate to the worksheet you are working on.

4. Select a group of cells you wish to name. e.g. C4: C11. You can do this by pressing Shift+Arrow Key, to highlight the cells.

5. On the left side of the formula bar, Click inside the Name box C4.

6. Type your favorite name for the range While following the naming guidelines discussed above.

7. Press Enter key. You have now successfully named a range (a group of cells).

 

Method 2:

Alternatively: Using New Name Dialog

Procedure:

1. Select/highlight the group of cells that you wish to name

2. Navigate to the “Formulas 11” menu tab.

3. Click “Define name” inside the Defined name group. A new name Dialog Box will appear. The Dialog accepts: “Name”, “Scope”, “Comments “and ” refers to” as inputs.

4. Type a name in the Name Text Field.

5. Select the Scope from the drop-down list. The Scope refers to the extent or location to which Excel recognizes a given name. Each name should be unique within its own Scope. You can leave the scope to Workbook.

6. Add a description or comment in the “Comment section.”

7. Don’t touch the “reference to “since it contains the referencing of the cells you had selected.

8. Click OK.

 

Method 3:

Using Selection Dialog. This method simply uses column header labels as the names of the range.

Procedure:

1. Select the range of cells you want to work with.

2. Navigate to the formula tab.

3. Select ” Create from Selection” inside the Defined names group.

4. A create from selection Dialog appears.

5. Now you can select where your name should come from by ticking one of the checkboxes. e.g. Top row.

6. Press OK in the dialog when done.