Excel ↔︎ Python type conversion

Exact conversion behavior between Excel cells, Boardflare notebook Python values, BF.OUTPUT, and BF.FUNCTION, with a downloadable compatibility harness comparing Microsoft Python in Excel.

Boardflare’s notebook conversion rules are designed to match the ordinary Excel-value behavior of Microsoft Python in Excel wherever that is practical through the Office custom-function boundary.

The canonical Boardflare Notebook Reference defines the authoritative type conversion contracts and API signatures. This page provides comparative analysis, design rationale, and verification against Microsoft Python in Excel:

what Boardflare workbook inputs return for Excel cells and ranges;
what BF.OUTPUT() and BF.FUNCTION() return to Excel for Python values;
pandas and NumPy shape rules;
dates, times, missing values, and Excel errors;
the intentional cases where Boardflare does not copy Microsoft behavior;
the executable workbook used to verify the behavior end to end.

Download the executable type-conversion harness (.xlsx)

Verification basis

The edge cases on this page were verified in Excel with the executable harness on August 17, 2026. The harness runs Microsoft xl() / PY() and Boardflare workbook-input / BF.OUTPUT() paths against matching cases in the same workbook. Microsoft and Office behavior can change, so the workbook remains the executable compatibility reference rather than treating undocumented edge cases as permanent platform guarantees.

The four conversion paths

The harness tests four independent paths:

Excel --xl()----------------------> Microsoft Python in Excel
Excel --bf.inputs----------------> Boardflare notebook Python

Microsoft Python --PY(..., 0)-----> Excel values / spills
Microsoft Python --PY(..., 1)-----> Excel Python-object cards
Boardflare Python --bf.publish----> BF.OUTPUT(...) -> Excel values / spills

Boardflare’s comparison target on output is PY(..., return_type=0), which returns Excel values. Microsoft documents return_type=1 as returning a Python object; those Python-object cards are a Microsoft Python-in-Excel mechanism and are not emulated by BF.OUTPUT().

Canonical conversion specifications

The authoritative developer contracts for type conversion are defined in the canonical reference:

Excel → Python (bf.inputs): scalar mapping, blank handling, headers=True/headers=False DataFrame conversion, and ExcelErrorValue error preservation.
Python → Excel (BF.OUTPUT): scalar mapping, missing values, dates/times, 1D/2D lists, NumPy arrays, pandas Series, DataFrame header/index layout, and rich-value entities.
Function Arguments & Returns (BF.FUNCTION): positional arguments, trailing defaults, and return value conversion.

The remainder of this page documents the verification methodology against Microsoft Python in Excel, the design rationale behind intentional differences, and the executable compatibility harness.

Excel → Python with `bf.inputs()`

When declaring workbook dependencies via bf.inputs(), Boardflare converts Excel cell types to idiomatic Python equivalents.

Comparison with Microsoft `xl()`

The harness tests scalar and range inputs across both environments:

Scalars: Blank cells become None, text becomes str, numbers become int or float, booleans become bool, and dates/times become datetime.datetime or datetime.time.
Ranges: Multi-cell ranges become pandas DataFrame objects. When headers=True is specified, the first row becomes column names; otherwise columns are integer indices.

Excel errors are intentionally inspectable

This is an intentional design difference from Microsoft xl().

In Microsoft Python, an input cell containing a worksheet error propagates through the xl() probe and can disrupt execution. Boardflare instead returns a typed ExcelErrorValue object (e.g. ExcelErrorValue('#N/A')). This preserves the difference between an actual worksheet error and literal text containing "#N/A", allowing notebook code to inspect, handle, or republish the error without failing upstream cells.

Python → Excel with `BF.OUTPUT()` and `BF.FUNCTION()`

When returning Python values to Excel via BF.OUTPUT() or BF.FUNCTION(), Boardflare targets the Excel-value behavior of PY(..., return_type=0).

Scalars & Missing Values: None is returned as a formatted value displaying None (preserving cell formatting), numbers and booleans return native Excel types, and NaN/inf map to standard Excel error codes (#NUM!, #N/A).
Dates & Times: naive datetime.datetime, datetime.date, and datetime.time return formatted Excel date/time serials. Timezone-aware datetimes return #VALUE!.
Sequences & Arrays: 1D lists/tuples and 1D NumPy arrays produce vertical spills; 2D rectangular sequences and 2D arrays produce 2D spills.
DataFrames: DataFrames return 2D spills with column headings. Indexes are included when named or non-numeric.

For complete shape and type conversion rules, see Output layout & conversion contract.

Intentional differences from Microsoft Python in Excel

Compatibility is not useful when copying a behavior would silently lose information or create a nondeterministic worksheet result.

Integers outside the exact Excel/JavaScript range

Boardflare rejects integers outside:

-9,007,199,254,740,991
+9,007,199,254,740,991

Microsoft Python in Excel can return a larger Python integer as an Excel number, but Excel’s numeric representation cannot preserve every integer beyond this range exactly. Boardflare therefore rejects it instead of silently changing digits. Return the value as text when exact digits matter.

Python sets

Boardflare rejects set results because set iteration order is not a stable worksheet contract. Convert a set to an explicitly ordered list or tuple when spill order matters.

Python-object cards

Microsoft PY(..., return_type=1) can keep supported Python objects in worksheet cells as Python-object cards. BF.OUTPUT() is an Office custom function and returns worksheet values rather than generic Python objects.

Top-level rich Arrays

A normal Python rectangular sequence is the supported way to return a spill. Boardflare rejects a top-level Excel rich Array value because that data-type shape is not supported as a top-level Office custom-function return. An Array can still be nested inside a supported Entity property.

Excel rich values

Boardflare additionally supports selected Excel rich-value dictionaries beyond the ordinary PY Excel-value compatibility target, including:

formatted Double values with properties;
formatted Boolean values with properties;
formatted String values with properties;
Entities;
native Excel errors;
an Entity property containing a nested Array.

A rich Error dictionary is converted to a native custom-function Excel error instead of attempting to return an enhanced Error data-type object directly.

`BF.FUNCTION()` arguments are a separate boundary

The harness primarily compares Boardflare workbook-input and Python results. Worksheet arguments passed to a published function have their own deliberately simple contract:

scalar worksheet values remain scalars;
a one-cell range is unwrapped to a scalar;
a multi-cell range becomes a two-dimensional Python list;
omitted optional arguments use their Python defaults.

The result returned by the Python callable then uses the same Python → Excel rules documented above.

The executable compatibility harness

Download the Excel ↔︎ Python type-conversion harness

The workbook uses the same Excel source cells for Microsoft and Boardflare so differences are attributable to the conversion path rather than different test data.

It contains seven sheets:

Sheet	Purpose
`README`	In-workbook instructions and coverage notes
`Excel Sources`	Shared scalar, date/time, formula, error, and range fixtures
`PY Excel to Python`	Python types produced by Microsoft `xl()`
`PY Python to Excel`	Microsoft `PY(..., return_type=0)` and object-card probes
`BF Excel to Python`	Python values produced by Boardflare workbook inputs
`BF Python to Excel`	`bf.publish()` → `BF.OUTPUT()` results
`Coverage Matrix`	Index of the conversion families under test

Running it

You need an Excel build with Microsoft Python in Excel and the current Boardflare Python add-in.

Download and open the harness in Excel.
Allow the Microsoft Python-in-Excel cells to calculate.
Open/start the Boardflare notebook embedded in the workbook.
Wait for the Boardflare workbook input to hydrate and for the bf.publish() registry to become ready.
Compare the green result regions on the Microsoft and Boardflare sheets.
Recalculate if required by your Excel calculation mode.
Save a separate calculated copy if you want to inspect the exact saved workbook state.

Some cells intentionally return Excel errors. Those errors are test outcomes, not necessarily failures.

What the harness verifies

The workbook compares:

Python type and repr
scalar worksheet values
Excel error codes
spill orientation and dimensions
DataFrame/Series headings and index behavior
date/time values and number formats
missing-value behavior
ragged-array behavior
rich-value behavior

Every remaining difference should be intentional and documented on this page.

Microsoft references

The Microsoft mechanisms being compared are documented here:

Microsoft documents the broad conversion and custom-function mechanisms, while several edge cases above are empirical results from the executable harness and therefore remain explicitly tested rather than inferred from documentation alone.