The xl() helper — getting Excel data into Python

The xl() forms

xl("A1")                       # single value
xl("A1:C5")                    # range → DataFrame (or Series if one col)
xl("A1:C5", headers=True)      # treat first row as headers
xl("Sheet2!A1:C100")           # range from another sheet
xl("MyTable[#All]")            # an Excel Table by name
xl("MyTable[Revenue]")         # one column of a Table

Single cells

=PY(
tax_rate = xl("B1")
amount   = xl("B2")
amount * (1 + tax_rate)
)

Range with headers

Best practice: lay your data out with headers in the first row, then read with headers=True:

=PY(
df = xl("A1:D100", headers=True)
df.groupby("Region")["Revenue"].sum()
)

Cross-sheet references

=PY(
df = xl("RawData!A1:F1000", headers=True)
df.head()
)

Excel Tables — the cleanest way

If your data is a real Excel Table (Ctrl+T on a range), you can reference it by name. That's the most readable option and survives row insertions/deletions:

=PY(
sales = xl("SalesTable[#All]", headers=True)
sales["Total"] = sales["Qty"] * sales["Price"]
sales
)

Walkthrough: build a small dashboard cell

Region      Quarter   Revenue
North       Q1        1200
North       Q2        1500
South       Q1         900
South       Q2        1100

=PY(
df = xl("A1:C5", headers=True)
df.groupby("Region")["Revenue"].agg(["sum", "mean"])
)