Read a Named Range into pandas

Spreadsheet builders often name the block of cells that matters — SalesData, RateTable, Assumptions — so formulas can say =VLOOKUP(A2, RateTable, 2, FALSE) instead of =VLOOKUP(A2, Rates!$B$4:$C$19, 2, FALSE). The name is also the most stable way for a script to find that block: rows get inserted above it, the sheet gets renamed, the table moves two columns right, and the name follows. pandas.read_excel has no range_name= argument, though, so reading a named range takes one step with openpyxl to resolve the name to a sheet and a cell reference, then a few lines to turn those cells into a DataFrame. This page is part of Reading Excel Files with pandas.

Prerequisites

pip install pandas openpyxl

The examples use pricing.xlsx. Its Inputs sheet has a rate table in A4:C8 named RateTable and a single-cell name TaxRate; two quarterly sheets each carry a sheet-scoped name Totals. A few awkward names are included on purpose: one covering two separate areas, one whose target was deleted, and one defined by an OFFSET formula.

Step 1 — List the names in the workbook

Open the file with openpyxl and look at wb.defined_names, a dictionary of workbook-scoped names. Each value is a DefinedName whose attr_text is the formula the name stands for:

from openpyxl import load_workbook

wb = load_workbook("pricing.xlsx", data_only=True)
for name, dn in wb.defined_names.items():
    print(f"{name:10} {dn.attr_text}")
for ws in wb.worksheets:
    for name, dn in ws.defined_names.items():
        print(f"{ws.title}: {name} -> {dn.attr_text}")

Workbook-scoped names live on the workbook; names scoped to a single sheet live on that worksheet's own defined_names. That is how two sheets can both define Totals without clashing — the Q3 and Q4 sheets each have their own. openpyxl 3.1 changed this API: older answers online use wb.defined_names.definedName or wb.get_named_range(), which no longer exist. data_only=True makes cells return the values Excel last calculated rather than formula strings, which is what you want when reading data (see Read Formula Results with openpyxl data_only).

Step 2 — Resolve the name and build the DataFrame

destinations turns the reference into (sheet title, cell range) pairs — the quotes around the sheet name are removed, the $ signs are kept, and a worksheet accepts the reference either way. Index the worksheet with the range to get the cells, pull out their values, and hand the rows to pandas:

import pandas as pd

def read_named_range(wb, name, header=True):
    dn = wb.defined_names[name]
    (sheet, ref), = dn.destinations          # exactly one area expected
    rows = [[c.value for c in row] for row in wb[sheet][ref]]
    if header:
        return pd.DataFrame(rows[1:], columns=rows[0])
    return pd.DataFrame(rows)

rates = read_named_range(wb, "RateTable")
print(rates)
print(rates.dtypes.to_dict())

The rate table comes back with its header from the first row of the range and proper numeric types. The title in A1 and the blank rows above the table are ignored, because the name covers only A4:C8 — which is the advantage over skiprows and nrows, where the offsets break as soon as someone adds a line above the table.

A single-cell name such as TaxRate is usually a parameter rather than a table. Read its one value directly:

(sheet, ref), = wb.defined_names["TaxRate"].destinations
tax_rate = wb[sheet][ref].value
print(sheet, ref, tax_rate)

Step 3 — Read sheet-scoped names

A name scoped to a sheet is not in wb.defined_names; look on the worksheet instead. Resolve it the same way:

def read_sheet_name(wb, sheet, name):
    dn = wb[sheet].defined_names[name]
    (target, ref), = dn.destinations
    rows = [[c.value for c in row] for row in wb[target][ref]]
    return pd.DataFrame(rows[1:], columns=rows[0])

both = pd.concat({s: read_sheet_name(wb, s, "Totals") for s in ["Q3 Sales", "Q4 Sales"]},
                 names=["Sheet", None])
print(both)
print("in workbook scope:", "Totals" in wb.defined_names)

Reading the same local name from several sheets and stacking the results with pd.concat is a neat way to combine per-period sheets that were all built from one template. The keys of the dictionary become an outer index level, so each row still says which sheet it came from.

Step 4 — Handle names that do not point at one block

Not every name is a clean rectangle. Check the awkward cases before trusting the result:

def describe(dn):
    if dn.attr_text.startswith("#REF!") or "#REF!" in dn.attr_text:
        return "broken: target was deleted"
    if dn.is_reserved:
        return "reserved (print area, filter database)"
    try:
        dests = list(dn.destinations)
    except Exception as exc:
        return f"not a plain reference: {type(exc).__name__}"
    if not dests:
        return "formula, not a cell reference"
    return f"{len(dests)} area(s): {dests}"

for name in ["RateTable", "Regions", "OldLookup", "SalesDyn"]:
    print(f"{name:10}", describe(wb.defined_names[name]))

• Several areas. Regions refers to A5:A6 and A7:A8, two areas joined by a comma. destinations yields both; read each and concatenate them rather than unpacking a single pair.
• Broken names. A name whose rows or sheet were deleted refers to #REF!. Excel keeps it; your script should report it rather than fail with a confusing KeyError.
• Formula names. A dynamic name such as SalesDyn, defined with OFFSET and COUNTA to grow with the data, is a formula, not a reference. openpyxl cannot evaluate it, so it has no destinations. Read the anchor cell and expand it yourself, or — better — convert the block to an Excel table and read the table by name (see Read an Excel Table by Name into pandas).

Multiple areas are read and joined like this:

parts = [[c.value for row in wb[s][ref] for c in row] for s, ref in wb.defined_names["Regions"].destinations]
print(sum(parts, []))

Step 5 — Use the name to drive read_excel

If you prefer read_excel's type handling and options (dtype, converters, parse_dates), use openpyxl only to find where the range is, then translate the reference into skiprows, nrows and usecols:

from openpyxl.utils import range_boundaries

(sheet, ref), = wb.defined_names["RateTable"].destinations
min_col, min_row, max_col, max_row = range_boundaries(ref)
df = pd.read_excel("pricing.xlsx", sheet_name=sheet, header=0,
                   skiprows=min_row - 1, nrows=max_row - min_row,
                   usecols=range(min_col - 1, max_col))
print(df)

range_boundaries returns 1-based column and row numbers; skiprows counts the rows to drop before the header, and nrows counts data rows after it, which is why it is max_row - min_row and not one more. usecols accepts zero-based column positions. The file is opened twice — once by openpyxl for the name and once by read_excel — which is fine for small workbooks. For large ones, read the cells from the workbook you already have open instead.

Common pitfalls

• Looking only at wb.defined_names. Sheet-scoped names are on ws.defined_names. A missing name may simply be local to a sheet.
• Code written for openpyxl before 3.1. wb.defined_names.definedName and wb.get_named_range() from older answers raise AttributeError on current versions; wb.defined_names is now a plain dictionary keyed by name.
• Reading formulas instead of values. Without data_only=True, cells with formulas return strings like =B5*1.2. With it, they return the last value Excel saved — which is None for files written by Python and never opened in Excel (see Recalculate Excel Formulas Without Excel).
• Assuming the first row is a header. A name like Rates might cover only the numbers. Pass header=False and set column names yourself when the range has no header row.
• Hard-coding the header row. The point of the name is that the block can move. If the script also passes skiprows=3, it breaks the first time someone inserts a row — derive every offset from the name, as in Step 5.
• Names with the same spelling in two scopes. A workbook-level Totals and a sheet-level Totals can coexist. Excel formulas on that sheet use the local one; decide explicitly which one your script means rather than taking whichever lookup succeeds first.

Performance notes

Read-only mode supports everything on this page: wb.defined_names, the sheet-scoped names on each worksheet, and indexing a worksheet with a range. Resolving a name costs nothing; the work is loading the workbook. For a large file where only one named block matters, load_workbook(..., read_only=True, data_only=True) streams the sheet and indexing a range reads only the rows it covers. When you need the same range from hundreds of files, process them in parallel and return small DataFrames from each worker.

Conclusion

Named ranges are the most robust handle a script can have on a block of cells. Resolve the name with openpyxl — wb.defined_names for workbook scope, ws.defined_names for sheet scope — take destinations, index the worksheet with the reference, and build the DataFrame from the first row and the rest. Check for multiple areas, #REF! targets and formula-based names before trusting the result, and fall back to range_boundaries plus read_excel when you need its parsing options.

Frequently asked questions

Can pandas read_excel read a named range directly? No. read_excel takes a sheet name plus skiprows, nrows and usecols, but no range name. Resolve the name with openpyxl first, then either build the DataFrame from the cells or translate the reference into read_excel arguments with range_boundaries.

Why does wb.defined_names not contain a name I can see in Excel? It is probably scoped to one sheet. In Excel's Name Manager the Scope column shows it; in openpyxl it is in that worksheet's defined_names, not the workbook's. Hidden names and reserved names such as _xlnm.Print_Area are also stored differently.

How do I read a dynamic named range defined with OFFSET? openpyxl does not evaluate formulas, so a name defined with OFFSET or INDEX has no fixed destination. Read from the anchor cell until the first empty row, or convert the block to an Excel table, which records its current size in the file and can be read by name.

How do I read a named range that covers several areas? Loop over dn.destinations, which yields one (sheet, reference) pair per area, read each area's cells, and concatenate the results. Unpacking a single pair raises a ValueError when there is more than one area.

Related

• Up one level: Reading Excel Files with pandas — every option that decides what lands in the DataFrame.
• Create a Named Range in Excel with openpyxl — the writing side of the same API.
• Read a Cell Range into a pandas DataFrame — when the block has an address but no name.
• Read an Excel Table by Name into pandas — tables resize themselves, names do not.