# Guide: Manual named-range tables (no Benchling)
> π¨ **Colour walkthrough:** [open the interactive visual version](../visualizations/normal-tables.html) β 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](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.
```python
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](#1-key-concepts-in-60-seconds)
2. [Step 1 β Lay out the table in Excel](#2-step-1--lay-out-the-table-in-excel)
3. [Step 2 β Register the named ranges](#3-step-2--register-the-named-ranges)
4. [Step 3 β Leave a gap below for stacked neighbours](#4-step-3--leave-a-gap-below-for-stacked-neighbours)
5. [Step 4 β Paste data in with `paste_df_to_named_range`](#5-step-4--paste-data-in-with-paste_df_to_named_range)
6. [Step 5 β Read data out](#6-step-5--read-data-out)
7. [Step 6 β Format the table](#7-step-6--format-the-table)
8. [Step 7 β Save and clean up](#8-step-7--save-and-clean-up)
9. [Common errors & fixes](#9-common-errors--fixes)
10. [Related](#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**):
`_table_start` and `_table_end`. You don't create these by hand β
the package writes and maintains them for you. The *anchor* named range
(``) 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`](#9-common-errors--fixes).
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`](#9-common-errors--fixes) first.
For the full contract and the re-init story, see
[concepts/stacked-tables-and-reinit.md](../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`](#addnamedrange-create-an-anchor-from-python).
The header row matters because:
- Its **column extent** (first β last header cell) defines the table's width.
- Each non-empty header cell becomes a **per-column named range** so columns
are addressable by name.
- `paste_df_to_named_range` matches your DataFrame columns **positionally**
against these header cells β the leftmost DataFrame column lands under the
leftmost header, and so on (see [Step 4](#5-step-4--paste-data-in-with-paste_df_to_named_range)).
---
## 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).
```python
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]:
```
```python
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:**
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).
```python
# 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:
```python
def add_named_range(wb, sheet_name, name, range_object,
scope="sheet", replace_if_exists=False):
```
```python
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](../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.
```python
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:
```
```python
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
- **Grow** (more rows than before): rows are inserted below the old extent;
any stacked neighbour shifts down by the delta.
- **Shrink** (fewer rows): the bottom rows are cleared and the band collapses;
a stacked neighbour moves up by the delta. The `_table_end` bookmark follows
the new last row.
- **Widen** (more columns than the current header): the header is **auto-extended**
and the new header cells take their labels from `df.columns`. Safe because the
stacked-only invariant guarantees nothing shares the table's rows. Overflowing
past Excel's last column (XFD = 16,384) raises `ExcelColumnOverflowError`.
- **Narrow** (fewer columns): trailing data cells are cleared; the header keeps
its declared width.
### `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.
```python
# 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`):
```python
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](../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.
```python
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,
):
```
```python
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`.
```python
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,
):
```
```python
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
```python
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): ...
```
```python
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:
```python
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`
```python
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.
```python
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):
```python
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"`.
```python
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`
```python
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**.
```python
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:
```python
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
```python
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:
```python
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](../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:
```python
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}")
```
---
## 10. Related
- [concepts/stacked-tables-and-reinit.md](../concepts/stacked-tables-and-reinit.md)
β the stacked-only contract, danger zones, bookmark auto-heal, and re-initialisation.
- [guides/benchling-single.md](benchling-single.md) β build a table from a single Benchling result schema.
- [guides/benchling-multiple.md](benchling-multiple.md) β multiple Benchling tables on one or many sheets.
- [guides/plates.md](plates.md) β plate layouts (96/384-well) as managed tables.
- [reference/tools.md](../reference/tools.md) β full function reference, `cleanup_stale_table_names`, file I/O, plotting.
- [index.md](../index.md) β documentation hub.