Reference Cells, Ranges and Tables with xl() in Python in Excel

Python in Excel runs your code in a Microsoft cloud container that cannot see your disk, cannot reach the network and has never heard of pd.read_excel("sales.xlsx"). The workbook it lives in is the one data source it has, and the only door into that workbook is a single function: xl(). Every cell, range, table, name and Power Query result a =PY formula uses comes through it. This guide, part of Using Python in Excel, covers each kind of reference, what Python receives back, and the choices that keep a formula working when the data grows.

Prerequisites

• A Microsoft 365 subscription and Excel build where Python in Excel is available. It needs an internet connection, and it is not offered on device-based licences, shared computer activation, or the iPad, iPhone and Android apps.
• A workbook containing some data. The examples assume a sheet with an Excel table named Sales (columns Date, Region, Rep, Amount), a single-cell named range TaxRate, and a second sheet called Raw data.

To start a Python formula, select a cell and either choose Formulas → Insert Python, or type =PY( and pick the Python entry from AutoComplete. The cell switches into Python editing mode: Enter adds a new line and Ctrl+Enter commits the formula.

Step 1 — Reference a single cell

A one-cell reference returns a plain Python value: a number becomes an int or float, text a str, a logical a bool, and a date arrives as a datetime rather than as Excel's serial number.

rate = xl("B1")          # 0.2 from cell B1
label = xl("A1")         # "Tax rate"
f"{label}: {rate:.0%}"   # returned to the cell as text

While you are editing a Python formula you do not have to type these references. Selecting cells with the mouse inserts the matching xl("…") call at the cursor, which avoids typos in sheet names that contain spaces.

Step 2 — Reference a range, with or without headers

A multi-cell reference returns a pandas DataFrame. Without further arguments, every row is data and the columns are numbered from zero. Pass headers=True and the first row of the range becomes the column names — which is nearly always what you want for a block of records.

raw = xl("A3:D8")                      # columns 0, 1, 2, 3; row 3 is data
sales = xl("A3:D8", headers=True)      # columns Date, Region, Rep, Amount
sales.groupby("Region")["Amount"].sum()

A fixed range has one serious weakness: it does not grow. Add a ninth row of sales under A3:D8 and this formula silently ignores it. That is the main reason to prefer tables, covered next.

Step 3 — Reference an Excel table

Convert the data to a table (Insert → Table, or Ctrl+T), give it a name on the Table Design tab, and reference the whole thing with the [#All] specifier. [#All] includes the header row, and headers=True turns it into column names.

sales = xl("Sales[#All]", headers=True)
by_rep = (
    sales.groupby(["Region", "Rep"], as_index=False)["Amount"]
         .sum()
         .sort_values("Amount", ascending=False)
)
by_rep

Table references are the right default for three reasons. They expand automatically when rows are added, so the formula never misses new data. They survive rows and columns being inserted elsewhere on the sheet, because they point at the table rather than at coordinates. And the table name documents what the data is, which a reference like A3:D8 does not. If you already build tables from Python, the same object is covered from the file side in Create an Excel Table with Python.

Step 4 — Reference other sheets and named ranges

xl() takes the same reference text you would type in an Excel formula, so a range on another sheet uses the usual Sheet!Range syntax, with single quotes around a sheet name that contains spaces. A defined name is referenced by its name alone.

raw = xl("'Raw data'!A1:C200", headers=True)
tax_rate = xl("TaxRate")

sales = xl("Sales[#All]", headers=True)
sales["Net"] = sales["Amount"] / (1 + tax_rate)
sales[["Region", "Amount", "Net"]]

What xl() cannot reference is another workbook. External references such as [Budget.xlsx]Sheet1!A1 are not available, and neither is reading a file from disk inside the formula. Data from another workbook, a CSV file, a database or a web API has to be brought into this workbook first.

Step 5 — Bring external data in through Power Query

Power Query is the supported route for anything outside the workbook. Use Data → Get Data to connect to the file, folder, database or web source, shape it in the Power Query editor, and load it — Close & Load To… → Only Create Connection is enough. The query is then referenceable by its name.

orders = xl("Orders")                       # the Power Query query named Orders
orders["Month"] = orders["OrderDate"].dt.to_period("M").astype(str)
orders.pivot_table(index="Month", columns="Region", values="Amount", aggfunc="sum")

This split of responsibilities is worth embracing rather than resenting. Power Query handles credentials, refresh and the connection itself; Python does the analysis. Refresh the query (Data → Refresh All) and every Python formula that references it recalculates on the new data. For the equivalent pipeline outside Excel entirely, see Export SQL Query Results to Excel with Python.

Step 6 — Check the types you received

The conversion from Excel cells to pandas follows rules set in the workbook's initialization code (Formulas → Initialization), and it is worth confirming what arrived before building on it. Numbers stored as text stay text, blank cells arrive as missing values, and a column mixing numbers and text ends up with the object type.

sales = xl("Sales[#All]", headers=True)
summary = {
    "rows": len(sales),
    "dtypes": sales.dtypes.astype(str).to_dict(),
    "blank amounts": int(sales["Amount"].isna().sum()),
}
summary

Returning a small dictionary or DataFrame like this while you develop a workbook is the Python in Excel version of a debugger print. You can also use print(), whose output appears in the Diagnostics pane rather than in the cell. Cleaning mixed columns once they are in pandas uses exactly the same techniques as anywhere else — Convert Excel Text Columns to Numbers with pandas applies unchanged.

Common pitfalls

The calculation-order row matters more than it appears. Python cells on a sheet run in row-major order — across row 1 from column A, then across row 2, and so on — and sheets run in order too. A helper function defined in cell A10 is not available to a formula in A5. Keeping definitions in a block at the top-left of the first sheet avoids the whole class of problem.

Performance and scale notes

Each reference is serialised and sent to the cloud runtime when the formula calculates, and the data involved in a single calculation is capped at 100 MB. Referencing a whole table of a few thousand rows is quick; referencing entire columns or a sheet's full used range sends far more than the formula needs. Reference the narrowest table or range that contains the data, and when several formulas use the same large table, read it once in an early cell and reuse the variable in later ones rather than calling xl() repeatedly.

For workbooks with many Python cells, switch to Partial calculation while you edit so Python formulas only recalculate when you press F9. That keeps the grid responsive while you work on the code.

Conclusion

xl() is the whole interface between a Python formula and its workbook. Use single-cell references for parameters, headers=True for any block of records, and Table[#All] as the default for data that will grow. Reach other sheets and defined names with ordinary Excel reference syntax, bring anything external in through Power Query, and confirm the types you received before building analysis on them. Keep references narrow and definitions early, and the rest is ordinary pandas.

Frequently asked questions

Can xl() write values back to cells? No. xl() only reads. A Python formula returns its result to its own cell, which can spill into neighbouring cells when you choose Excel values as the output type.

Can I reference a table column directly? Read the table with Table#All and headers=True, then select the column in pandas. That keeps the column names exactly as they appear in the table header.

Why does xl() return a DataFrame for a single row? Any reference with more than one cell returns a DataFrame. Take the first row with .iloc0 if you want a Series, or reference a single cell to get a scalar.

Can I read another open workbook? No. External workbook references are not supported. Load the other workbook's data with Power Query and reference the query by name.

Do references update if I insert rows above the data? Yes, the same way Excel formulas do — the reference text is adjusted. Table references are unaffected by inserted rows elsewhere on the sheet.

Related

• Up one level: Using Python in Excel — how =PY formulas fit alongside scripts that automate Excel from outside.
• Return DataFrames and Values from Python in Excel Cells — the other half of the conversation: getting results back.
• Fix Python in Excel Errors — #PYTHON!, #CALC!, #BUSY! and the rest.
• Reading Excel Files with Pandas — reading the same data from a script instead of a cell.
• Create a Named Range in Excel with openpyxl — defining the names xl() can reference.