Working with Range Names

While doing Data Analysis, referring to various data will be more meaningful and easy if the reference is by Names rather than cell references – either a single cell or a range of cells. For example, if you are calculating Net Present Value based on a Discount Rate and a series of Cash Flows, the formula

Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

is more meaningful than

C10 = NPV (C2, C6:C8)

With Excel, you can create and use meaningful names to various parts of your data. The advantages of using range names include −

A meaningful Range name (such as Cash_Flows) is much easier to remember than a Range address (such as C6:C8).

Entering a name is less error prone than entering a cell or range address.

If you type a name incorrectly in a formula, Excel will display a #NAME? error.

You can quickly move to areas of your worksheet by using the defined names.

With Names, your formulas will be more understandable and easier to use. For example, a formula Net_Income = Gross_Income – Deductions is more intuitive than C40 = C20 – B18.

Creating formulas with range names is easier than with cell or range addresses. You can copy a cell or range name into a formula by using formula Autocomplete.

In this chapter, you will learn −

Syntax rules for names.

Creating names for cell references.

Creating names for constants.

Managing the names.

Scope of your defined names.

Editing names.

Filtering names.

Deleting names.

Applying names.

Using names in a formula.

Viewing names in a workbook.

Using paste names and paste pst.

Using names for range intersections.

Copying formulas with names.

Copying Name using Formula Autocomplete

Type the first letter of the name in the formula. A drop-down box appears with function names and range names. Select the required name. It is copied into your formula.

Range Name Syntax Rules

Excel has the following syntax rules for names −

You can use any combination of letters, numbers and the symbols - underscores, backslashes, and periods. Other symbols are not allowed.

A name can begin with a character, underscore or backslash.

A name cannot begin with a number (example - 1stQuarter) or resemble a cell address (example - QTR1).

If you prefer to use such names, precede the name with an underscore or a backslash (example - 1stQuarter, _QTR1).

Names cannot contain spaces. If you want to distinguish two words in a name, you can use underscore (example- Cash_Flows instead of Cash Flows)

Your defined names should not clash with Excel’s internally defined names, such as Print_Area, Print_Titles, Consopdate_Area, and Sheet_Title. If you define the same names, they will override the Excel’s internal names and you will not get any error message. However, it is advised not to do so.

Keep the names short but understandable, though you can use up to 255 characters

Creating Range Names

You can create Range Names in two ways −

Using the Name box.

Using the New Name dialog box.

Using the Selection dialog box.

Create a Range Name using the Name Box

To create a Range name, using the Name box that is to the left of formula bar is the fastest way. Follow the steps given below −

Step 1 − Select the range for which you want to define a Name.

Step 2 − Cpck on the Name box.

Step 3 − Type the name and press Enter to create the Name.

Create a Range Name using the New Name dialog box

You can also create Range Names using the New Name dialog box from Formulas tab.

Step 1 − Select the range for which you want to define a name.

Step 2 − Cpck the Formulas tab.

Step 3 − Cpck Define Name in the Defined Names group. The New Name dialog box appears.

Step 4 − Type the name in the box next to Name

Step 5 − Check that the range that is selected and displayed in the Refers box is correct. Cpck OK.

Create a Range Name using the Create Names from Selection dialog box

You can also create Range names using the Create Names from the Selection dialog box from Formulas tab, when you have Text values that are adjacent to your range.

Step 1 − Select the range for which you want to define a name along with the row / column that contains the name.

Step 2 − Cpck the Formulas tab.

Step 3 − Cpck Create from Selection in the Defined Names group. The Create Names from Selection dialog box appears.

Step 4 − Select top row as the Text appears in the top row of the selection.

Step 5 − Check the range that got selected and displayed in the box next to Refers to be correct. Cpck OK.

Now, you can find the largest value in the range with =Sum(Student Name), as shown below −

You can create names with multiple selection also. In the example given below, you can name the row of marks of each student with the student’s name.

Now, you can find the total marks for each student with =Sum (student name), as shown below.

Creating Names for Constants

Suppose you have a constant that will be used throughout your workbook. You can assign a name to it directly, without placing it in a cell.

In the example below, Savings Bank Interest Rate is set to 5%.

Cpck Define Name.

In the New Name dialog box, type Savings_Bank_Interest_Rate in the Name box.

In Scope, select Workbook.

In Refers to box, clear the contents and type 5%.

Cpck OK.

The Name Savings_Bank_Interest_Rate is set to a constant 5%. You can verify this in Name Manager. You can see that the value is set to 0.05 and in the Refers to =0.05 is placed.

Managing Names

An Excel Workbook can have any number of named cells and ranges. You can manage these names with the Name Manager.

Cpck the Formulas tab.

Cpck Name Manager in the Defined Names group. The Name Manager dialog box appears. All the names defined in the current workbook are displayed.

The List of Names are displayed with the defined Values, Cell Reference (including Sheet Name), Scope and Comment.

The Name Manager has the options to −

Define a New Name with the New Button.

Edit a Defined Name.

Delete a Defined Name.

Filter the Defined Names by Category.

Modify the Range of a Defined Name that it Refers to.

Scope of a Name

The Scope of a name by default is the workbook. You can find the Scope of a defined names from the pst of names under the Scope column in the Name Manager.

You can define the Scope of a New Name when you define the name using New Name dialog box. For example, you are defining the name Interest_Rate. Then you can see that the Scope of the New Name Interest_Rate is the Workbook.

Suppose you want the Scope of this interest rate restricted to this Worksheet only.

Step 1 − Cpck the down-arrow in the Scope Box. The available Scope options appear in the drop-down pst.

The Scope options include Workbook, and the sheet names in the workbook.

Step 2 − Cpck the current worksheet name, in this case NPV and cpck OK. You can define / find the sheet name in the worksheet tab.

Step 3 − To verify that Scope is worksheet, cpck Name Manager. In the Scope column, you will find NPV for Interest_Rate. This means you can use the Name Interest_Rate only in the Worksheet NPV, but not in the other Worksheets.

Note − Once you define the Scope of a Name, it cannot be modified later.

Deleting Names with Error Values

Sometimes, it may so happen that Name definition may have errors for various reasons. You can delete such names as follows −

Step 1 − Cpck Filter in the Name Manager dialog box.

The following filtering options appear −

Clear Filter

Names Scoped to Worksheet

Names Scoped to Workbook

Names with Errors

Names without Errors

Defined Names

Table Names

You can apply Filter to the defined Names by selecting one or more of these options.

Step 2 − Select Names with Errors. Names that contain error values will be displayed.

Step 3 − From the obtained pst of Names, select the ones you want to delete and cpck Delete.

You will get a message, confirming delete. Cpck OK.

Editing Names

You can use the Edit option in the Name Manager dialog box to −

Change the Name.

Modify the Refers to range

Edit the Comment in a Name.

Change the Name

Step 1 − Cpck the cell containing the function Large.

You can see, two more values are added in the array, but are not included in the function as they are not part of Array1.

Step 2 − Cpck the Name you want to edit in the Name Manager dialog box. In this case, Array1.

Step 3 − Cpck Edit. The Edit Name dialog box appears.

Step 4 − Change the Name by typing the new name that you want in the Name Box.

Step 5 − Cpck the Range button to the right of Refers to Box and include the new cell references.

Step 6 − Add a Comment (Optional)

Notice that Scope is deactive and hence cannot be changed.

Cpck OK. You will observe the changes made.

Applying Names

Consider the following example −

As you observe, names are not defined and used in PMT function. If you place this function somewhere else in the worksheet, you also need to remember where exactly the parameter values are. You know that using names is a better option.

In this case, the function is already defined with cell references that do not have names. You can still define names and apply them.

Step 1 − Using Create from Selection, define the names.

Step 2 − Select the cell containing the formula. Cpck next to Define Name in the Defined Names group on the Formulas tab. From the drop-down pst, cpck Apply Names.

Step 3 − The Apply Names dialog box appears. Select the Names that you want to Apply and cpck OK.

The selected names will be appped to the selected cells.

You can also Apply Names to an entire worksheet, by selecting the worksheet and repeating the above steps.

Using Names in a Formula

You can use a Name in a Formula in the following ways −

Typing the Name if you remember it, or

Typing first one or two letters and using the Excel Formula Autocomplete feature.

Cpcking Use in Formula in the Defined Names group on the Formulas tab.

Select the required Name from the drop-down pst of defined names.

Double-cpck on that name.

Using the Paste Name dialog box.

Select the Paste Names option from the drop-down pst of defined names. The Paste Name dialog box appears.

Select the Name in the Paste Names dialog box and double-cpck it.

Viewing Names in a Workbook

You can get all the Names in your workbook along with their References and Save them or Print them.

Cpck an empty Cell where you want to copy the Names in your workbook.

Cpck Use in Formula in the Defined Names group.

Cpck Paste Names from the drop-down pst.

Cpck Paste List in the Paste Name dialog box that appears.

The pst of names and their corresponding references are copied at the specified location on your worksheet as shown in the screen shot given below −

Using Names for Range Intersections

Range Intersections are those inspanidual cells that have two Ranges in common.

For example, in the data given below, the Range B6:F6 and the Range C3:C8 have Cell C6 in common, which actually represents the marks scored by the student Kodeda, Adam in Exam 1.

You can make this more meaningful with the Range Names.

Create Names with Create from Selection for both Students and Exams.

Your Names will look as follows −

Type =Kodeda_Adam Exam_1 in B11.

Here, you are using the Range Intersection operation, space between the two ranges.

This will display marks of Kodeda, Adam in Exam 1, that are given in Cell C6.

Copying Formulas with Names

You can copy a formula with names by Copyand Paste within the same worksheet.

You can also copy a formula with names to a different worksheet by copy and paste, provided all the names in the formula have workbook as Scope. Otherwise, you will get a #VALUE error.