Py LearnPythonForExcelThe Excel-user's path to Python
Home › Course › Module 08 › Pandas in =PY() — the killer combination

Pandas in =PY() — the killer combination

Module 08 · Python inside Excel — =PY()9 min readIntermediate

What you'll learn

  • Group, pivot, and aggregate in =PY()
  • Sort and filter in =PY()
  • Spill the result into a clean Excel range

The setup

Put this raw data in A1:D11 (10 rows + headers):

Date         Region   Product   Revenue
2026-01-05   North    Widget    1200
2026-01-12   North    Gadget     800
2026-01-19   South    Widget     950
2026-02-02   South    Gadget    1100
2026-02-09   East     Widget    1300
2026-02-16   East     Gadget     700
2026-03-03   North    Widget    1500
2026-03-10   South    Widget    1050
2026-03-17   East     Gadget     900
2026-03-24   North    Gadget    1250

Pivot by Region × Product

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

Group by month

=PY(
df = xl("A1:D11", headers=True)
df["Month"] = pd.to_datetime(df["Date"]).dt.to_period("M")
df.groupby("Month")["Revenue"].sum()
)

Top N

=PY(
df = xl("A1:D11", headers=True)
df.nlargest(3, "Revenue")
)

Filter

=PY(
df = xl("A1:D11", headers=True)
df[df["Region"] == "North"]
)

Walkthrough: build a region × month sales matrix

The =PY() cell

=PY(
df = xl("A1:D11", headers=True)
df["Month"] = pd.to_datetime(df["Date"]).dt.strftime("%Y-%m")
m = df.pivot_table(index="Region", columns="Month",
                   values="Revenue", aggfunc="sum", fill_value=0)
m
)

Spill it

Right-click → Output as → Excel values. You get a clean Region × Month matrix that updates whenever the source data changes.

📝 Pandas is already imported
Inside =PY(), pandas is already available as pd by default. You don't need an import pandas as pd at the top — though it's harmless if you add it.

Key takeaways

  • =PY() gives you the full power of pandas inside a cell.
  • Groupby, pivot, sort, filter — all one-liners.
  • Spill the result as Excel values when you want to use it elsewhere.

Build your own pivot

Using the table above, write a =PY() that returns total Revenue by Product, sorted descending.

📹 Video walkthrough
A video walkthrough of this lesson will be embedded here. Until then, the written walkthrough above mirrors what the video will cover step-for-step.