Clean Data with pandas in Python in Excel

Python in Excel runs pandas right inside a workbook: type =PY( into a cell, reference a range with xl(), and the Python code runs in Microsoft's cloud and returns its result to the grid. For data cleaning that is a genuinely useful combination. The messy table stays where the people who maintain it can see it, the cleaning steps are written once as code instead of repeated by hand, and the cleaned result updates whenever the source changes. This guide, part of Using Python in Excel, cleans a typical hand-maintained table step by step and explains the rules that make multi-cell Python work predictable.

Prerequisites

Python in Excel is available in Microsoft 365 on Windows, Mac and the web for supported plans. There is nothing to install: the Python runtime and libraries such as pandas and NumPy are provided by Microsoft in the cloud. The examples assume a sheet with a table named Contacts like this one — the kind of list that several people type into over months:

Whitespace, inconsistent case, a duplicate that differs only in spacing, two date formats, text in a numeric column, and an abbreviation — each of them breaks a pivot or a lookup.

Step 1 — Read the table into a DataFrame

In an empty cell, type =PY( to enter Python mode, then reference the table with xl(). Referencing the whole table with [#All] and headers=True gives you proper column names:

raw = xl("Contacts[#All]", headers=True)
raw.shape

Commit with Ctrl+Enter. The cell shows a DataFrame card; hovering or clicking it previews the data. Keep this first cell as a plain read — it makes the later cells easier to debug, because you can always inspect raw exactly as it arrived. How xl() addresses ranges, tables and named ranges is covered in Reference Cells, Ranges and Tables with xl() in Python in Excel.

Step 2 — Clean text columns

In the next cell — to the right of or below the first, because of the calculation order described below — normalise the text: strip surrounding spaces, collapse repeated spaces inside names, and put names and regions into a consistent case:

df = raw.copy()
df["Name"] = (df["Name"].astype(str)
                         .str.strip()
                         .str.replace(r"\s+", " ", regex=True)
                         .str.title())
df["Region"] = df["Region"].astype(str).str.strip().str.title()
df.head()

Working on a copy keeps raw intact for comparison. The same operations outside Excel are described in Strip Whitespace and Normalise Text Columns with pandas; inside Excel they are identical, which is much of the appeal — a cleaning routine prototyped in a PY cell can move into a scheduled script later with little change.

Step 3 — Fix types: dates and numbers

xl() passes values as Excel holds them. Real date cells arrive as datetimes, but dates typed as text arrive as strings, and numbers typed with thousands separators or placeholders arrive as text. Convert explicitly, treating anything unparseable as missing so it can be reported rather than guessed:

df["Signed up"] = pd.to_datetime(df["Signed up"], dayfirst=True, errors="coerce")
df["Spend"] = pd.to_numeric(
    df["Spend"].astype(str).str.replace(",", "", regex=False).str.strip(),
    errors="coerce",
)
df.dtypes

dayfirst=True records a decision: in this workbook 05/01/2026 means 5 January. Make that decision deliberately for your data — it is the single most common silent error in spreadsheet cleaning — and see Parse Excel Dates into Python datetimes with pandas for mixed-format columns.

Step 4 — Standardise categories and remove duplicates

Categories typed by hand need a mapping from every variant to one canonical value. Keep the mapping visible and short, and flag anything it does not cover:

REGIONS = {"North": "North", "Sth": "South", "South": "South", "East": "East", "West": "West"}
df["Region"] = df["Region"].map(REGIONS)
unknown_regions = df.loc[df["Region"].isna(), "Name"].tolist()

clean = df.drop_duplicates(subset=["Name", "Signed up"], keep="first").reset_index(drop=True)
len(raw), len(clean), unknown_regions

The duplicate check runs after text cleaning on purpose: " Ana Silva" and "ana silva " only match once both have been normalised. Choosing the key for duplicates — here name plus sign-up date — is a business decision; Pandas: Drop Duplicates From an Excel Column discusses the options.

Step 5 — Return the clean table to the grid

A PY cell that ends with a DataFrame shows it as a Python object card by default. To get cells you can chart, filter or reference, switch the output to Excel value — from the output menu beside the formula bar, or with Ctrl+Alt+Shift+M — and the DataFrame spills as values:

clean

Leave room below and to the right for the spill; if anything is in the way the cell shows #SPILL!. Other formulas can refer to the spilled result with the spill operator, for example =SUM(J2#) style references to a column of it, or you can return a single summary instead of the whole table:

clean.groupby("Region", dropna=False)["Spend"].sum()

A good pattern is one cell that returns the cleaned table as values for people to use, and a second small cell that returns a list of problems — rows with unknown regions, unparseable dates or missing spend — so the owners of the source data can fix them at the source.

problems = df[df["Region"].isna() | df["Signed up"].isna() | df["Spend"].isna()]
problems[["Name", "Region", "Signed up", "Spend"]]

How calculation order and recalculation work

Python cells share one namespace per workbook and run in row-major order: across the first row from left to right, then the next row, and so on, sheet by sheet in tab order. That is why the cleaning steps above can use variables from earlier cells — and why a cell that uses df must sit after the cell that defines it. When a referenced cell or an earlier Python cell changes, the affected Python cells recalculate. On large workbooks, set calculation to partial or manual while editing to avoid rerunning all Python cells on every keystroke.

Common pitfalls

• Using a variable before it is defined. A cell above or left of the defining cell raises NameError. Keep each chain of cells in reading order.
• Returning objects where values are expected. A chart or formula that references a Python object cell sees a single object, not a table. Switch the output to Excel value.
• Expecting local files or the internet. Python in Excel cannot read files from your disk or call web APIs; data must come through the workbook. Get External Data into Python in Excel with Power Query shows the supported route.
• Cleaning in place by hand afterwards. Edits typed over the spilled output are not possible, and edits to the source should go into the source table — that is what makes the cleaning repeatable.

Performance and scale notes

Python in Excel is designed for analysis-sized data: tens or hundreds of thousands of rows are fine, while very large tables are slow to send to the cloud and back on every recalculation. Keep the source as a proper Excel table so xl() reads exactly the used range, return values only where people need them, and prefer one cell that builds the cleaned frame over many cells that each copy it. For bulk cleaning of files outside a workbook, a regular Python script with the same pandas code is the better home — compare the options in Python in Excel vs openpyxl and xlwings.

Conclusion

Python in Excel turns a cleaning routine into a set of PY cells: read the table with xl(), normalise text, convert dates and numbers explicitly, map categories and remove duplicates after normalising, then return the result as Excel values and list the problems for the source owners. Keep cells in reading order, because they run row by row and share one namespace, and bring external data in through the workbook rather than from Python.

Frequently asked questions

Does cleaning data in Python in Excel change the original table? No. A PY cell reads the table through xl() and returns a new result. The source cells are never modified, which makes the cleaning repeatable — fix the source and the cleaned output follows.

How do I get the cleaned DataFrame into cells instead of a single card? Switch the PY cell's output from Python object to Excel value, with the output menu next to the formula bar or the keyboard shortcut Ctrl+Alt+Shift+M. The DataFrame then spills into the grid as a table of values.

Can one PY cell use a DataFrame created in another? Yes. Python cells share one namespace per workbook and run in row-major order — left to right, top to bottom, sheet by sheet — so a cell can use variables defined in cells that come before it.

Which pandas version does Python in Excel use? A fixed Anaconda distribution managed by Microsoft, updated from time to time. Check it with a PY cell containing pd.version, and avoid depending on the very newest pandas features.

Related

• Up one level: Using Python in Excel — how =PY() works.
• Return DataFrames and Values from Python in Excel Cells — output modes in detail.
• Cleaning Excel Data With Pandas — the same techniques in scripts.
• Fix Python in Excel Errors — when a PY cell shows an error.