Named ranges¶

Named ranges, called defined names in Excel, let you give a name to a range, a formula or a value. You can then use the name instead of the address, formula or value, for example in other formulas. In EPPlus, names are added via the Names collection, either on the workbook or on a worksheet. EPPlus 8.1 introduced new methods that make working with names easier.

Workbook and worksheet names¶

A name added to Workbook.Names can be used in the whole workbook. A name added to ExcelWorksheet.Names is scoped to that worksheet.

This example adds a named range to a worksheet:

var myDefinedRange = worksheet.Names.Add("MyDefinedRange", worksheet.Cells["A4:A10"]);

Dim myDefinedRange = worksheet.Names.Add("MyDefinedRange", worksheet.Cells("A4:A10"))

This example adds a name that contains a formula to the workbook:

var myDefinedFormula = package.Workbook.Names.Add("MyDefinedFormula", "SUM(10,7)");

Dim myDefinedFormula = package.Workbook.Names.Add("MyDefinedFormula", "SUM(10,7)")

As with cell formulas in EPPlus, formulas in names use comma (,) to separate arguments and are written without the leading equal sign.

LAMBDA functions in names¶

A name can also contain a LAMBDA function. The name can then be called like a function in other formulas in the workbook, which is a way to create reusable custom functions without code. See LAMBDA in defined names for examples.

New methods in EPPlus 8.1¶

Adding names¶

Validating cell references in formulas¶

The static property ExcelNamedRange.ValidateCellAddressInFormulas controls how cell references in the formulas of names are handled. When it is set to true, EPPlus validates the cell references, and references without a worksheet name are changed to include the worksheet. For example, SUM(C2) is converted to SUM('Sheet 1'!$C$2).

ExcelNamedRange.ValidateCellAddressInFormulas = true;

ExcelNamedRange.ValidateCellAddressInFormulas = True

Changing the content of a name¶

The SetValue(object value), SetFormula(string formula) and SetRange(ExcelRangeBase range) methods replace the previous content of the name. For example, if a name contains a formula and you set it to a range, the formula is removed.

Reading the content of a name¶

How you read the content of a name depends on what the name contains:

• A range: use the name as any other range, with the same methods and properties.
• A formula: use the GetFormula() method or the Formula property.
• Anything else: use the GetValue() method, or the Value or Text property.

Using a method or property that does not match the content of the name may return null.

Copying and moving names¶

Copy copies the name to the specified workbook or worksheet:

var copiedName = myNamedRange.Copy("CopiedRange", worksheet2);

Dim copiedName = myNamedRange.Copy("CopiedRange", worksheet2)

Move moves the name to the specified workbook or worksheet:

var movedName = myNamedRange.Move(worksheet2);

Dim movedName = myNamedRange.Move(worksheet2)

See also¶

• Addressing cells and ranges
• Formula calculation
• LAMBDA functions in defined names