6.4 Python in Excel

What you'll be able to do

Run real Python — pandas, plots — inside a workbook cell. Know exactly which licence allows it. Judge honestly when the work belongs in Excel, and when it has outgrown Excel and wants a notebook.

Before you start

A current work or school Microsoft 365 desktop Excel. Python in Excel is one of the five paid topics from Getting Your Footing. There is a limit on how much computing you get, so heavy use may need the paid add-on. If your plan does not include it, read the lesson anyway. Knowing what it is decides when to ask for it.

The work

1. Same either way: type =PY( in any cell, or use Formulas → Insert Python, and the cell becomes a Python cell. The code you write runs in Microsoft's cloud, not on your machine, and that explains everything odd about it. You cannot install packages of your own, and there is no access to your own files. There is a usage limit, and results take a moment to come back.
2. Same either way: get the data across. Inside a Python cell, xl("Sales[Amount]") reads a Table column, and xl("A1:C100", headers=True) reads a range. Both arrive as a pandas DataFrame, because pandas, matplotlib and the usual scientific packages are already loaded. The AI and machine learning area's Python module and this feature meet each other here.
3. Same either way: results come back two ways, and the choice is yours. To begin with, the cell holds a Python object, shown as a card. Convert the cell's output to Excel values, using the option in its context menu, which Shift+F10 opens. The DataFrame then spills into the grid like a dynamic array, where formulas, charts and PivotTables can use it. Keep objects for passing between Python cells, and use values when handing results to Excel.
4. Same either way: three one-liners repay the effort immediately, and Excel alone labours over all three. df.describe() gives the full statistical summary of every column at once. df.groupby("store").agg({"amount": ["sum", "mean"]}) gives a PivotTable with several statistics in one line. And a seaborn heat map or box plot draws chart types Excel does not offer, right there in the grid.
5. Same either way: respect the boundary, which is the honest skill here. Python in Excel earns its place when the workbook is what you are delivering and one step needs real analysis. A statistical summary inside a report that somebody else refreshes is the case. The work has outgrown it when the Python is the point: long scripts, packages you must choose, large data, version control. That work belongs in a notebook, which the AI and machine learning orientation module covers, with Excel importing the results.

Try it

If your plan includes it, read the sales Table into a DataFrame and spill df.describe() into the grid. Build the groupby summary and draw one seaborn plot. Then say aloud which side of the boundary your own current project sits on, and why.

Check yourself