Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Collapse]WPF Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Common Concepts
 [Collapse]Controls and Libraries
  [Expand]Windows and Utility Controls
   Dialogs, Notifications and Panels
   Reporting
  [Expand]Data Grid
  [Expand]Ribbon, Bars and Menu
  [Expand]Charts Suite
  [Expand]Pivot Grid
  [Expand]Scheduler
  [Expand]Scheduler (new)
  [Collapse]Spreadsheet
    Product Structure
   [Expand]Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
   [Expand]Pivot Table Overview
   [Expand]Charting
    Printing
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
    Themes and Templates
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Tree List
  [Expand]Gauge Controls
  [Expand]Map Control
  [Expand]Layout Management
  [Expand]Windows Modern UI
  [Expand]Printing-Exporting
  [Expand]Data Editors
  [Expand]Navigation Controls
  [Expand]Spell Checker
  [Expand]Property Grid
  [Expand]PDF Viewer
  [Expand]TreeMap Control
  [Expand]Diagram Control
 [Expand]Localization
  Redistribution and Deployment
  Get More Help
 [Expand]API Reference
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Defined Names

This document introduces the Defined Name concept and describes how to manage defined names.

Expanded Defined Name Overview

To make is easier to understand information contained in a worksheet and refer to individual cells, ranges of cells, formulas and constant values, you can use defined names. A defined name is an object that implements the DefinedName interface and contains the following information.

Property

Description

Name

DefinedName.Name

Indicates an individual cell, range of cells, formula or constant. Usually, a name explains the purpose of an object to which this name refers, making it easier to find and use this object.

When specifying a name, take into account special rules.

Refers To

DefinedName.RefersTo

A string specifying a reference to a cell or cell range, formula or constant associated with the defined name. For example:

"=Sheet1!$D$20" - refers to the D20 cell located on the Sheet1 worksheet;

"=Sheet1!$A$1:$C$10" - refers to the A1:C10 range of cells located on the Sheet1 worksheet;

"=SUM(Sheet1!$B$1:$B$10)" - refers to the formula calculating the sum of values contained in the B1:B10 range of cells located on the Sheet1 worksheet;

"=10.5" - refers to the constant value.

By default, defined names use absolute cell references including worksheet names.

Comment

DefinedName.Comment

An explanation or additional information accompanying the defined name.

Note

Comment length cannot exceed 255 characters.

Expanded Defined Name Scope

Each defined name has a scope - an area (individual worksheet or entire workbook) where a name is recognized and can be used without qualification. For example, a defined name (cellName) whose scope is the first worksheet of a workbook (Sheet1) is recognized without qualification in this worksheet only (for example, =5+cellName). To use this defined name in other worksheets, precede it with the name of the worksheet to which the defined name is scoped (for example, "=5+Sheet1!cellName"). If the scope of a defined name (cellName_global) is an entire workbook, this name is recognized in any worksheet of this workbook (for example, "=5+cellName_global").

Each worksheet contained in a workbook and workbook itself has its own collection of defined names (DefinedNameCollection) that can be accessed via the Worksheet.DefinedNames or IWorkbook.DefinedNames property, respectively. Each name must be unique in its scope (use the DefinedNameCollection.Contains method to determine whether or not a specific name already exists in the collection). However, the same name can be used in different scopes.

When you use the defined name without preceding it by a worksheet name, this name is searched within the DefinedNameCollection collection of the worksheet where this name is used. Then, if the name is not found in this worksheet, it will be searched for in the workbook's collection of defined names. To use a global defined name explicitly, precede it with the workbook name (for example, "=5+WorkbookName.xlsx!cellName").

If the defined name is not found, the cell that uses this name displays the #NAME? error.

Expanded Syntax Rules for Names

When creating and modifying defined names, follow the rulers below.

  • Start a name with a letter, the "_" underscore symbol or the "\" backslash. The rest of characters in a name can be letters, numbers, periods and underscore symbols.

    Note that a name cannot consist only of one of the following letters: "C", "c", "R", or "r".

  • A name cannot be the same as a cell reference (for example, "A1", "$M$15", etc.).
  • A name cannot contain spaces (use underscore symbols and periods instead).
  • A name cannot be an empty string.
  • A name length cannot exceed 255 characters.
  • Uppercase and lowercase letters are interpreted as the same. For example, you are not allowed to create the Products and PRODUCTS names in one scope.

Expanded Create Defined Names

You can create defined names via the Range.Name property, or Worksheet.DefinedNames.Add and IWorkbook.DefinedNames.Add methods.

  • Range.Name

    Access an object that specifies a cell or cell range to be named and set its Range.Name property. The corresponding DefinedName object is automatically created and added to the Worksheet.DefinedNames collection of the worksheet that contains the named cell or cell range. Thus, this worksheet is a scope of the created name. The DefinedName.RefersTo property is automatically set to the absolute cell reference (including the worksheet name).

  • Worksheet.DefinedNames.Add

    This method allows you to create a defined name whose scope is the specified worksheet and associate this name with an individual cell, range of cells, formula or constant value.

  • IWorkbook.DefinedNames.Add

    This method allows you to create a defined name whose scope is the entire workbook and associate this name with an individual cell, range of cells, formula or constant value.

Expanded Access and Change Defined Names

All defined names of a spreadsheet document are stored in the DefinedNameCollection collections. Use the Worksheet.DefinedNames property to access the collection of defined names whose scope is a specific worksheet, or the IWorkbook.DefinedNames property to get defined names whose scope is an entire workbook. To obtain an individual defined name from the required collection, use properties and methods of the corresponding DefinedNameCollection object. For example, you can get a defined name by its index in the collection or by its name (DefinedNameCollection.GetDefinedName).

An individual defined name is specified by the DefinedName object. Use this object's properties (DefinedName.Name, DefinedName.RefersTo and DefinedName.Comment) to modify the corresponding defined name as required.

Note

After you change an existing defined name, all instances of this name in a workbook will be also changed. For example, if you change DefinedName.Name, all cells using the old name will display the #NAME? error.

Expanded Delete Defined Names

To delete an existing defined name, use the DefinedNameCollection.Remove method.

Note

After you delete a name, all cells using that name will display the #NAME? error. After you delete a named cell or range of cells, all cells using defined names that refer to the deleted cell or cell range will display the #REF! error.

How would you rate this topic?​​​​​​​