Defining and changing a named range in Excel

Content
  1. Give descriptive names to specific cells or ranges of cells
  2. Define and manage names with the Name field
  3. Naming Rules and Restrictions
  4. Defining and managing names with the Name Manager
  5. Delete or edit names
  6. Filter name
  7. Defined names and range in Excel
  8. Local worksheet level area
  9. Global reach at the workbook level
  10. Territory Conflicts and Territory Priority

Give descriptive names to specific cells or ranges of cells

named range range name or specific name refer to the same object in Excel; is a descriptive name, for example Jan_Sales or June_Precip A anchored to a specific cell or range of cells in a worksheet or workbook. Named ranges make it easier to use and identify data when creating charts and in formulas such as:

 = СУММ (Jan_Sales) 
 = June_Precip + July_Precip + Aug_Precip 

Because a named range doesn’t change when a formula is copied to other cells, it provides an alternative to using absolute cell references in formulas. There are three ways to define a name in Excel: using the name field, the new name box, or the name manager. This article provides instructions for the name field and name management.

These instructions apply to Excel 2019, 2016, 2013, 2010, 2007, and Excel for Office 365.

Define and manage names with the Name field

One of, and perhaps the easiest, way to define names is to use Name field located upstairs column A on the sheet. You can use this method to generate unique names that will be recognized by each sheet in the workbook. Create a name using the name field as shown in the image above:

  1. highlight desired range of cells on the worksheet.

  2. Enter desired name for this range in Window name For instance Jan_Sales

  3. Press key Enter on keyboard.

  4. The name is displayed in name field

The name is also displayed in the field Name if the same range of cells is selected on the worksheet. It also occurs in Name Manager

Naming Rules and Restrictions

Syntax rules to remember when creating or editing range names:

  • The name cannot contain spaces.

  • The first character of the name must be a letter, an underscore, or a backslash.

  • Other characters can only be letters, numbers, periods, or underscores.

  • The maximum name length is 255 characters.

  • Uppercase and lowercase letters are indistinguishable from Excel, so in Excel Jan_Sales and jan_sales considered the same name.

  • Cell reference cannot be used when names like A25 or R1C4

Defining and managing names with the Name Manager

The second method of defining names is to use the dialog New name † this dialog opens with the option Define name is located in the middle of the tab formulas on the adhesive tape † The New Name dialog makes it easy to define names with a worksheet-level range.

To create a name using the New Name dialog:

  1. highlight desired range of cells on the worksheet.

  2. Press Formulas Tab on the adhesive tape

  3. Press the button Define name to open the dialog New name

  4. In the dialog you need to specify: Name Region and Range

  5. When you’re done, click Okay to return to the sheet.

  6. The name is displayed in name field when a specific range is selected.

The name manager can be used to define existing names as well as to manage them; it is located next to the “Define name” option on the tab formulas on the adhesive tape

When defining a name in Name Manager dialog opens New name described above. The full list of steps is as follows:

  1. Click Formulas Tab on the adhesive tape

  2. Click the icon Name Manager in the middle of the ribbon to open Name Manager

  3. V Name Manager Press on the button To create to open the dialog New name

  4. In this dialog you need to define Name Region and Range

  5. Click Okay return to Name Manager where the new name appears in the window.

  6. Click close to to return to the sheet.

Delete or edit names

When the name manager is open:

  1. In the name list window click once to the name to be deleted or edited.

  2. To remove a name, click the button remove above the list box.

  3. To change the name, click the button Change to open the dialog Change name

In the Edit Name dialog box, you can edit the selected name, add comments about the name, or change an existing range reference.

The scope of an existing name cannot be changed using the edit options. To change the realm, delete the name and redefine it with the correct realm.

Filter name

Knob Filter v Name Manager allows you to easily:

  • Find names with errors, for example an invalid range.

  • Define the range of the name – be it worksheet level or book level.

  • Sort and filter listed names – specific (range) names or table names.

The filtered list is displayed in the drop-down list in Name Manager

Defined names and range in Excel

All names are region , which points to places where a specific name is recognized in Excel. The name range can be for individual worksheets ( local area ) or for the entire workbook ( worldwide reach † The name must be unique within the scope, but the same name can be used in different scopes.

The default range for new names is the global workbook level. Once defined, the scope of a name cannot be changed easily. To change the scope of a name, delete the name in the name manager and redefine it with the correct scope.

Local worksheet level area

A sheet range name is only valid for the sheet for which it is defined. If the name Total sales has scope page 1 workbook, Excel does not recognize the name on sheet 2 sheet 3 or another sheet in the workbook. This allows the same name to be defined for use on multiple worksheets – as long as the range for each name is limited to the specific worksheet.

You can use the same name for different sheets to maintain continuity between sheets and to ensure that formulas that use the name Total sales always refer to the same range of cells in multiple worksheets in the same workbook.

To distinguish identical names with different ranges in formulas, precede the name with the sheet name, for example:

 Лист1! Total_sales 

Or

 Лист2! Total_sales 

Names made with block name always has a global scope at the workbook level unless the sheet name and range name are entered in the name field when defining the name.

Strong examples>:

  • Name: Jan_Sales, Scope – global workbook level
  • Name: Page 1! Jan_Sales, Scope – local sheet level

Global reach at the workbook level

A name defined with a workbook-level scope is recognized for all worksheets in that workbook. Therefore, a workbook-level name can only be used once in a workbook, unlike the sheet-level names discussed above.

However, the workbook-level range name is not recognized by another workbook, so global-level names may be repeated in different Excel files. For example, if the name Jan_Sales has a global scope, the same name can be used in different workbooks called 2012_Revenue 2013_Revenue and 2014_Revenue .

Territory Conflicts and Territory Priority

You can use the same name at both the local worksheet level and the workbook level, because the scope of the two elements will be different. However, this situation will cause a conflict when the name is used.

To resolve such conflicts in Excel, names defined at the local level of the worksheet take precedence over the global level of the workbook. In such a situation, the sheet level name 2014_Revenue is used instead of name at book level 2014_Revenue

To override the priority rule, use the workbook level name along with the specific sheet level name, such as:

 2014_Revenue!Лист1 

The only exception to the precedence priority is the name of the local worksheet level, which is within the range page 1 workbook. Areas related to: page 1 a workbook cannot be overwritten by global-level names.

Leave a Reply

Your email address will not be published.