Python in Excel for Beginners: The First 10 Things Worth Learning

Python in Excel is one of those features that attracts both excitement and confusion. Beginners often hear that it changes everything, while experienced spreadsheet users wonder whether it is worth the extra complexity.

The sensible answer is to start with the few use cases where it clearly adds value, rather than trying to use Python for everything from day one.

Note: Availability can vary by version and channel. Treat this guide as current as of 5 August 2026 and check the Microsoft availability notes for your environment.

Connect on LinkedInSagnik Bhattacharya

Quick answer

The ten things below are the working knowledge that makes Python in Excel useful rather than novel: entering a Python cell, pulling data in with xl(), thinking in DataFrames, choosing the output mode, filtering, grouping, plotting, cleaning, reading the error codes, and understanding where your data actually goes. Learn those and you can do real analysis. Everything else is detail you can look up.

1. A Python cell is a formula, and it commits with Ctrl+Enter

Type =PY( in a cell and Excel switches that cell into Python mode — the formula bar changes colour and the cell now expects Python, not Excel syntax. You can also use Formulas → Insert Python, or the shortcut Ctrl+Alt+Shift+P.

The part that trips up everyone on day one: Enter does not run the code. Enter inserts a newline, because Python cells are multi-line by default. You commit with Ctrl+Enter. If you press Enter and nothing happens, nothing is broken — you just added a blank line.

The other structural rule is calculation order. Python cells do not follow Excel's dependency graph. They run in row-major order — left to right along a row, then down to the next row, across the whole workbook. A Python cell in B2 can use a variable defined in A2, but a cell in A2 cannot use a variable defined in B2, even if you write a reference between them. When results come back stale or undefined, check the physical layout before you debug the code.

2. xl() is the only way data gets in

Python in Excel cannot see your workbook unless you hand it a range. The bridge is the xl() function, and it is Excel-flavoured, not Python-flavoured:

# A plain range — no headers, so pandas invents 0,1,2 column names
xl("A1:C50")

# The same range, treating row 1 as column names
xl("A1:C50", headers=True)

# A named Excel Table — the option worth defaulting to
xl("SalesData[#All]", headers=True)

# A single cell, which comes back as a scalar, not a DataFrame
xl("B7")

# A whole column from a Table
xl("SalesData[Revenue]")

Use Tables (Ctrl+T) rather than raw ranges wherever you can. A Table grows when rows are appended, so xl("SalesData[#All]", headers=True) keeps working after new data arrives. A hard-coded xl("A1:C50") silently ignores row 51 onwards, and nothing in the output tells you it happened.

Note the headers=True argument specifically. Leave it off and pandas treats your header row as data, which turns every numeric column into an object column, and then every numeric operation you try afterwards fails with a type error that points at the wrong line.

3. Everything is a DataFrame — that is the real mental shift

An Excel range is a grid of cells you address by position. A pandas DataFrame is a table you address by name. That change is the whole learning curve; the syntax is secondary.

df = xl("SalesData[#All]", headers=True)

df.shape        # (rows, columns) — the first thing to check
df.columns      # column names, exactly as spelled in row 1
df.dtypes       # what pandas thinks each column's type is
df.head(10)     # first ten rows
df.describe()   # count, mean, std, min, quartiles, max for numeric columns

df.describe() alone replaces a block of COUNT, AVERAGE, STDEV, MIN, QUARTILE and MAX formulas, and it updates itself when the columns change. That single method is usually the moment Python in Excel starts to feel worth it.

Check df.dtypes early and often. Most confusing Python-in-Excel behaviour comes from a column being object (text) when you assumed it was float64 (a number) — usually caused by a stray "N/A", a currency symbol, or a space in an otherwise numeric column.

4. Excel Value vs Python Object — the setting that causes the most confusion

Every Python cell returns its result in one of two modes, toggled from the small icon at the left of the formula bar or with Ctrl+Alt+Shift+M:

Beginners routinely think their code failed because they see one card instead of a table. The code ran fine — the cell is in Python Object mode. Switch it to Excel Value and 150 rows spill out.

The reverse matters too. Keep intermediate steps as Python Objects: spilling a 50,000-row DataFrame into the grid just so the next cell can read it back is slow and clutters the sheet. Spill only the final result.

5. Filtering rows: boolean masks, not AutoFilter

Filtering in pandas means writing a condition that produces True/False for every row, then indexing with it.

df = xl("SalesData[#All]", headers=True)

# One condition
high = df[df["Revenue"] > 10000]

# Multiple conditions — round brackets are mandatory, and it is & / | not and / or
q4_uk = df[(df["Region"] == "UK") & (df["Quarter"] == "Q4")]

# Membership, instead of chained ORs
core = df[df["Region"].isin(["UK", "Germany", "France"])]

# Text matching
enterprise = df[df["Segment"].str.contains("Enterprise", na=False)]

enterprise

Two syntax rules cause nearly every beginner error here. Use & and |, never Python's and / or — the plain keywords raise "truth value of a Series is ambiguous". And wrap each condition in round brackets, because & binds tighter than > in Python, so leaving them off compares the wrong things.

Pass na=False to .str.contains() whenever the column might hold blanks, or empty cells raise instead of simply not matching.

6. GroupBy: where Python starts beating PivotTables

A PivotTable is faster for a one-off summary. GroupBy wins when the summary is a repeatable step in a longer chain, or when you need several different aggregations at once.

df = xl("SalesData[#All]", headers=True)

# Revenue by region
df.groupby("Region")["Revenue"].sum()

# Two levels of grouping
df.groupby(["Region", "Segment"])["Revenue"].sum()

# Different aggregations per column, named cleanly
summary = df.groupby("Region").agg(
    total_revenue=("Revenue", "sum"),
    average_deal=("Revenue", "mean"),
    deal_count=("Revenue", "count"),
    best_deal=("Revenue", "max"),
)
summary.reset_index()

The .agg() form is the one to learn properly. Building that same four-metric-by-region table with formulas means SUMIFS, AVERAGEIFS, COUNTIFS and MAXIFS, each repeated per region, each needing a hard-coded region list that breaks when a new region appears. The Python version discovers the regions itself.

Call .reset_index() before returning to the grid. Without it the grouping column is a pandas index, and it disappears when the result spills into Excel — you get numbers with nothing labelling them.

7. Charts: matplotlib returns a picture into the cell

A plot in a Python cell returns an image object that Excel renders inside that cell. Resize the row and column to see it properly, or right-click and extract it to a floating picture.

import matplotlib.pyplot as plt

df = xl("SalesData[#All]", headers=True)
by_region = df.groupby("Region")["Revenue"].sum().sort_values()

fig, ax = plt.subplots(figsize=(8, 4.5))
by_region.plot(kind="barh", ax=ax, color="#4C78A8")
ax.set_xlabel("Revenue")
ax.set_title("Revenue by region")
ax.spines[["top", "right"]].set_visible(False)
fig.tight_layout()
fig

The last line matters: end the cell with the figure object (fig) so it is what the cell returns. A cell that ends on plt.show() returns None and renders an empty result — a genuinely common first-plot failure.

Be honest about when this is worth it. For a bar chart of ten regions, a native Excel chart is faster to build and easier for colleagues to edit. Reach for matplotlib when you want something native charts do badly: small multiples, distribution plots, regression overlays, or a chart whose styling you need identical across twenty workbooks.

8. Cleaning data — the unglamorous thing you will use most

This is where Python genuinely saves hours, and it is the least discussed use case.

df = xl("RawExport[#All]", headers=True)

# Strip whitespace from every text column at once
for col in df.select_dtypes("object"):
    df[col] = df[col].str.strip()

# Force a stubborn text column to numeric; unparseable values become NaN
df["Revenue"] = pd.to_numeric(df["Revenue"], errors="coerce")

# Parse dates that arrived as text, day-first as UK exports usually are
df["OrderDate"] = pd.to_datetime(df["OrderDate"], dayfirst=True, errors="coerce")

# Standardise inconsistent category spellings
df["Region"] = df["Region"].str.title().replace({"Uk": "UK", "Usa": "USA"})

# Remove exact duplicate rows, keeping the first
df = df.drop_duplicates()

# What is still missing, by column
df.isna().sum()

errors="coerce" is the important habit. It converts anything unparseable to NaN instead of throwing, so one bad row out of 40,000 does not kill the whole cell. Then df.isna().sum() tells you exactly how many failed and in which column — which is a far better bug report than an exception.

Doing this cleanup with formulas means a hidden helper sheet of TRIM, VALUE, DATEVALUE and IFERROR columns that everyone is afraid to delete. Six lines of Python replace it and state the intent plainly.

9. The four error codes, and what each actually means

The single most useful habit: when you see #PYTHON!, do not re-read your code first. Open Formulas → Diagnostics and read the actual traceback. It names the failing line and the exception type, which is nearly always faster than guessing.

10. It runs in Microsoft's cloud, and that has consequences

Python in Excel does not execute on your machine. Your data is sent to a container in the Microsoft Cloud, the code runs there, and the result comes back. Four practical consequences follow, and all four are easier to learn now than to discover later:

• Latency is real. Every recalculation is a network round trip. A workbook with thirty Python cells is noticeably slower than one with thirty formulas, and this is the main argument against using Python where a formula would do.
• You cannot pip install. The runtime ships a fixed Anaconda distribution — pandas, numpy, matplotlib, seaborn, statsmodels, scikit-learn and the rest of the standard stack are preloaded. If a library is not in that set, it is not available.
• It needs a connection. Python cells do not recalculate offline. They hold their last computed value, which is fine for reading but means the workbook is not self-contained.
• Sensitive data leaves your device. This is the one to raise before rolling it out to a team. Check your organisation's data handling rules before putting personal, financial or client data through a Python cell — the answer is often "not without approval".

There is also no bridge to VBA. Python cells cannot be called from macros, cannot be invoked by custom Excel functions, and cannot write to other cells the way a macro can. Python in Excel computes and returns a value; it does not automate the application.

Worked example: a churn analysis in four cells

A customer-success team has an export with one row per customer: signup date, cancellation date (blank if still active), monthly revenue and segment. The question is which segments churn worst. Four Python cells answer it end to end.

Cell B1 — load and clean:

df = xl("Customers[#All]", headers=True)
df["SignupDate"] = pd.to_datetime(df["SignupDate"], dayfirst=True, errors="coerce")
df["CancelDate"] = pd.to_datetime(df["CancelDate"], dayfirst=True, errors="coerce")
df["MonthlyRevenue"] = pd.to_numeric(df["MonthlyRevenue"], errors="coerce")
df = df.dropna(subset=["SignupDate", "MonthlyRevenue"])
df

Cell B2 — derive the churn flag and tenure:

df = xl("B1")
df["Churned"] = df["CancelDate"].notna()
end = df["CancelDate"].fillna(pd.Timestamp.today())
df["TenureMonths"] = ((end - df["SignupDate"]).dt.days / 30.44).round(1)
df

Cell B3 — churn rate and revenue at risk by segment:

df = xl("B2")
result = df.groupby("Segment").agg(
    customers=("Churned", "size"),
    churned=("Churned", "sum"),
    avg_tenure_months=("TenureMonths", "mean"),
    monthly_revenue=("MonthlyRevenue", "sum"),
)
result["churn_rate_pct"] = (result["churned"] / result["customers"] * 100).round(1)
result = result.sort_values("churn_rate_pct", ascending=False)
result.reset_index()

Set that final cell to Excel Value and the table spills into the grid, where conditional formatting and ordinary formulas work on it normally.

Cell B4 — the chart:

import matplotlib.pyplot as plt

result = xl("B3")
fig, ax = plt.subplots(figsize=(8, 4.5))
ax.barh(result["Segment"], result["churn_rate_pct"], color="#E45756")
ax.set_xlabel("Churn rate (%)")
ax.set_title("Churn rate by segment")
ax.spines[["top", "right"]].set_visible(False)
fig.tight_layout()
fig

Notice the pattern: each cell reads the previous cell's DataFrame with xl("B1"), xl("B2") and so on. Intermediate cells stay as Python Objects; only B3 spills. That is the idiomatic structure — a short chain of named steps rather than one enormous cell, so when something breaks you can see which step produced the wrong shape.

When a formula is still the better answer

The honest summary: Python in Excel is not a replacement for Excel. It is a better tool for cleaning, multi-metric aggregation and statistics, and a worse tool for lookups, simple conditional maths and anything a colleague needs to edit without reading code.

Common mistakes

• Pressing Enter instead of Ctrl+Enter and concluding the feature is broken.
• Forgetting headers=True, which turns every numeric column into text and breaks the next operation with a confusing type error.
• Using and / or in a filter instead of & / |, producing "truth value of a Series is ambiguous".
• Not calling .reset_index() after a groupby, so the labels vanish when the result reaches the grid.
• Ending a plotting cell on plt.show() instead of the figure object, giving an empty cell.
• Ignoring row-major order and referencing a variable defined in a cell that runs later.
• Assuming the code runs locally and putting client data through a cloud runtime without checking policy.
• Rewriting working formulas in Python for its own sake, adding latency and a runtime dependency for no analytical gain.

When to use something else

If you need AI help more than code, go to Copilot in Excel with Python. If you want to understand the in-grid Python surface itself, the PY function guide is the next logical step. For reshaping and merging large exports on a schedule, Power Query is usually the better home than a Python cell.

Frequently asked questions

What should I learn first in Python in Excel?

In order: committing a cell with Ctrl+Enter, loading a Table with xl("Table[#All]", headers=True), checking df.dtypes and df.describe(), switching between Excel Value and Python Object, and one groupby().agg(). That is enough to do genuine analysis; everything else can be looked up as needed.

Why does pressing Enter not run my Python cell?

Python cells are multi-line, so Enter inserts a newline. Commit with Ctrl+Enter. This is the single most common first-hour confusion.

Why does my cell show "DataFrame (150 rows × 4 columns)" instead of my data?

The cell is in Python Object mode, which is the default. Switch it to Excel Value from the icon at the left of the formula bar, or with Ctrl+Alt+Shift+M, and the rows spill into the grid.

When is Python the right tool versus a formula?

Python wins for cleaning messy exports, computing several aggregations across groups you have not hard-coded, and real statistics. Formulas win for lookups, simple conditional maths, and anything colleagues must edit without reading code. If a formula already solves it cleanly, keep the formula.

Which libraries are available, and can I install more?

The runtime ships a fixed Anaconda distribution — pandas, numpy, matplotlib, seaborn, statsmodels and scikit-learn among others are preloaded. You cannot pip install into it; if a package is not included, it is not available.

Where does the code run, and is my data sent anywhere?

The code runs in a container in the Microsoft Cloud, not on your machine, so workbook data is transmitted there and back on every recalculation. That also means Python cells do not recalculate offline. Check your organisation's data handling rules before using it on personal, financial or client data.

Why do my Python cells calculate in the wrong order?

They run in row-major order across the workbook — left to right along a row, then down — not in Excel's usual dependency order. A cell can only use variables defined in cells that come earlier in that reading order. Rearranging the cells physically fixes it.

Can I call Python in Excel from VBA or a macro?

No. Python cells cannot be triggered from VBA, used inside custom functions, or made to write into other cells. They compute a value and return it; they do not automate Excel.

What does #PYTHON! mean?

Your code raised an exception. Open Formulas → Diagnostics for the full traceback, which names the failing line and exception type — considerably faster than re-reading the code and guessing.

Related guides on this site

• PY Function in Excel: What It Is, How It Works, and When to Use It
• Copilot in Excel With Python: Forecasting, Risk Analysis, and Deeper Reasoning
• Format Data for Copilot in Excel: Tables, Supported Ranges, and Common Failures
• How to Use Microsoft Copilot for Data Analysis in Excel
• Power Query M Code With AI: Reshaping and Merging Data

Official references

• Microsoft Support: Python in Excel availability — which channels and platforms have the feature
• Microsoft Support: Get started with Python in Excel — the official first-run walkthrough
• Microsoft Support: Data security and Python in Excel — read this before using it on client data
• pandas: 10 minutes to pandas — the canonical DataFrame primer
• Matplotlib quick start guide — figure and axes basics used in the plotting examples