Guide: Manual named-range tables (no Benchling)

🎨 Colour walkthrough: open the interactive visual version — colour before/after Excel-grid diagrams of every step. The Unicode grids below are the always-available text version.

This is the definitive guide to building, filling, reading, formatting, and maintaining managed tables in Excel using mgtx-xlwings-utils — with the table layout authored by hand in the spreadsheet, not pulled from Benchling. If your columns come from a Benchling result schema, read benchling-single.md instead; everything below still applies once the table exists, but the setup step is different.

Everything here runs against a live Excel workbook on Windows via xlwings (Excel COM automation). You hold the workbook open in Excel, run Python next to it, and the helpers read from / write to the cells you see on screen.

import xlwings as xw
from xlwings_package import initialize_workbook

wb = xw.Book("MyAssay.xlsx")   # attaches to the open workbook
initialize_workbook(wb)        # optional: store wb as the module default

Contents

  1. Key concepts in 60 seconds
  2. Step 1 — Lay out the table in Excel
  3. Step 2 — Register the named ranges
  4. Step 3 — Leave a gap below for stacked neighbours
  5. Step 4 — Paste data in with paste_df_to_named_range
  6. Step 5 — Read data out
  7. Step 6 — Format the table
  8. Step 7 — Save and clean up
  9. Common errors & fixes
  10. Related

1. Key concepts in 60 seconds

Managed table. A rectangular table whose live data extent is tracked by two sheet-scoped named ranges (called bookmarks): <name>_table_start and <name>_table_end. You don’t create these by hand — the package writes and maintains them for you. The anchor named range (<name>) points at the header row; the bookmarks point at the top-left and bottom-right data cells.

Per-column names. Each header cell also gets its own sheet-scoped named range (a token such as Sample_ID), so you can read or address any column by name regardless of where it physically sits.

Lane model. Tables that share at least one column (transitively) form a lane. Within a lane, two managed tables must occupy disjoint row ranges — row overlap raises SideBySideNotSupportedError. Tables in completely disjoint columns are independent lanes and may freely share rows (side-by-side, L-shape, or T-shape layouts are all allowed). The restriction is about row overlap within a lane, not across the whole sheet.

Danger zone. The band directly below a table, in its own columns, is owned by the table — it grows into that space on a repaste. Loose content placed there would be silently shifted or clobbered, so the package refuses and raises TableSpaceOccupiedError first.

For the full contract and the re-init story, see concepts/stacked-tables-and-reinit.md.

2. Step 1 — Lay out the table in Excel

Do this part in Excel itself, by hand, before touching Python.

  1. Pick a sheet (say Inputs) and a top-left corner.
  2. Type your column headers across a single row. This is the header row and it is the load-bearing element of the whole workflow — every column name, every named range, and the table’s left/right edges are derived from it.
  3. Headers must be contiguous (no blank cell between two headers). A blank header cell marks the right edge of the table.
A
B
C
D
1
Sample No.
Sample ID
Conc (mg)
Result
2
3

Which cells to select, and why

Before initialising you give the package an anchor named range that covers the header row. Two ways to do that:

Option A — select the header cells and name them in Excel. Select A1:D1, click the Name Box (top-left, left of the formula bar), type a name (e.g. sample_table), and press Enter. That single multi-cell name is your anchor.

Option B — create the anchor from Python with add_named_range.

The header row matters because:

3. Step 2 — Register the named ranges

initialise_table_named_ranges scans the anchor’s header row and assigns a sheet-scoped named range to every header cell that doesn’t already have one. Run it once per table (re-running is safe and idempotent).

def initialise_table_named_ranges(
    wb,
    table_name_range: str,
    scope_sheet: Optional[str] = None,
    include_list: Optional[Iterable[str]] = None,
    ignore_list: Optional[Iterable[str]] = None,
) -> dict[str, str]:

from xlwings_package import initialise_table_named_ranges

mapping = initialise_table_named_ranges(
    wb,
    "sample_table",        # the anchor name covering A1:D1
    scope_sheet="Inputs",
)
# mapping → {"Sample_No.": "Sample No.", "Sample_ID": "Sample ID",
#            "Conc_mg": "Conc (mg)", "Result": "Result"}

Returns a dict mapping each generated range-name token to its display header string. Tokens are sanitised from the header text: spaces and punctuation collapse to _, leading digits get a col_ prefix (e.g. "1st value" → col_1st_value). Case is preserved.

What gets created on the sheet:

BEFORE init
A
B
1
Sample ID
Conc (mg)

names: sample_table (A1:B1) — anchor

→
AFTER init
A
B
1
Sample_IDSample ID
Conc_mgConc (mg)

names: sample_table (A1:B1) — anchor (unchanged)
Sample_ID = $A$1 — per-column (new)
Conc_mg = $B$1 — per-column (new)

If there are already data rows below the header at init time, the package also writes the bookmark pair sample_table_table_start / sample_table_table_end covering that data. If the anchor itself spans multiple rows, the bookmarks are taken from the anchor extent. If the header is alone with no data yet, no bookmarks are written — the first paste creates them.

Restricting which columns are named

Pass either include_list or ignore_list (never both — that raises ValueError). Selectors match against the range-name token or the display header (case- and whitespace-insensitive).

# Only register two columns:
initialise_table_named_ranges(wb, "sample_table", scope_sheet="Inputs",
                              include_list=["Sample ID", "Result"])

# Register everything except Notes:
initialise_table_named_ranges(wb, "sample_table", scope_sheet="Inputs",
                              ignore_list=["notes"])

Note: include_list / ignore_list filter the returned mapping. The per-column names for the filtered-out columns may still be created on the sheet; the filter controls what you get back, which then drives reads.

add_named_range — create an anchor from Python

If you’d rather not use the Excel Name Box, build the anchor in code:

def add_named_range(wb, sheet_name, name, range_object,
                    scope="sheet", replace_if_exists=False):

from xlwings_package import add_named_range

ws = wb.sheets["Inputs"]
header = ws.range("A1:D1")
add_named_range(wb, "Inputs", "sample_table", header,
                scope="sheet", replace_if_exists=True)

scope is "sheet" (default, recommended for managed tables) or "workbook". replace_if_exists=True overwrites a same-named range instead of erroring.

4. Step 3 — Leave a gap below for stacked neighbours

If this is the only table on the sheet, skip this — it has unlimited room to grow downward.

If you plan to stack a second managed table below the first, leave a few blank rows between them at layout time. That gap is the separation the package preserves through every grow / shrink.

A
B
C
1
Sample ID
Conc (mg)
Result
2
3
4
↕ gap — leave blank; the package keeps it
5
6
7
Replicate
Mean
Std

On the first paste into Table 1, the package measures the empty rows down to Table 2’s header and writes into that gap first; it only pushes Table 2 down if the new data exceeds the gap. On subsequent pastes it shifts Table 2 by exactly the row-count delta. Either way, the gap you set up is preserved.

Do not put loose labels/notes in the gap (the danger zone) — see concepts/stacked-tables-and-reinit.md.

5. Step 4 — Paste data in with paste_df_to_named_range

This is the workhorse. It writes a DataFrame’s values into the table, grows or shrinks the row count to fit, optionally widens the header, sanitises against formula injection, and updates the bookmarks — all in one call.

def paste_df_to_named_range(
    wb,
    df: pd.DataFrame | list[pd.DataFrame],
    named_range: str | list[str],
    scope_sheet: Optional[str] = None,
    sanitize_formulas: bool = True,
) -> None:

import pandas as pd
from xlwings_package import paste_df_to_named_range

df = pd.DataFrame({
    "Sample No.": [1, 2, 3],
    "Sample ID":  ["VS0001", "VS0002", "VS0003"],
    "Conc (mg)":  [12.4, 9.8, 15.1],
    "Result":     ["Pass", "Pass", "Fail"],
})

paste_df_to_named_range(wb, df, "sample_table", scope_sheet="Inputs")
BEFORE paste
A
B
1
Sample No.
Sample ID
2
3
4
→
AFTER paste
A
B
1
Sample No.
Sample ID
2
start1
VS0001
3
2
VS0002
4
3
endVS0003

Column matching is positional — order matters

The DataFrame’s columns are written left-to-right against the existing header positions. Before writing, the package checks each DataFrame column label against the header at that position (matching the per-column range token or the display header, case/whitespace-insensitive). If a label doesn’t line up, it raises ColumnOrderMismatchError before touching the sheet — protecting you from silently misaligning a reordered DataFrame.

The DataFrame index is never written — only df.values.

Grow, shrink, and widen

sanitize_formulas (default True)

By default, text cells beginning with =, +, -, or @ are apostrophe-escaped so externally-sourced data can’t smuggle a live Excel formula into the sheet (the CSV/formula-injection class). Numbers and numeric-looking strings ("-5.3", "+1e3") are left alone, so bio labels like "+/+" and "-/-" survive as text.

# Trusted source, want a real formula written through verbatim:
paste_df_to_named_range(wb, df, "sample_table",
                        scope_sheet="Inputs", sanitize_formulas=False)

Two more sanitisation behaviours happen automatically regardless of the flag: ±inf becomes an empty cell, and strings over Excel’s 32,767-char per-cell limit are truncated (with a single UserWarning naming the affected columns).

Errors paste_df_to_named_range raises

Error When Fix
EmptyDataError df has zero rows Don’t paste an empty frame; guard with if not df.empty.
ColumnOrderMismatchError df.columns don’t line up with existing headers (reordered / inserted / dropped middle column) Reorder the DataFrame to match the sheet, or re-initialise the table to the new schema.
TableSpaceOccupiedError Loose content sits in the danger zone (or in the auto-widen zone) Move the content above the title row or to a column outside the cluster, or give it its own named range.
SideBySideNotSupportedError Another managed table’s rows would overlap this table’s post-paste rows Restack the tables vertically (disjoint rows), or move one to fully disjoint columns.
NamedRangeNotFoundError named_range can’t be resolved Check the name and scope_sheet; run initialise_table_named_ranges first.
ExcelColumnOverflowError Auto-widen would pass column XFD (16,384) Reduce DataFrame width or move the header further left.

Pasting multiple tables in one call

Pass parallel lists (lengths must match, else ValueError):

paste_df_to_named_range(
    wb,
    [df_results, df_summary],
    ["results_table", "summary_table"],
    scope_sheet="Outputs",
)

Bookmarks self-heal

If _table_start / _table_end are missing, broken (#REF!), or inverted, the next paste silently drops the bad pair and writes as if it were a first paste. See concepts/stacked-tables-and-reinit.md.

6. Step 5 — Read data out

Two readers return a (DataFrame, mapping) tuple. The difference is how they find the table boundaries.

import_excel_table — read from the header anchor

Use when you have the header anchor named range. Data rows are discovered by scanning downward from the header. Bookmarks are auto-healed and grown (never auto-shrunk) on every call.

def import_excel_table(
    wb,
    table_name_range: str,
    scope_sheet: Optional[str] = None,
    use_name_range_names: bool = True,
    check_empty_columns: bool = True,
    include_list: Optional[Iterable[str]] = None,
    ignore_list: Optional[Iterable[str]] = None,
):

from xlwings_package import import_excel_table

df, mapping = import_excel_table(wb, "sample_table", scope_sheet="Inputs")
# df.columns are range-name tokens by default (use_name_range_names=True):
#   ["Sample_No.", "Sample_ID", "Conc_mg", "Result"]

# Want the human header labels as columns instead?
df, mapping = import_excel_table(wb, "sample_table", scope_sheet="Inputs",
                                 use_name_range_names=False)
#   ["Sample No.", "Sample ID", "Conc (mg)", "Result"]

check_empty_columns=True (default) raises EmptyDataError if any column is entirely null; the table being empty also raises EmptyDataError.

scan_named_range_table — read from explicit start/end bookmarks

Use when there’s no header anchor, only the _table_start / _table_end bookmarks. The header row is inferred as the row immediately above table_start_name.

def scan_named_range_table(
    wb,
    table_start_name: str,
    table_end_name: str,
    scope_sheet: Optional[str] = None,
    use_name_range_names: bool = False,
    include_list: Optional[Iterable[str]] = None,
    ignore_list: Optional[Iterable[str]] = None,
):

from xlwings_package import scan_named_range_table

df, mapping = scan_named_range_table(
    wb,
    table_start_name="sample_table_table_start",
    table_end_name="sample_table_table_end",
    scope_sheet="Inputs",
)

Contiguous-header assumption. scan_named_range_table finds the table’s right edge by scanning header cells rightward from the start column and stopping at the first blank header cell. A deliberately blank separator column between two header groups is therefore not supported on this path — keep the header row contiguous. (import_excel_table derives the width from the anchor’s column extent instead.)

  import_excel_table scan_named_range_table
Input header anchor name start + end bookmark names
Header row the anchor’s row row above table_start
Width anchor column extent scan right to first blank header
Default column keys range-name tokens display headers
Empty-column guard yes (check_empty_columns) no

Reading individual values

def get_named_range_results(wb=None, worksheet_name="", named_range="", strip=True): ...
def get_value(wb, range_name, sheet_name=""): ...
def get_list_results(wb, sheet_name, column_name, start_row_name, end_row_name): ...

from xlwings_package import get_named_range_results, get_value, get_list_results

# Single named cell (strips surrounding whitespace by default):
run_id = get_named_range_results(wb, "Inputs", "run_id")

# Any range/address by value:
title = get_value(wb, "A1", "Inputs")

# A contiguous vertical slice into a Python list — column + start/end row markers:
sample_ids = get_list_results(wb, "Inputs", "Sample_ID",
                              "sample_table_table_start", "sample_table_table_end")

get_list_results takes a column_name (a named range or address whose column is used) and two row markers (named ranges or addresses whose rows bound the slice), returning the values from that column between those rows.

Validate / transform the DataFrame

The dataframe_utils helpers pair naturally with reads:

from xlwings_package import (
    strip_strings, check_missing_columns,
    check_case_sensitive_entries, rename_dtype_columns,
)

df = strip_strings(df)
check_missing_columns(df, ["Sample_ID", "Conc_mg", "Result"])
check_case_sensitive_entries(df, "Result", "Pass", display_name="Result")
df = rename_dtype_columns(df, "Conc_mg", "Conc_mg", float)

7. Step 6 — Format the table

Colour rows with colour_rows

def colour_rows(
    wb,
    name_range: str,
    df: pd.DataFrame,
    mode: Literal["group_alternate", "equals_no", "values_match"],
    column_name: str,
    scope_sheet: Optional[str] = None,
    values_to_colour: Optional[Iterable] = None,
    colour_name: Optional[str] = None,
):

df must be in the same row order as the sheet — the function maps row i of df to data row i under the header. Three modes:

group_alternate — alternates light-blue / white bands each time the value in column_name changes. Great for visually grouping repeated categories.

from xlwings_package import colour_rows

colour_rows(wb, "sample_table", df, mode="group_alternate",
            column_name="batch_id", scope_sheet="Inputs")
A
B
2
batch_1
…
3
batch_1
…
4
batch_2
…
5
batch_2
…
6
batch_3
…

equals_no — colours red every row where column_name equals exactly the string "No" (pass/fail flag columns):

colour_rows(wb, "sample_table", df, mode="equals_no",
            column_name="passed", scope_sheet="Inputs")

values_match — colours rows whose column_name value is in values_to_colour, using colour_name. Both args are required for this mode (else ValueError). colour_name accepts a built-in name (red, dark red, light red, blue, dark blue, light blue, white), any CSS colour name (via webcolors, e.g. "salmon"), or a hex string like "#FFB6C1".

colour_rows(wb, "sample_table", df, mode="values_match",
            column_name="Result", values_to_colour=["Fail", "Invalid"],
            colour_name="#FFB6C1", scope_sheet="Inputs")

colour_rows raises EmptyDataError if column_name is missing from df or entirely null.

Add a dropdown source list with add_dropdown_list

def add_dropdown_list(
    wb: xw.Book,
    *,
    list_sheet_name: str,
    title: str,
    options: Sequence[str],
    start_column: str = "A",
    start_row: int = 1,
    column_step: int = 2,
) -> tuple[xw.Sheet, xw.Range]:

This writes a titled option source list to the first free column of a list sheet (creating the sheet if needed), scanning columns from start_column in steps of column_step (A, C, E …). All keyword args after wb are keyword-only.

from xlwings_package import add_dropdown_list

list_ws, options_range = add_dropdown_list(
    wb,
    list_sheet_name="Lists",
    title="Result options",
    options=["Pass", "Fail", "Invalid"],
)

Important: add_dropdown_list only lays out the option values; it does not attach an in-cell Data Validation dropdown to your table. To make a working dropdown, point a Validation at the returned options_range yourself:

target = wb.sheets["Inputs"].range("D2:D100")
target.api.Validation.Add(
    Type=xw.constants.DVType.xlValidateList,
    Formula1=f"='{options_range.sheet.name}'!{options_range.address}",
)

The returned options_range excludes the title row, so it points only at the real option values.

8. Step 7 — Save and clean up

wb.save()
# wb.save("FinalAssay.xlsx")   # save-as a copy

If you stored a module-level default workbook with initialize_workbook, clear it when done so later calls don’t accidentally reuse a closed book:

from xlwings_package import clear_workbook
clear_workbook()

Need to remove #REF! orphans left by manual edits in the Name Manager? Use cleanup_stale_table_names (see reference/tools.md).

9. Common errors & fixes

Exception Typically raised by Cause Fix
EmptyDataError paste_df_to_named_range, import_excel_table Zero-row DataFrame; empty table; an all-null column with check_empty_columns=True Guard against empty frames; pass check_empty_columns=False if all-null columns are expected.
ColumnOrderMismatchError paste_df_to_named_range DataFrame columns reordered / a middle column inserted or dropped relative to the sheet header Reorder the DataFrame to match the existing header order, or re-initialise the table to the new schema.
TableSpaceOccupiedError paste_df_to_named_range Loose content in the table’s danger zone or in the auto-widen zone Move it above the title row, to a column outside the cluster, or give it its own named range.
SideBySideNotSupportedError initialise_table_named_ranges, paste_df_to_named_range A second managed table’s rows overlap this table’s rows (row overlap within a column cluster) Stack the tables vertically (disjoint rows), or place one in fully disjoint columns.
NamedRangeNotFoundError all readers, paste_df_to_named_range, resolve_named_range Anchor/bookmark name missing or wrong scope; on scan_named_range_table, start/end on different sheets Verify the name and scope_sheet; run initialise_table_named_ranges first; pass scope_sheet to disambiguate.
WorksheetNotFoundError get_worksheet, resolve_named_range (with scope_sheet) Sheet name doesn’t exist Check the exact sheet tab name (case-sensitive).
ExcelColumnOverflowError paste_df_to_named_range (widen path) Auto-widen would pass Excel’s last column (XFD = 16,384) Reduce DataFrame width, or move the header to a leftmost column.
ValueError initialise_table_named_ranges, colour_rows, add_named_range, add_dropdown_list include_list + ignore_list together; missing required args for the chosen colour_rows mode; bad scope; empty title/options Pass only one list; supply mode-required args; use a valid scope/title.

All package exceptions subclass XlwingsPackageError, so you can catch the whole family at once:

from xlwings_package import XlwingsPackageError

try:
    paste_df_to_named_range(wb, df, "sample_table", scope_sheet="Inputs")
except XlwingsPackageError as exc:
    print(f"Table operation failed: {exc}")