# Plates: the complete guide
> ๐จ **Colour walkthrough:** [open the interactive visual version](../visualizations/plates.html) โ 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`](#common-errors--fixes).
Tables in entirely disjoint column bands are independent lanes and may share rows
freely. See [Stacked tables & re-init](../concepts/stacked-tables-and-reinit.md)
for the full contract.
All examples assume a live workbook:
```python
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 |
```python
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:
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](#4-create_plate_layout-draw-the-grid)).
---
## 2. `create_plate_table` โ build the entry sheet
```python
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)`:
- `df` โ a pandas DataFrame mirroring the table's column structure. `Sample No.`
is pre-filled `1 โฆ sample_rows`; `Plate Position` is pre-filled with well
labels in column-major order (`A1, B1, โฆ, A2, B2, โฆ`) and padded with `None`
if `sample_rows` exceeds the well count; all other columns are empty.
- `sheet` โ the `xw.Sheet` it wrote to.
**What it builds on the sheet:**
- A merged, orange (RGB 255,192,0) **title** row, a bold orange **header** row,
and a bordered white **data area** of `sample_rows` rows.
- Default headers `Sample No.`, `Sample ID`, `Plate Position`, followed by any
`extra_headers` (blank/duplicate entries are dropped, order preserved).
- A **sheet-scoped named range per header cell** (lowercased token โ
`Sample ID` โ `sample_id`, `Plate Position` โ `plate_position`,
`Concentration` โ `concentration`) plus a `plate_table` range over the whole
header row.
- The **Plate Position dropdown** (options on the `"lists"` sheet).
- Hidden metadata in row 1: `plate_format_key` (cell A1), `plate_rows_count_key`
(B1), `plate_cols_count_key` (C1). `create_plate_layout` reads these to detect
the format automatically โ don't delete them.
> **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
- **`plate_format`** โ one of the preset keys (`"6"`, `"12"`, `"24"`, `"48"`,
`"96"`, `"384"`, `"1536"`). Default `"96"`. Invalid keys raise `ValueError`.
- **`plate_rows_count` / `plate_cols_count`** โ custom dimensions. Provide
**both** or **neither** (`ValueError` otherwise); both must be `> 0`. When set,
they override `plate_format`.
- **`sample_rows`** โ number of data rows. Defaults to the format's total well
count (96 for 96-well). Must be `> 0`.
- **`extra_headers`** โ extra columns appended after the three defaults.
- **`start_col`** โ left edge of the table. A 1-based integer or an Excel column
letter (`"D"`). Default `2` (column B), leaving column A free. Must be `>= 1`.
- **`sanitize_formulas`** โ `True` (default) escapes values that start with
`= + - @` so Excel can't interpret pasted text as a formula
([formula-injection](#3-fill-the-table) defence). Set `False` only if you
deliberately want live formulas.
### Example
```python
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:
```python
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):
---
## 3. Fill the table
After `create_plate_table`, **enter your sample data in Excel** (or write it
from Python). Typical edits:
- **`Sample ID`** โ your sample names / Benchling IDs.
- **`Plate Position`** โ pick from the dropdown (`A1 โฆ H12`). The layout matches
on these values, so a complete, duplicate-free position column matters.
- **Extra columns** (`Batch`, `Concentration`, โฆ) โ any per-well variables you
want to show in the layout.
Writing from Python (positions pre-filled, IDs and a variable typed in):
```python
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](#common-errors--fixes)).
---
## 4. `create_plate_layout` โ draw the grid
```python
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
- **`plate_name`** โ unique token identifying this layout (used to build its
named ranges). Required, non-blank.
- **`plate_title`** โ title written above the grid. Required, non-blank.
- **`value_named_ranges`** โ which source columns to show in each well. Accept
either **display names** (`"Sample ID"`) or **lowercased tokens**
(`"sample_id"`). At least one required.
- **`well_value_order`** โ the top-to-bottom order of those variable lines
inside each well. Defaults to `value_named_ranges` order. Same display-or-token
matching.
- **`source_sheet_name`** / **`source_table_named_range`** โ where the filled
table lives (defaults `"plate sheet"` / `"plate_table"`).
- **`target_sheet_name`** โ where the grid is drawn (default `"plate layouts"`;
created if missing). The source sheet is moved to sit just left of the layout
sheet.
- **`start_cell`** โ top-left anchor for a *fresh* plate (default `"B2"`). On a
*re-paste* the existing plate's anchor is reused.
- **`append_if_sheet_exists`** โ `True` (default) stacks a new plate **below**
existing plates on the target sheet (using their `_plate_end` markers); `False`
starts at `start_cell`.
### 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
```python
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:
```python
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_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:
- **Taller** new plate (96 โ 384): rows are **inserted** so plates stacked below
are pushed down, not overwritten.
- **Shorter** new plate (384 โ 96): the excess rows are removed with a
**full-row delete** so plates below shift up and stay compact.
> **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`
```python
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:
- **`variables`** โ subset of variables, by display name or token. Default: all.
- **`rows`** โ subset of row labels (`["A", "B"]`). Default: all.
- **`columns`** โ subset of column numbers (`[1, 2, 3]`). Default: all.
- **`return_format`** โ `"wide"` (one row per well, one column per variable) or
`"long"` (one row per well ร variable).
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.
```python
# 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](../concepts/stacked-tables-and-reinit.md). |
| `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
- **Formula injection** โ writes escape leading `= + - @` by default
(`sanitize_formulas=True`). Opt out only for intentional formulas.
- **Overflow** โ placement is rejected *before* writing when a column would land
past XFD, so the workbook is never left half-drawn.
---
## Related
- [Normal tables](normal-tables.md) โ managed tables without a plate grid.
- [Benchling: single table](benchling-single.md) ยท [Benchling: multiple tables](benchling-multiple.md) โ registry-backed tables.
- [Stacked tables & re-init](../concepts/stacked-tables-and-reinit.md) โ the vertical-stack contract and `SideBySideNotSupportedError`.
- [Tools reference](../reference/tools.md) โ full API signatures.
- [Docs index](../index.md)