Python in Excel: Use the PY Function for Data Analysis

Python with AI Tutorial · Chapter 41 of 48

Python in Excel lets you run Python code directly inside a Microsoft 365 worksheet cell with the =PY() function. The code executes in the Microsoft Cloud, reads your ranges and tables through xl(), and returns a value, a pandas DataFrame or a chart to the grid, so you can combine Excel formulas with pandas and matplotlib without leaving the workbook.

What you need

Python in Excel is part of Microsoft 365 on Windows, Mac and Excel for the web. You do not install anything: the Python runtime, pandas, numpy, matplotlib, seaborn and statsmodels are already there, provided by the Anaconda distribution. If =PY( does not turn green when you type it, your organisation has not enabled the feature or your plan does not include it.

• A Microsoft 365 Business, Enterprise, Family or Personal subscription on the Current Channel.
• An internet connection, because every Python cell is calculated in the cloud.
• Optional: the Python in Excel add-on for faster "premium compute", and Copilot if you want Excel to write the code for you.

Your first =PY() formula

Select a cell, type =PY( and press Tab (or use Formulas > Insert Python). The cell switches to a Python editor. Write the code, then press Ctrl+Enter to run it. The value of the last line becomes the cell result.

sales = [12500, 9800, 14300]
sum(sales)

Output:
36600

Everything you already know from the course works here: variables, lists, functions and imports. Only the way you get data in and out is different.

Reading cells with xl()

The xl() function is the bridge between the grid and Python. Give it a range, a table name or a sheet-qualified address and it returns a pandas DataFrame. Add headers=True when the first row contains column names. Assume this small sales table sits in A1:D7.

df = xl("A1:D7", headers=True)
df["Revenue"].sum()

Output:
150700

Other valid references are xl("A1") for a single cell, xl("Sales[Revenue]") for a table column and xl("Budget!B2:B13") for another sheet. You can also click cells while the editor is open and Excel inserts the xl() call for you.

Returning a DataFrame to the grid

When the last line is a DataFrame, Excel stores it as a Python object by default, shown as a small card in the cell. Press Ctrl+Alt+Shift+M (or use the output menu next to the formula bar) to switch to Excel values, and the frame spills into neighbouring cells like a dynamic array.

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

Output:
Region  Revenue
East    45000
North   61200
South   44500

A Python object can be passed to another Python cell, which is useful for building a pipeline: load and clean in one cell, aggregate in the next, chart in a third.

Pivot tables in one line

pandas does the work that a PivotTable normally does, and the result recalculates whenever the source range changes.

df = xl("A1:D7", headers=True)
df.pivot_table(index="Region", columns="Product",
               values="Revenue", aggfunc="sum", fill_value=0)

Output:
Region  Keyboard  Laptop  Monitor
East           0   33000    12000
North          0   46200    15000
South       6000   38500        0

Charts with matplotlib inside a cell

Plots come back as image objects. Right-click the cell and choose Display Plot over Cells (Ctrl+Alt+Shift+C) to float the chart on the sheet, or leave it in the cell and resize the row.

import matplotlib.pyplot as plt
df = xl("A1:D7", headers=True)
totals = df.groupby("Region")["Revenue"].sum()
totals.plot(kind="bar", title="Revenue by Region", color="#2a6fdb")
plt.ylabel("Revenue")
plt.tight_layout()

Output:
[Image] bar chart with three bars: East 45000, North 61200, South 44500

I am using Python in Excel. My data is in A1:D7 with headers Region, Product, Units, Revenue. Write the code for a =PY() cell that draws a stacked bar chart of Revenue by Region split by Product using matplotlib, with a legend and a title. Use xl("A1:D7", headers=True) to read the data.

Mixing Python cells with Excel formulas

A Python cell set to Excel values behaves like any other number, so ordinary formulas can reference it. Here F2 returns the average order value and G2 checks it with IF.

df = xl("A1:D7", headers=True)
round(float(df["Revenue"].sum() / df["Units"].sum()), 2)

Output:
449.85

In G2:  =IF(F2>400, "Above target", "Below target")   ->  Above target

Python cells calculate in row-major order: left to right, top to bottom, sheet by sheet. Put a cell that defines a variable above and to the left of the cells that use it.

Copilot in Excel writes the Python for you

With a Copilot licence, open the Copilot pane, select your table and ask a question such as "forecast next quarter revenue by region". Copilot chooses Advanced analysis, writes the Python into new =PY() cells on a fresh sheet, runs it and explains the result. You can open every generated cell, read the code and edit it, which makes it a good way to learn pandas idioms. Always check the columns it picked: Copilot occasionally treats a text column as a category when you wanted a number.

Translate this Excel formula into pandas code for a Python in Excel cell. The table is in A1:D7 with headers Region, Product, Units, Revenue and is read with xl("A1:D7", headers=True). Formula: =SUMIFS(D:D, A:A, "North", B:B, "Laptop"). Return only the number.

Limitations to know before you start

• Cloud execution. Code and the referenced data are sent to a Microsoft Cloud container. Check your company data policy before using it on sensitive workbooks, and expect a short delay on every recalculation.
• No pip. You can only import libraries in the curated Anaconda set. pip install and custom packages are not available; use the Python Editor task pane (Excel Labs) or a local script instead.
• No local files or network. open(), requests and database drivers cannot reach your PC or the internet. Data must come from the workbook through xl() or a Power Query connection.
• Licence. Standard compute is included in Microsoft 365; the Python in Excel add-on unlocks faster premium compute. Excel 2021 and other perpetual versions do not have the feature.
• One-way results. A Python cell cannot write to other cells, change formatting or run macros; it only returns a value or object.

Common mistakes

• Forgetting headers=True, so the column names become row 0 and the numbers arrive as text.
• Leaving the output as a Python object and wondering why =SUM() over it returns an error. Switch to Excel values.
• Using a variable defined in a cell that is below or to the right of the current one; the calculation order means it does not exist yet.
• Expecting print() to show something. Only the last expression is returned; print output goes to the diagnostics pane.
• Trying to import requests or read a CSV from your Downloads folder. The cloud sandbox cannot see them.

Exercise

Using the A1:D7 table above, write a =PY() cell that returns a DataFrame with each product, its total units and its average revenue per unit, sorted from highest to lowest revenue per unit.

df = xl("A1:D7", headers=True)
out = df.groupby("Product").agg(Units=("Units", "sum"), Revenue=("Revenue", "sum"))
out["PerUnit"] = (out["Revenue"] / out["Units"]).round(2)
out.drop(columns="Revenue").sort_values("PerUnit", ascending=False).reset_index()

Output:
Product   Units  PerUnit
Laptop      107  1100.00
Monitor     108   250.00
Keyboard    120    50.00

Related chapters

• Python pandas: DataFrames explained
• Read and write Excel files with pandas
• Python Matplotlib charts
• Python with AI course hub

FAQ

Is Python in Excel free?

It is included with a Microsoft 365 subscription at standard compute speed. A paid Python in Excel add-on gives faster premium compute, and Copilot features need a Copilot licence.

Can I install extra libraries with pip in Python in Excel?

No. Only the libraries shipped in the Anaconda distribution for Excel are available, which already covers pandas, numpy, matplotlib, seaborn, statsmodels and scikit-learn.

Does Python in Excel work offline?

No. Every Python cell is calculated in the Microsoft Cloud, so you need an internet connection, and the referenced cell data leaves your machine during calculation.

Working with spreadsheets too? Ready-made Excel, Google Sheets and Power BI templates are at NextGenTemplates.com.

Chapter 41 of 48 · Python with AI: all 48 chapters