Create a Named Range in Excel with openpyxl

A defined name turns 'Lookups'!$A$2:$A$40 into Regions. Formulas become readable, ranges can be repointed in one place, and dropdown validation stops breaking every time someone inserts a row. openpyxl creates them through the DefinedName class, and the two things worth understanding are the reference syntax and the scope. This guide, part of Working with Excel Formulas in Python, covers both, plus listing, updating and deleting names from Python.

Prerequisites

pip install openpyxl

The DefinedName class lives in openpyxl.workbook.defined_name, and the collections it goes into are wb.defined_names and ws.defined_names.

Create a workbook-wide name

Add the name to the workbook and any sheet can use it:

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

wb = Workbook()

lookups = wb.active
lookups.title = "Lookups"
lookups["A1"] = "Region"
for i, region in enumerate(["North", "South", "West", "Central"], start=2):
    lookups[f"A{i}"] = region
lookups["C1"] = "VAT rate"
lookups["C2"] = 0.20

last = lookups.max_row
wb.defined_names.add(DefinedName("Regions", attr_text=f"'Lookups'!$A$2:$A${last}"))
wb.defined_names.add(DefinedName("TaxRate", attr_text="'Lookups'!$C$2"))

report = wb.create_sheet("Report")
report["A1"] = "Net"
report["B1"] = 1000
report["A2"] = "VAT"
report["B2"] = "=B1*TaxRate"
report["A3"] = "Regions covered"
report["B3"] = "=COUNTA(Regions)"

wb.save("named_ranges.xlsx")
print(list(wb.defined_names))       # ['Regions', 'TaxRate']

The name is built from last, so it always covers the rows that exist. That is the practical reason to create names from Python rather than by hand: the range is generated from the data in the same run that wrote it.

Quoting the sheet name is only strictly required when the title contains a space or punctuation, but quoting it always is harmless and saves you from a title change breaking the reference later.

Create a sheet-local name

Adding the name to a worksheet limits its visibility to that sheet, so several tabs can each have their own Target or Rate:

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

targets = {"North": 120000, "South": 90000, "West": 45000}

wb = Workbook()
for region, target in targets.items():
    ws = wb.create_sheet(region)
    ws["A1"] = "Target"
    ws["B1"] = target
    ws["A2"] = "Actual"
    ws["B2"] = 0
    ws["A3"] = "Attainment"
    ws["B3"] = "=IF(Target=0,0,B2/Target)"
    ws["B3"].number_format = "0.0%"
    ws.defined_names.add(DefinedName("Target", attr_text=f"'{region}'!$B$1"))

del wb["Sheet"]
wb.save("scoped_targets.xlsx")

for name in wb.sheetnames:
    print(name, list(wb[name].defined_names))

Every sheet's B3 uses the identical formula string, and each resolves Target to its own cell. Without sheet scope you would need North_Target, South_Target and so on, and the formula would have to be generated per sheet — more code and more to get wrong.

The resolution order in the diagram is worth remembering when a value looks wrong: a sheet-local TaxRate shadows the workbook one for formulas on that sheet only, so two tabs can show different results from an identical formula.

Point a name at a whole table or a single column

Names are not limited to one column. A rectangular range works for VLOOKUP tables, and a single cell works for an input:

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

wb = Workbook()
prices = wb.active
prices.title = "Prices"
prices.append(["SKU", "Unit_Price"])
for sku, price in (("A-100", 19.99), ("B-200", 49.50), ("C-300", 12.25)):
    prices.append([sku, price])

last = prices.max_row
wb.defined_names.add(DefinedName("PriceTable", attr_text=f"'Prices'!$A$2:$B${last}"))

orders = wb.create_sheet("Orders")
orders.append(["SKU", "Quantity", "Unit_Price"])
orders.append(["A-100", 4])
orders["C2"] = '=IFERROR(VLOOKUP(A2,PriceTable,2,FALSE),"unpriced")'

wb.save("price_table.xlsx")

VLOOKUP(A2, PriceTable, 2, FALSE) reads far better than the same call with a raw range, and when the price list grows you regenerate one name instead of hunting formulas. The same name is what you would hand to a dropdown validation list so the choices stay in step with the lookup table.

List, replace and delete names

The collections behave like dictionaries, which makes maintenance straightforward:

from openpyxl import load_workbook
from openpyxl.workbook.defined_name import DefinedName

wb = load_workbook("named_ranges.xlsx")

# Inspect what is there
for name, defn in wb.defined_names.items():
    print(f"{name:12} -> {defn.attr_text}")

# Repoint a name at a longer range (delete, then add)
if "Regions" in wb.defined_names:
    del wb.defined_names["Regions"]
wb.defined_names.add(DefinedName("Regions", attr_text="'Lookups'!$A$2:$A$500"))

# Remove one that is no longer used
wb.defined_names.pop("TaxRate", None)

wb.save("named_ranges_updated.xlsx")

Printing the inventory before you change anything is worth the two lines it costs. A workbook that has been round-tripped through several teams often carries names nobody recognises, including ones Excel created itself: Print_Area and Print_Titles are ordinary defined names under the surface, and a loop that blindly deletes everything will quietly change how the file prints.

There is no rename: the name is the key, so a rename is a delete plus an add — and any formula that used the old name will show #NAME? until you rewrite it. If you generate both the names and the formulas in the same script, keep the name strings in one constant at the top so the two never drift apart.

Naming rules that Excel enforces

openpyxl does not validate these rules when you add the name — the file is written happily and Excel reports a problem on open. A one-line regular expression check before adding is cheap insurance in a generator that builds names from column headers.

Generate names from your data model

When the sheet layout comes from a Python structure, derive the names from the same structure so the two cannot disagree:

import re
from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName

NAME_OK = re.compile(r"^[A-Za-z_][A-Za-z0-9_.]{0,254}$")

inputs = {"VAT rate": 0.20, "Discount threshold": 500, "FX EURGBP": 0.86}

wb = Workbook()
ws = wb.active
ws.title = "Inputs"
ws.append(["Input", "Value"])

for label, value in inputs.items():
    ws.append([label, value])
    row = ws.max_row
    name = re.sub(r"[^A-Za-z0-9_]", "_", label)          # spaces are illegal in names
    if not NAME_OK.match(name):
        raise ValueError(f"cannot build a valid name from {label!r}")
    wb.defined_names.add(DefinedName(name, attr_text=f"'Inputs'!$B${row}"))

print(list(wb.defined_names))     # ['VAT_rate', 'Discount_threshold', 'FX_EURGBP']
wb.save("inputs_named.xlsx")

Every input cell now has a name that a formula elsewhere can use, and the validation stops an awkward label — one starting with a digit, say — from producing a workbook Excel offers to repair.

Common pitfalls and gotchas

Performance and scale notes

Defined names cost nothing at runtime — they are a handful of entries in the workbook's XML, and Excel resolves them once per formula evaluation. The scale issue is a different one: a workbook that accumulates hundreds of stale names (a common outcome of years of copy-pasting sheets) opens slowly and shows a cluttered Name Manager. If your script regenerates a workbook from a template, it is worth dropping names whose target sheet no longer exists as part of the run.

Conclusion

Creating a named range is one DefinedName added to either wb.defined_names or ws.defined_names, and the two decisions that matter are made before that call: use an absolute reference so the target cannot drift, and pick workbook scope for shared inputs versus sheet scope for values each tab owns. Generate the range end from the data you just wrote, keep name strings in one place, and clean up names whose sheets have gone.

Frequently asked questions

Does the reference need dollar signs? Yes, in practice. A defined name should point at an absolute reference such as 'Rates'!$B$1, otherwise the target shifts relative to whichever cell is active when the name is resolved.

What characters are allowed in a name? Names start with a letter or underscore and may contain letters, digits, underscores and periods. Spaces are not allowed, and a name must not look like a cell reference such as A1 or Q3.

How do I make a name that only one sheet can see? Add it to that worksheet's collection with ws.defined_names.add(...) instead of wb.defined_names.add(...). Several sheets can then reuse the same name for their own cells.

Can I delete or rename a defined name? Delete with del wb.defined_names["Name"] and add a fresh one. There is no rename operation — the name is the dictionary key.

Related

Up to the parent guide:

• Working with Excel Formulas in Python — where named ranges fit in the formula workflow.

Related guides:

• Write a Formula to an Excel Cell with openpyxl — the formulas that consume these names.
• Add SUM and AVERAGE Formulas with Python — aggregate over a named range.
• Add Dropdown Data Validation to Excel with openpyxl — a list that reads from a name.
• Using openpyxl for Excel File Manipulation — workbooks, sheets and cells in depth.