Defined Names and Named Ranges in Excel

The defined names in Excel files are descriptive or human-readable names that reference a single cell, range of cells, constant values, or formulas. These names can be used to define cell references without using the columns and rows, and thus often make it easier to understand the purpose of that cell or cells.

GemBox.Spreadsheet represents them with ExcelWorkbook.DefinedNames and ExcelWorksheet.NamedRanges collections. The difference is that NamedRanges is a collection of NamedRange objects which are filtered DefinedName objects that reference cells in a global scope (ExcelWorkbook) or a current local scope (ExcelWorksheet).

The following example shows how you can create and retrieve defined names or named ranges in an Excel file from C# and VB.NET.

Adding and retrieving defined names or named ranges in Excel file from C# and VB.NET
Screenshot of defined names or named ranges
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Names");

        // Create a defined name for a constant value with a global scope.
        workbook.DefinedNames.AddDefinedName("Tax", "0.2", -1);

        // Retrieve defined name.
        DefinedName taxConstant = workbook.DefinedNames["Tax"];

        // Use defined name with formula.
        worksheet.Cells["A1"].Value = taxConstant.Name;
        worksheet.Cells["B1"].Formula = "=Tax";
        worksheet.Cells["B1"].Style.NumberFormat = "0%";

        // Create a named range for cell "A3" with a local scope.
        worksheet.Cells["A2"].Value = "Price";
        worksheet.Cells["A3"].Value = 240;
        worksheet.Cells["A4"].Value = 180;
        worksheet.Cells["A5"].Value = 210;
        worksheet.NamedRanges.Add("Prices", worksheet.Cells.GetSubrange("A3"));

        // Retrieve named range.
        NamedRange priceRange = worksheet.NamedRanges["Prices"];

        // Modify named range's cell reference to cells "A3:A5".
        priceRange.Range = worksheet.Cells.GetSubrange("A3:A5");

        // Use named range with formulas.
        worksheet.Cells["B2"].Value = "Total";
        worksheet.Cells["B3"].Formula = "=Prices * (Tax + 1)";
        worksheet.Cells["B4"].Formula = "=Prices * (Tax + 1)";
        worksheet.Cells["B5"].Formula = "=Prices * (Tax + 1)";

        workbook.Save("Defined Names.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Names")

        ' Create a defined name for a constant value with a global scope.
        workbook.DefinedNames.AddDefinedName("Tax", "0.2", -1)

        ' Retrieve defined name.
        Dim taxConstant As DefinedName = workbook.DefinedNames("Tax")

        ' Use defined name with formula.
        worksheet.Cells("A1").Value = taxConstant.Name
        worksheet.Cells("B1").Formula = "=Tax"
        worksheet.Cells("B1").Style.NumberFormat = "0%"

        ' Create a named range for cell "A3" with a local scope.
        worksheet.Cells("A2").Value = "Price"
        worksheet.Cells("A3").Value = 240
        worksheet.Cells("A4").Value = 180
        worksheet.Cells("A5").Value = 210
        worksheet.NamedRanges.Add("Prices", worksheet.Cells.GetSubrange("A3"))

        ' Retrieve named range.
        Dim priceRange As NamedRange = worksheet.NamedRanges("Prices")

        ' Modify named range's cell reference to cells "A3:A5".
        priceRange.Range = worksheet.Cells.GetSubrange("A3:A5")

        ' Use named range with formulas.
        worksheet.Cells("B2").Value = "Total"
        worksheet.Cells("B3").Formula = "=Prices * (Tax + 1)"
        worksheet.Cells("B4").Formula = "=Prices * (Tax + 1)"
        worksheet.Cells("B5").Formula = "=Prices * (Tax + 1)"

        workbook.Save("Defined Names.%OutputFileType%")

    End Sub
End Module

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.