---
title: "Build a Monthly Report Card in a Single Python in Excel Cell"
date: 2026-06-30T00:00:00Z
updated: 2026-06-27T20:52:13Z
tags: ["Python", "Excel", "Visualization", "Efficiency"]
canonical: https://bianca.codes/blog/build-monthly-report-card-python-in-excel/
---

# Build a Monthly Report Card in a Single Python in Excel Cell

_Python in Excel can't write a file, but it can turn a transaction table into a chart that lives in the workbook and never goes stale - in one =PY() cell._

You have this month's transactions sitting in an Excel table. By the end of this post you'll have one `=PY()` cell that turns them into a titled, labelled revenue-by-region chart - the report card - that lives in the workbook and redraws the moment a number changes. You need Microsoft 365 with Python in Excel (Current Channel, on Windows or Mac) and your data as an Excel table. There's nothing to install: pandas and seaborn ship with the feature and run in Microsoft's cloud.

One honest boundary before you start. Python in Excel can't write a file to disk - openpyxl isn't even in the box - so this is not a script that emails an `.xlsx`. The output lives in the grid. That's the trade: you give up file export, and you get a chart that travels with the workbook and never goes stale.

## Why one cell and not a pivot chart

A pivot chart needs the pivot, a chart object, and a refresh you have to remember. A standalone Python script needs an environment the recipient doesn't have. `=PY()` collapses all of that into a formula that sits next to the data and recomputes when you edit it. The cost is that it runs in the cloud and hands back an image rather than a file. For a recurring internal report card, that's the right side of the trade.

## Step 1: Put the transactions in a table and reach them with xl()

Select your data and press Ctrl+T to make it an Excel table, named `Sales`. Then in an empty cell type `=PY` and choose PY from the AutoComplete menu (or use Formulas \> Insert Python). The cell switches into Python mode. Pull the table in:

```python
sales = xl("Sales[#All]", headers=True)
```

`xl()` is how Python sees the grid. `[#All]` with `headers=True` hands pandas a DataFrame with named columns instead of positional ones. If you only wanted one column, `xl("Sales[Revenue]")` returns it as a Series.

## Step 2: Aggregate with pandas - the same groupby, now in the grid

```python
by_region = sales.groupby("Region")["Revenue"].sum().sort_values(ascending=False)
total = by_region.sum()
```

This is the same line you'd write in a standalone script. The only difference is the data arrived through `xl()` instead of `read_csv`. Press Alt+Enter to add new lines inside the one cell - you're still in a single formula.

## Step 3: Draw the report card with seaborn

```python
import seaborn as sns
import matplotlib.pyplot as plt

ax = sns.barplot(x=by_region.index, y=by_region.values, color="#1F4E78")
ax.set_title(f"Revenue by Region - June 2026  (Total ${total:,.0f})")
ax.set_xlabel("")
ax.set_ylabel("Revenue ($)")
ax.bar_label(ax.containers[0], fmt="${:,.0f}")
plt.tight_layout()
```

seaborn and matplotlib are already imported under `sns` and `plt`, so the import lines are for clarity and you can drop them. The total folded into the title and the value labels sitting on each bar are what make this read as a report someone built, rather than a default chart. Navy bars, no axis clutter, the number you actually care about up top.

## Step 4: Return it as a chart and put it over the grid

Commit the cell. Because the code drew a plot, Python in Excel returns an image object - a card icon labelled "Image". Click the card to preview it. To turn it into a real, resizable chart on the sheet, right-click the cell and choose **Display Plot over Cells** (Ctrl+Alt+Shift+C). The chart floats over the grid; drag the corner nodes to size it.

By default a `=PY()` cell returns a Python object. The output toggle next to the formula bar (Ctrl+Alt+Shift+M) switches a cell to **Excel Value** if you'd rather drop the image inside a single cell - but for a chart, Display Plot over Cells is the one you want.

## The whole cell

```python
sales = xl("Sales[#All]", headers=True)

by_region = sales.groupby("Region")["Revenue"].sum().sort_values(ascending=False)
total = by_region.sum()

ax = sns.barplot(x=by_region.index, y=by_region.values, color="#1F4E78")
ax.set_title(f"Revenue by Region - June 2026  (Total ${total:,.0f})")
ax.set_xlabel("")
ax.set_ylabel("Revenue ($)")
ax.bar_label(ax.containers[0], fmt="${:,.0f}")
plt.tight_layout()
ax
```

One cell, top to bottom. Change a figure in the `Sales` table and the card redraws on recalculation. Next month, point `xl()` at the new rows and update the title - everything between holds.

## Common mistakes

### **Expecting a file at the end.**

Python in Excel runs in a sealed Microsoft cloud container: no disk access, no openpyxl, no `to_excel` to a path. The report card is an image in the workbook, not a file you generate and attach. If a styled `.xlsx` on disk is the actual requirement, that's standalone Python's job, not `=PY()`'s - and worth being clear about before you build the wrong thing.

### **Referencing the table without headers=True.**

`xl("Sales[#All]")` on its own hands pandas a frame with integer column names, so `groupby("Region")` raises and the cell shows `#PYTHON!` or `#CALC!`. Pass `headers=True`, or reference the column directly with `xl("Sales[Revenue]")` when you only need one.

### **Putting the Python cell above the data it reads.**

Python in Excel calculates in row-major order - top to bottom, left to right. A Python cell that references another Python cell, or a spilled result, has to sit below it or to the right, otherwise it computes against nothing. Keep the report card cell beneath its inputs.

## Frequently Asked Questions

### **Do I need to install Python, pandas, or seaborn?**

No. Python in Excel runs in the cloud on a curated Anaconda distribution, and pandas, numpy, matplotlib, seaborn, and statsmodels are imported for you. There's nothing to install and no environment to manage - if you have the feature, you have the libraries.

### **Can I send this to someone who doesn't have Python in Excel?**

They'll see the last chart and values saved in the workbook, but they can't recalculate them. On unsupported platforms the Python cells display the cached result and error if forced to run. If you used Display Plot over Cells, the floating chart image survives as something they can at least look at.

### **Where does my data go when the cell runs?**

The data you reference through `xl()` is sent to a Microsoft Cloud container, executed there, and the result comes back to the grid. The Python code has no internet access and can't reach your local files - it only sees what you pass it.

### **Which libraries can I use?**

The Anaconda core set: pandas, numpy, matplotlib, seaborn, statsmodels, and more. Import any of them at the top of a cell. You can't `pip install` arbitrary packages - the available set is fixed by the runtime.

### **Can I get the numbers out as a table, not just the chart?**

Yes. In a separate cell below the chart, return `by_region` and switch the output menu to Excel Value. The summary spills into the grid as ordinary cells you can reference with regular Excel formulas.

## Where to go from here

This is where the Python for Excel Users arc lands. You started by reading a CSV with pandas in a standalone script; the groupby in Step 2 is the same one from [Python for Excel Users, Part 3](/blog/python-for-excel-users-part-3-merging-datasets-and-replacing-your-vlookups/), except the DataFrame now comes from `xl()` instead of `read_csv`, and the result renders in the workbook instead of a terminal. Same pandas, no context switch - the spreadsheet has started running the language you were already writing next to it.