Plates: the complete guide

🎨 Colour walkthrough: open the interactive visual version — colour plate-grid diagrams (table → layout → read-back). The Unicode grids below are the always-available text version.

Plates are managed tables in mgtx-xlwings-utils, specialised for the microplate workflow: you collect samples in a flat plate table, then project them into a visual plate layout (a row × column well grid) that mirrors the physical plate on your bench. The layout round-trips back to a tidy DataFrame.

Like all managed tables in this package, plates follow the lane model: tables that share at least one column (transitively) form a lane, and within a lane any two managed tables or plates must occupy pairwise-disjoint row ranges. Row overlap within a lane raises SideBySideNotSupportedError. Tables in entirely disjoint column bands are independent lanes and may share rows freely. See Stacked tables & re-init for the full contract.

All examples assume a live workbook:

import xlwings as xw
from xlwings_package import (
    create_plate_table,
    create_plate_layout,
    plate_layout_to_dataframe,
    get_plate_format_presets,
)

wb = xw.Book("plates.xlsx")   # or xw.books.active for the open workbook

1. Concepts

Plate table vs. plate layout

  Plate table (create_plate_table) Plate layout (create_plate_layout)
Shape Flat list — one row per well 2-D grid — rows × columns of wells
Columns Sample No., Sample ID, Plate Position, plus your extras One cell per well, with stacked variable lines
Purpose Data entry / sample inventory Visual map matching the physical plate
You edit Yes — type or paste sample data here No — generated from the table
Default sheet "plate sheet" "plate layouts"

The table is your data-entry surface. The layout is the read-only projection. You fill the table, then call create_plate_layout to draw the grid; the layout reads its variable values from the table’s named ranges.

A plate written by create_plate_table / create_plate_layout can always be read back with plate_layout_to_dataframe — that is the named-range round-trip that ties the three functions together.

Supported formats

get_plate_format_presets() returns the built-in formats. The real keys and their dimensions (rows × columns) are:

plate_format key Format Rows × Cols Row labels Total wells
"6" 6-well 2 × 3 A–B 6
"12" 12-well 3 × 4 A–C 12
"24" 24-well 4 × 6 A–D 24
"48" 48-well 6 × 8 A–F 48
"96" 96-well 8 × 12 A–H 96 (default)
"384" 384-well 16 × 24 A–P 384
"1536" 1536-well 32 × 48 A–AF 1536 
presets = get_plate_format_presets()
# {"6": {...}, "12": {...}, "24": {...}, "48": {...},
#  "96": {...}, "384": {...}, "1536": {...}}
presets["96"]
# {'format': '96-well', 'rows_x_columns': '8 x 12',
#  'well_diameter_mm': '6.10 - 7.15', 'well_depth_mm': '10.40 - 11.40',
#  'max_well_volume': '400 uL', 'recommended_working_volume': '80-350 uL'}

A 6-well plate, drawn in full:

1
2
3
A
A1
A2
A3
B
B1
B2
B3

A 24-well plate (A–D × 1–6):

1
2
3
4
5
6
A
A1
A2
A3
A4
A5
A6
B
B1
B2
B3
B4
B5
B6
C
C1
C2
C3
C4
C5
C6
D
D1
D2
D3
D4
D5
D6
24-well = A–D × 1–6 · 96-well = A–H × 1–12 · 384-well = A–P × 1–24 · 1536-well = A–AF × 1–48

Larger formats follow the same shape, just bigger: 96-well is A–H × 1–12, 384-well is A–P × 1–24, and 1536-well is A–AF × 1–48 (its rows spill past Z into two-letter labels AA, AB, … AF).

Custom dimensions

When no preset fits, supply both plate_rows_count and plate_cols_count (passing only one raises ValueError). A 10 × 10 plate, for example, gets row labels A–J and columns 1–10, and stores its shape as "custom". Row labels extend past 26 the same way Excel columns do: A–Z, then AA–ZZ, then AAA–ZZZ (up to 18,278 rows), so very tall custom plates round-trip correctly.

The Plate Position dropdown

Every plate table’s Plate Position column is wired to an in-cell dropdown listing every valid well for the chosen format (e.g. A1 … H12 for 96-well). The well options are written to a separate list sheet (default "lists") via add_dropdown_list, and a Data Validation list (xlValidateList, xlValidAlertStop) is attached to the data cells. This keeps positions canonical so the layout fills wells by matching Plate Position values, not by row order (see section 4).

2. create_plate_table — build the entry sheet

def create_plate_table(
    wb: xw.Book,
    *,
    sheet_name: str = "plate sheet",
    sample_rows: int | None = None,
    extra_headers: Sequence[str] | None = None,
    table_title: str = "Plate Table",
    header_named_range: str = "plate_table",
    dropdown_list_sheet_name: str = "lists",
    dropdown_list_title: str = "plate dropdown list",
    plate_format: str = "96",
    plate_rows_count: int | None = None,
    plate_cols_count: int | None = None,
    start_col: int | str = 2,
    sanitize_formulas: bool = True,
) -> tuple[pd.DataFrame, xw.Sheet]:

Returns (df, sheet):

What it builds on the sheet:

The sheet is recreated from scratch. If sheet_name already exists, a new table is appended below the existing used range (3 rows down), not overwritten. Use a fresh sheet name for a clean slate.

Key parameters

Example

df, ws = create_plate_table(
    wb,
    plate_format="96",
    sheet_name="plate sheet",
    extra_headers=["Batch", "Concentration"],
)
# df.columns → ['Sample No.', 'Sample ID', 'Plate Position', 'Batch', 'Concentration']
# len(df)    → 96

Custom 10 × 10 plate:

df, ws = create_plate_table(
    wb,
    plate_rows_count=10,
    plate_cols_count=10,
    extra_headers=["Treatment"],
)
# 100 rows; row labels A–J; columns 1–10; format stored as "custom"

Before (empty "plate sheet") → after (B-anchored table):

Before — empty "plate sheet"
B
C
D
E
F
1
2
3
→
After — B-anchored Plate Table
B
C
D
E
F
1
96
8
12
row 1 = hidden plate_format_key / rows / cols metadata
3
Plate Table
4
Sample No.
Sample ID
Plate Position
Batch
Concentration
5
1
A1 ▾
6
2
B1 ▾
7
3
C1 ▾
… 96 rows: Plate Position pre-filled column-major A1, B1, …, H1, A2, …, H12 (▾ = dropdown) …

3. Fill the table

After create_plate_table, enter your sample data in Excel (or write it from Python). Typical edits:

Writing from Python (positions pre-filled, IDs and a variable typed in):

ws = wb.sheets["plate sheet"]
n = 96
ws.range("sample_id").offset(1, 0).resize(n, 1).value = \
    [[f"S-{i:03d}"] for i in range(1, n + 1)]
ws.range("concentration").offset(1, 0).resize(n, 1).value = \
    [[round(0.1 * i, 2)] for i in range(1, n + 1)]

Formula-injection safety. Writes sanitize by default (sanitize_formulas=True): a value like =cmd|... is stored as the literal text, not executed. This applies to both create_plate_table and create_plate_layout. Opt out per call with sanitize_formulas=False only when you intend the cell to hold a real formula.

For a 96-well plate you must populate at least 96 wells before laying out — the layout validates the source row count (see section 6).

4. create_plate_layout — draw the grid

def create_plate_layout(
    wb: xw.Book,
    *,
    plate_name: str,
    plate_title: str,
    value_named_ranges: Sequence[str],
    well_value_order: Sequence[str] | None = None,
    source_table_named_range: str = "plate_table",
    source_sheet_name: str = "plate sheet",
    target_sheet_name: str = "plate layouts",
    start_cell: str = "B2",
    append_if_sheet_exists: bool = True,
    sanitize_formulas: bool = True,
) -> xw.Sheet:

Returns the target xw.Sheet. The plate format is read automatically from the source sheet’s plate_format_key / plate_rows_count_key / plate_cols_count_key metadata — you don’t pass it again.

Key parameters

Well-value ordering

The layout fills wells by matching Plate Position values from the source table — well A1 gets the row whose plate_position == "A1". (If the source table has no plate_position column, it falls back to column-major iloc order.) Duplicate positions raise ValueError. Within each well, the chosen variables stack vertically in well_value_order.

Example

ws = create_plate_layout(
    wb,
    plate_name="plate_run_001",
    plate_title="Run 001 — Batch A",
    value_named_ranges=["sample_id", "concentration"],
    source_sheet_name="plate sheet",
    target_sheet_name="plate layouts",
)

Custom line order inside each well:

ws = create_plate_layout(
    wb,
    plate_name="plate_run_001",
    plate_title="Run 001",
    value_named_ranges=["sample_id", "concentration", "batch"],
    well_value_order=["batch", "sample_id", "concentration"],
)

Before (filled table) → after (6-well layout, two stacked lines per well — sample_id then concentration):

Before — filled "plate sheet"
Sample ID
Plate Pos.
Conc.
5
S-001
A1
0.1
6
S-002
B1
0.2
7
S-003
A2
0.3
8
S-004
B2
0.4
… through A3 / B3 (6 wells) …
→
After — 6-well layout (A–B × 1–3)
1
2
3
Vars
A
S-001
0.1
S-003
0.3
S-005
0.5
Sample ID
Conc.
B
S-002
0.2
S-004
0.4
S-006
0.6

(96-well and larger render the same way, one cell per well across A–H × 1–12, etc.) The layout also writes a Variables label column on the right naming each stacked line, a bold orange title, and a full set of sheet-scoped named ranges (<plate_name>_plate_start, _plate_end, one per variable × row, and per column).

Re-running, format changes, and stacked plates below

Re-call create_plate_layout with the same plate_name to refresh a plate in place. It clears the old footprint and redraws — and it handles a format change (e.g. you rebuilt the table as 384-well after starting at 96-well) by clearing the full old footprint before drawing the new one:

Danger zone (full-row delete on shrink). Shrinking a plate deletes entire worksheet rows across the shrink band. This deliberately protects plates stacked directly below (their merged formatting is rebuilt; their cell content is preserved). But any loose, side-by-side content in other columns on those same rows — notes, a chart, an unmanaged table beside the plate — sits in the delete path and will be removed. Keep the columns beside a plate clear of anything you can’t lose, or stack content vertically instead.

Before any insert/delete, the stacked-only invariant is enforced: if another managed table’s projected row range would overlap this plate’s footprint, you get SideBySideNotSupportedError and nothing is written.

5. Read back — plate_layout_to_dataframe

def plate_layout_to_dataframe(
    wb: xw.Book,
    *,
    plate_name: str,
    sheet_name: str = "plate layouts",
    variables: Sequence[str] | None = None,
    rows: Sequence[str] | None = None,
    columns: Sequence[int] | None = None,
    return_format: Literal["wide", "long"] = "wide",
) -> pd.DataFrame:

The plate format is inferred automatically from the named ranges create_plate_layout wrote — no format parameter. Filters:

Every returned frame includes plate_name, plate_row, plate_col, and plate_position. The plate_position column round-trips exactly into a new plate table’s Plate Position column.

# Full plate, wide (one row per well)
df = plate_layout_to_dataframe(wb, plate_name="plate_run_001")

# Filtered subset
df = plate_layout_to_dataframe(
    wb,
    plate_name="plate_run_001",
    variables=["sample_id", "concentration"],
    rows=["A", "B", "C"],
    columns=[1, 2, 3, 4],
)

# Long format (one row per well × variable, with variable_token + variable_display)
df_long = plate_layout_to_dataframe(wb, plate_name="plate_run_001", return_format="long")

Tall custom plates and row labels. Row labels round-trip as 1–3 letter tokens (A–Z, AA–ZZ, AAA–ZZZ), and the reader sorts them by their physical Excel row position, so a 40-row custom plate (labels A … AN) comes back in true plate order rather than alphabetically.

6. Errors & fixes

Common errors & fixes

Symptom / message Likely cause Fix
ValueError: Source table must have at least N rows … Source table has fewer populated rows than the plate has wells. Fill the table to at least the full well count before laying out.
ValueError: Source table has N rows but the K-well plate holds only M wells … Source table has more rows than the plate holds. Trim/filter the source so its row count equals the well count.
ValueError: Duplicate Plate Position values … Two source rows share a Plate Position. Make positions unique (use the dropdown to avoid typos).
ValueError: Plate position 'A1' not found in source table. A well has no matching Plate Position row. Ensure every well position appears exactly once.
ValueError: These named ranges were not found in source table: … value_named_ranges references a column/token not in the table. Use a real display name or its lowercased token (e.g. concentration).
ValueError: plate_format must be one of: 6, 12, 24, 48, 96, 384, 1536. Bad plate_format key. Use a supported key, or custom plate_rows_count + plate_cols_count.
ValueError: Provide both plate_rows_count and plate_cols_count, or neither. Only one custom dimension supplied. Supply both, or neither.
ValueError: Plate named ranges not found for '…' / Sheet '…' was not found. (plate_layout_to_dataframe) Wrong plate_name/sheet_name, or the layout was never created. Match the plate_name and sheet_name you laid out with. Conceptually a NamedRangeNotFoundError situation — the markers are missing.
SideBySideNotSupportedError Another managed table/plate’s row range overlaps this plate. Stack vertically — keep row ranges disjoint. See Stacked tables & re-init.
ExcelColumnOverflowError Plate width pushes past Excel’s last column (XFD = 16,384) — e.g. a wide format with a high start_col. Lower start_col, or choose a narrower format. A 384-well (24 cols) or 1536-well (48 cols) plate needs its columns to fit before XFD.
UserWarning: Plate format metadata not found on sheet '…'. Defaulting to 96-well … plate_format_key / plate_rows_count_key / plate_cols_count_key were deleted from the source sheet. Don’t delete the row-1 metadata; recreate the table if it’s gone.
EmptyDataError An upstream import found no data in the source table region. Confirm the table is populated and its plate_table named range covers the header row.

Notes