Working with Excel

Read reactive workbook data with bf.inputs, return selected results with BF.OUTPUT, expose reusable Python with BF.FUNCTION, and understand supported values and limits.

Excel and the notebook are complementary surfaces. Use one displayed bf.inputs() widget for reactive workbook ranges/names. Publish only the results or reusable calculations that Excel actually needs to consume.

Read workbook data

Use bf.inputs(...) to declare the Excel data a notebook depends on. Boardflare watches those references and updates the widget’s values when the workbook changes.

Declare inputs once

import boardflare as bf

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

Then read the synchronized values by name in downstream cells:

sales = inputs["sales"]
tax_rate = inputs["tax_rate"]

Prefer one clear upstream input registry for a related analysis rather than scattering workbook references throughout many cells.

Use mapping access such as inputs["sales"], not attributes such as inputs.sales.

ImportantDisplay the bf.inputs() result

The displayed Anywidget model owns the live workbook connection. Do not hide it in an assignment-only cell or replace it with a private bridge API.

Reference cells and ranges

A plain reference string is enough when no options are required:

inputs = bf.inputs(threshold="Controls!B4")
inputs

Use bf.ref() when you need options such as headers:

inputs = bf.inputs(
    transactions=bf.ref("Data!A1:G500", headers=True),
)
inputs

Boardflare resolves supported workbook reference forms through the spreadsheet host, including A1 references, sheet-qualified ranges, and defined names. In multi-sheet workbooks, prefer sheet-qualified references so the dependency is obvious.

Understand returned values

Workbook reference Python value
One cell Scalar
Multi-cell range pandas DataFrame
Multi-cell range with headers=True DataFrame with the first row used as columns
Empty cell May materialize as None
bf.selection("Orders") DataFrame with the table headers and at most one row: the row at the user’s active cell (empty when the active cell is not on a row of that table)

For example:

inputs = bf.inputs(
    scenario="Control!B2",
    orders=bf.ref("Orders!A1:E200", headers=True),
)
inputs

inputs["scenario"] is a scalar, while inputs["orders"] is a DataFrame.

Read a table selection

Pass bf.selection("Orders") to bf.inputs(...) to read the row the user is on in an Excel table:

inputs = bf.inputs(
    picked=bf.selection("Orders"),
)
inputs["picked"]

The value is a pandas DataFrame with the table headers as columns and at most one row: the row of the user’s active cell, the cell the cursor is on. Moving the cursor within a highlighted range changes the row. When the active cell is outside the table’s body rows or columns, on another sheet, on the header or total row, or on a hidden or filtered row, the value is an empty DataFrame with those columns, never None.

Two limits to know about:

  • The cursor must be on a table cell. Cells beside the table select no row.
  • Filtering that hides the active row does not refresh the value until the user makes another selection.

The value is transient: it empties when the user clicks any cell outside the table or leaves the table’s sheet. Use it to drive what the notebook pane shows, not values you publish to cells. Match rows by column values (for example picked["id"]), not by index. See Workbook Design: Table Selection for details.

Let reactivity do the refresh work

The notebook is reactive. When Excel changes a declared dependency, Boardflare refreshes that input and marimo reruns cells that depend on it.

You normally should not build manual “refresh all Python” logic. Structure cells around explicit variables and dependencies instead.

During initial startup, Boardflare waits for the first workbook snapshot before allowing dependent calculations to proceed. This prevents downstream cells from publishing plausible-looking results based on an uninitialized placeholder.

Inspect input errors

Use:

inputs.errors

After initialization, reading an input that failed to resolve raises instead of silently returning a placeholder value.

Common causes include:

  • a renamed or deleted worksheet;
  • a mistyped reference;
  • an invalid defined name;
  • a range shape that no longer matches the notebook’s assumptions.

Name inputs for the analysis

Use Python names that describe the business role of the workbook data:

inputs = bf.inputs(
    actuals=bf.ref("Actuals!A1:F50", headers=True),
    assumptions=bf.ref("Assumptions!A1:B12", headers=True),
    tolerance="Controls!B3",
)
inputs

That makes downstream code easier to read than repeating workbook coordinates throughout the notebook.

Return results to Excel

Notebook results can stay in the notebook. When Excel needs a selected result, publish it once and consume it with BF.OUTPUT().

Publish a completed value

Suppose an upstream cell produces a summary table:

summary = [
    ["Metric", "Value"],
    ["Revenue", revenue],
    ["Gross Margin", gross_margin],
]

Publish it:

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

Then use the result in Excel:

=BF.OUTPUT("summary")

The value spills into the worksheet when it is a supported array/table shape.

ImportantDisplay the publication widget

Keep the bf.publish() result displayed. Its Anywidget model owns the live output registry used by worksheet formulas.

Publish several results together

Treat bf.publish() as the notebook’s explicit public registry:

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

Then worksheet consumers can select only what they need:

=BF.OUTPUT("forecast")
=BF.OUTPUT("exceptions")

Keeping the complete registry in one downstream cell makes it easy to inspect what the notebook exposes to Excel.

See which worksheet formulas consume a publication

A displayed publication also tracks active worksheet consumers. Expand the publication widget to see the worksheet reference, published name, and whether the consumer uses an output or function.

If you assigned the publication to a variable, inspect the same mapping from Python:

publication.consumers
publication.consumers["outputs"]
publication.consumers["functions"]

This is useful when changing or removing a published name because it shows which worksheet formulas are currently connected to that registry. In Excel, the references follow live streaming subscriptions and identify the formula anchor cell rather than an entire spill footprint.

Consumer tracking is runtime state. It is not stored as part of the notebook source and should not be treated as a complete static dependency audit for a closed workbook.

What BF.OUTPUT() is for

Use it when a notebook has already performed the expensive or multi-step work and the workbook needs the finished value for:

  • a report section;
  • a reconciliation table;
  • downstream worksheet formulas;
  • review or sign-off;
  • a dashboard or schedule maintained in Excel.

You do not need to publish every intermediate DataFrame or chart. Keep analysis detail in the notebook unless the workbook actually needs it.

Startup behavior

BF.OUTPUT() is a streaming Excel custom function. When the saved notebook is still starting, Excel can show its normal #BUSY! state. Boardflare waits for the notebook to establish the live publication registry rather than returning a temporary placeholder value.

If #BUSY! never resolves, open Troubleshooting and FAQ and confirm that the saved notebook starts successfully and the displayed bf.publish() cell runs without error.

Output shapes

Published values are normalized to worksheet matrices:

  • a scalar returns one cell;
  • a one-dimensional sequence returns a vertical spill;
  • a rectangular two-dimensional sequence returns a spill range;
  • a ragged two-dimensional sequence is padded with #N/A;
  • supported pandas and NumPy values follow the same Excel-value layout rules verified against Microsoft Python in Excel.

See Workbook Design: Output Layout & Conversion Contract for the complete output contract and the Excel ↔︎ Python type conversion harness for edge-case comparisons.

Save the source that recreates the result

Published Python objects are live-session state. Saving the workbook stores notebook source, not the current in-memory output registry.

When the workbook opens again, Boardflare starts the saved notebook, reruns the reactive analysis, and recreates the publication registry. That is why workbook formulas can remain connected without serializing Python objects into the file.

Create Excel functions

Use BF.FUNCTION() when a calculation should have one centralized Python implementation while workbook users call it from ordinary worksheet cells.

Define the Python function

def discount(price, rate=0.0):
    return float(price) * (1 - float(rate))

Publish it explicitly:

publication = bf.publish(
    functions={
        "discount": discount,
    },
)
publication

Then call it from Excel:

=BF.FUNCTION("discount", A1, B1)

The function name is looked up in the explicit mapping passed to bf.publish(). Boardflare does not discover arbitrary notebook globals or use eval.

Publish values and functions together

A normal notebook often exposes both:

publication = bf.publish(
    outputs={
        "forecast": forecast,
    },
    functions={
        "discount": discount,
        "scenario_value": scenario_value,
    },
)
publication

This keeps the notebook’s Excel-facing contract visible in one place.

Keep expensive work in notebook cells

BF.FUNCTION() is best for relatively short calculations or lookups whose implementation should be centralized in Python.

For expensive modeling, simulation, large transformations, or slow API work:

  1. perform the expensive work in ordinary reactive notebook cells;
  2. publish the completed result with BF.OUTPUT(), or publish a lightweight function that looks up values from the already-computed model.

A long synchronous function can block the notebook kernel until it returns or reaches the worksheet timeout.

Supported function shapes

Common supported signatures include:

def required(a, b):
    ...

def optional(a, b=10):
    ...

def variable(a, *items):
    ...

async def async_lookup(code):
    ...

Worksheet calls are positional. Required keyword-only parameters and **kwargs are not part of the current worksheet function contract.

Range arguments

Worksheet arguments are converted before Python receives them:

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

Return values follow the same worksheet conversion families as published outputs. See Workbook Design: Output Layout & Conversion Contract.

Why this matters

Without a centralized implementation, a complex calculation can be repeated or re-expressed in many worksheet locations. BF.FUNCTION() lets the implementation live once in the notebook while Excel remains the place where workbook users invoke it.

That is useful for algorithms, valuation logic, scoring, normalization, specialized numerical calculations, and other logic that is awkward to maintain as a long worksheet formula.

Canonical Reference & Limits

The exact API signatures, data conversion rules, runtime limits, and troubleshooting steps are maintained in the canonical Boardflare Notebook Reference: