Excel ↔ Python type conversion

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.

This page is the maintained reference for:

• what bf.inputs() returns 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)

:::info 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 bf.inputs() / BF.OUTPUT() 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().

Excel → Python with bf.inputs()

Declare the workbook values your notebook depends on:

import boardflare as bf

inputs = bf.inputs(
    tax_rate="Assumptions!B4",
    sales=bf.ref("Sales!A1:D500", headers=True),
)
inputs

Keep the returned inputs widget displayed as the Marimo cell result. Boardflare waits for the first real workbook snapshot before downstream cells run, then refreshes the input reactively when the referenced workbook cells change.

Scalar conversion

Excel source	Microsoft xl() in the harness	Boardflare bf.inputs()
Truly blank cell	None	None
Formula or explicit empty string	""	""
Integer-valued number	Python integer when materialized as an integer	Same target semantics
Decimal	float	float
Boolean	bool	bool
Text / Unicode	str	str
Date-formatted serial	midnight datetime.datetime	midnight datetime.datetime
Date + time	datetime.datetime	datetime.datetime
Time-formatted fraction	datetime.time	datetime.time
Elapsed time such as 1.5 days	observed as datetime.time(12, 0)	same target behavior

The elapsed-time case is worth noting: a scalar datetime.time has no whole-day component, so an Excel duration of 1.5 days is observed as noon rather than as a 36-hour Python duration. The harness keeps this case explicit because it is easy to assume otherwise.

Boardflare also removes insignificant floating-point noise around whole-second date/time boundaries. An Excel value displayed as 13:45:30, for example, should not become 13:45:30.000001 merely because of the serial's binary/decimal representation.

Excel errors are intentionally inspectable

This is the principal ordinary-input difference from Microsoft xl().

A scalar Excel error such as #N/A propagates through the Microsoft xl() probe. Boardflare instead returns a typed ExcelErrorValue:

ExcelErrorValue('#N/A')

That preserves the difference between an actual worksheet error and literal text containing "#N/A". Notebook code can inspect, handle, or republish the error without forcing unrelated reactive notebook cells to fail. Returning the typed error through Boardflare maps it back to the corresponding native Excel error.

Range conversion

A multi-cell input becomes a pandas DataFrame.

Range behavior	Boardflare result
Multi-cell range	pandas.DataFrame
headers=True	First row becomes DataFrame column names
Text column	normalized to object dtype
Date/datetime column	normalized to datetime64[ns]
Blank inside an object/text column	Python None rather than pandas NaN

These dtype normalizations reproduce the behavior observed from Microsoft xl() in the harness rather than leaving the result to version-specific pandas inference.

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

A notebook publishes selected values explicitly:

publication = bf.publish(outputs={
    "summary": summary,
    "forecast": forecast,
})
publication

The worksheet consumes them with:

=BF.OUTPUT("summary")

Published Python functions use the same result conversion contract when called through BF.FUNCTION().

Scalars and missing values

Python value	Excel result
None	Excel value displayed as None
bool	Boolean
safe int	Excel number
finite float	Excel number
str / Unicode	Excel text
float('nan') / NumPy NaN	#NUM!
float('inf') / float('-inf')	#NUM!
pd.NA	#N/A
pd.NaT	#NUM!
complex scalar	#N/A

None is returned using an Excel formatted value whose basic numeric value is 0 and whose number format displays None. This mirrors the Excel-value behavior observed from Python in Excel rather than returning an empty cell.

Dates, times, and durations

Boardflare returns date/time values as formatted Excel numeric values, so both the underlying Excel serial and the display format cross the custom-function boundary.

Python value	Worksheet representation
datetime.date	formatted Excel date
naive datetime.datetime	formatted Excel date/time
datetime.time	formatted Excel time
datetime.timedelta	Excel day fraction
timezone-aware datetime	#VALUE!
timezone-aware time	#VALUE!

The workbook's 1900/1904 date system is supplied to the converter, rather than assuming the 1900 system for every workbook.

Lists, tuples, and NumPy arrays

Python value	Excel-value result
1-D list / tuple	vertical spill
rectangular 2-D sequence	rectangular spill
ragged 2-D sequence	padded with #N/A to a rectangle
empty sequence	#VALUE!
mixed scalar/row sequence	invalid-shape error
NumPy scalar	scalar result
NumPy 0-D ndarray	#VALUE!
NumPy 1-D ndarray	vertical spill
NumPy 2-D ndarray	rectangular spill
NumPy 3-D+ ndarray	first two dimensions form a grid whose cells are #VALUE!

The harness includes a (2, 2, 2) ndarray case. Microsoft PY and Boardflare both produce a 2×2 grid of #VALUE! cells because each value remaining after the first two dimensions is itself still an array.

pandas Series

A Series is returned as one column:

• an unnamed Series returns its values;
• a named Series prepends the Series name as a heading row;
• missing values use the scalar missing-value rules above.

pandas DataFrames

A DataFrame returned to Excel always includes its column headings.

Its index is included when either:

1. the index has a name; or
2. the index values are nonnumeric.

The observed layouts are:

unnamed numeric index
---------------------
A      B
1      x
2      y

named numeric index
-------------------
       A      B
row
10     1      x
20     2      y

unnamed string index
--------------------
       A      B
r1     1      x
r2     2      y

The separate row containing a named index is deliberate. It reproduces the Excel-value layout observed from Microsoft Python in Excel rather than placing the index name in the top-left column-heading cell.

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 bf.inputs() 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 bf.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.

1. Download and open the harness in Excel.
2. Allow the Microsoft Python-in-Excel cells to calculate.
3. Open/start the Boardflare notebook embedded in the workbook.
4. Wait for bf.inputs() to hydrate and for the bf.publish() registry to become ready.
5. Compare the green result regions on the Microsoft and Boardflare sheets.
6. Recalculate if required by your Excel calculation mode.
7. 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:

• PY function
• Get started with Python in Excel
• Python in Excel DataFrames
• Custom functions and data types
• Handle and return errors from a custom function

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.