Skip to main content

Defined Names

File Format Support (click to show)

Defined names have evolved over the decades, with new features added over time:

  • "English" refers to defined names with English letters and numbers (ASCII)
  • "Unicode" refers to defined names with non-English characters.
  • "Comment" refers to comments that can be attached to defined names.
FormatsEnglishUnicodeComment
XLSX / XLSM
XLSB
XLS
XLML
SYLK
ODS / FODS / UOS

X (✕) marks features that are not supported by the file formats. There is no way to specify a Unicode defined name in the SYLK format.

wb.Workbook.Names is an array of defined name objects which have the keys:

KeyName in appDescription
Sheet"Scope"Sheet Index (0 = first sheet) or null (Workbook)
Name"Name"Case-sensitive name. Standard rules apply
Ref"Refers To"A1-Style Reference ("Sheet1!$A$1:$D$20")
Comment"Comment"Comment (for supported file formats)

Parsers do not always create the Names structure. Parsing and writing code should test for the existence of the defined names array before use:

/* ensure the workbook structure exists */
if(!wb.Workbook) wb.Workbook = {};
if(!wb.Workbook.Names) wb.Workbook.Names = [];

/* add a new defined name */
wb.Workbook.Names.push({ Name: "MyData", Ref: "Sheet1!$A$1:$A$2" });

Ranges

Defined name references in formulae are internally shifted to the cell address. For example, given the defined name

{ Name: "MyData", Ref: "Sheet1!A1:A2" } // no $ means relative reference

If D4 is set to =SUM(MyData):

ws["D4"].f = "SUM(MyData)";

Spreadsheet software will translate the defined name range down to the cell. Excel will try to calculate SUM(D4:D5) and assign to cell D4. This will elicit a circular reference error.

The recommended approach is to fix the rows and columns of the reference:

{ Name: "MyData", Ref: "Sheet1!$A$1:$A$2" } // absolute reference

Scoped Defined Names

Excel allows two sheet-scoped defined names to share the same name. However, a sheet-scoped name cannot collide with a workbook-scope name. Workbook writers may not enforce this constraint.

The following snippet creates a worksheet-level defined name "Global" and a local defined name "Local" with distinct values for first and second sheets:

/* "Global" workbook-level -> Sheet1 A1:A2 */
wb.Workbook.Names.push({ Name: "Global", Ref: "Sheet1!$A$1:$A$2" });

/* "Local" scoped to the first worksheet -> Sheet1 B1:B2 */
wb.Workbook.Names.push({ Name: "Local", Ref: "Sheet1!$B$1:$B$2", Sheet: 0 });

/* "Local" scoped to the second worksheet -> Sheet1 C1:C2 */
wb.Workbook.Names.push({ Name: "Local", Ref: "Sheet1!$C$1:$C$2", Sheet: 1 });

Live Demo

The following example creates 3 defined names:

  • "Global" is a workbook-level name that references Sheet1!$A$1:$A$2
  • "Local" in the first worksheet references Sheet1!$B$1:$B$2
  • "Local" in the second worksheet references Sheet1!$C$1:$C$2

Both worksheets include formulae referencing "Local" and "Global". Since the referenced ranges are different, the expressions using "Local" will differ.

Result
Loading...
Live Editor