Creating and Managing Named Ranges in Excel Using Python

When working with complex Excel worksheets, referencing data regions by raw cell addresses (such as A1:D10) is a common yet error-prone practice. As formulas and references multiply, keeping track of what each region represents becomes nearly impossible. Named ranges are designed to solve exactly this problem — they let you assign a meaningful name to a cell range, making formulas, data references, and code logic significantly clearer.

This article demonstrates how to create, query, modify, format, use in formulas, and delete named ranges in Excel using Python.

Prerequisites

This article uses the Spire.XLS for Python library to work with Excel files. Install it via pip:

pip install Spire.XLS

Then import the required modules in your Python script:

from spire.xls import *
from spire.xls.common import *

Creating a Named Range

The core idea behind a named range is to bind a cell range to a descriptive name. To create one, call the NameRanges.Add() method to add a new named range object, then point its RefersToRange property to the desired cell range.

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")
sheet = workbook.Worksheets[0]

# Create a named range and bind it to a specific cell range
named_range = workbook.NameRanges.Add("SalesData")
named_range.RefersToRange = sheet.Range["A1:E20"]

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Here, NameRanges.Add() returns an INamedRange object. By setting its RefersToRange property, the association between the name and the range is established. From that point on, you can use the name SalesData in Excel formulas or code instead of the raw address A1:E20.

Workbook-Level and Worksheet-Level Named Ranges

Excel named ranges have two possible scopes: global (workbook-level) and local (worksheet-level). A workbook-level named range is accessible throughout the entire workbook, while a worksheet-level named range is only valid within the sheet where it is defined.

Workbook-level named ranges are created via workbook.NameRanges:

named_range = workbook.NameRanges.Add("GlobalRange")
named_range.RefersToRange = sheet.Range["A1:D10"]

Worksheet-level named ranges are created via sheet.Names:

named_range = sheet.Names.Add("LocalRange")
named_range.RefersToRange = sheet.Range["A1:D19"]

Worksheet-level named ranges are especially useful when multiple sheets each need their own independent range with the same name.

Querying Named Ranges

Retrieving All Named Ranges

Iterate through the NameRanges collection to list every named range in the workbook:

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

for name_range in workbook.NameRanges:
    print(name_range.Name)

workbook.Dispose()

Retrieving a Specific Named Range

You can access a specific named range by index or by name:

# Retrieve by index
range_by_index = workbook.NameRanges[1]

# Retrieve by name
range_by_name = workbook.NameRanges["SalesData"]

Getting the Address of a Named Range

Use the RefersToRange property to obtain the cell range and its address:

named_range = workbook.NameRanges[0]
cell_range = named_range.RefersToRange
address = cell_range.RangeAddress

print(f"The address of named range '{named_range.Name}' is {address}")

Looking Up a Named Range by Cell Range

If you have a cell range and want to check whether it has been defined as a named range, use the GetNamedRange() method:

sheet = workbook.Worksheets[0]
cell_range = sheet.Range["A2:D2"]
result = cell_range.GetNamedRange()

if result is not None:
    print(f"Named range for this range: {result.Name}")

This is particularly handy for verifying whether a given range already has a named range binding.

Renaming a Named Range

You can rename an existing named range by simply updating its Name property:

workbook.NameRanges[0].Name = "UpdatedRangeName"

After renaming, all formulas that reference the old name are automatically updated — consistent with Excel's native behavior.

Formatting a Named Range

Once you retrieve a named range, you can apply formatting to its underlying cell range. This is convenient when you need to highlight a specific data region:

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

named_range = workbook.NameRanges[0]
cell_range = named_range.RefersToRange

# Set the background color
cell_range.Style.Color = Color.get_Yellow()

# Set bold font
cell_range.Style.Font.IsBold = True

workbook.SaveToFile("formatted_output.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The range object obtained through RefersToRange behaves exactly like any regular cell range, so you can apply any standard style settings to it.

Merging Cells in a Named Range

The cell range covered by a named range also supports merge operations:

named_range = workbook.NameRanges[0]
cell_range = named_range.RefersToRange

# Merge cells
cell_range.Merge()

The merge operation combines all cells in the range into a single cell, commonly used to create header rows that span multiple columns.

Using Named Ranges in Formulas

One of the most valuable uses of named ranges is replacing hard-coded cell addresses in formulas. The following example shows how to define a named range for a region and then reference it directly in a SUM formula:

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")
sheet = workbook.Worksheets[0]

# Create a named range
named_range = workbook.NameRanges.Add("ScoreRange")
named_range.RefersToRange = sheet.Range["B10:B12"]

# Reference the named range in a formula
sheet.Range["B13"].Formula = "=SUM(ScoreRange)"

# Populate data
sheet.Range["B10"].Value2 = Int32(10)
sheet.Range["B11"].Value2 = Int32(20)
sheet.Range["B12"].Value2 = Int32(30)

workbook.SaveToFile("formula_output.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Using named ranges significantly improves formula readability. When the data region changes, you only need to update the RefersToRange of the named range — there is no need to modify each formula's address references one by one.

Deleting Named Ranges

Named ranges can be removed by index or by name:

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

# Remove by index
workbook.NameRanges.RemoveAt(0)

# Remove by name
workbook.NameRanges.Remove("SalesData")

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Keep in mind that after deleting a named range, any formulas referencing that name will return errors. Before removing a named range, it is a good practice to check whether any formulas depend on it.

Practical Tips

• Naming conventions: Named range names cannot contain spaces, cannot start with a digit, and cannot conflict with cell addresses (such as A1). Use camelCase or underscore-separated names, such as SalesData or sales_data.
• Scope selection: If a named range is only used within a single worksheet, prefer creating it as a worksheet-level named range to avoid name conflicts.
• Dynamic updates: When the number of rows in a data region changes frequently, you can dynamically update the named range's coverage by reassigning its RefersToRange property in code.

Conclusion

Named ranges are a fundamental Excel feature that enhances formula readability and data management consistency. With Python, you can automate the creation, querying, modification, and cleanup of named ranges — making them ideal for batch-processing Excel files or building automated reporting systems.

This article covered the key operations: creating workbook-level and worksheet-level named ranges, querying by index and by name, retrieving address information, renaming, formatting, merging cells, referencing in formulas, and deleting. In real-world projects, you can combine these operations as needed to build more flexible and maintainable Excel data processing workflows.