Technically, Python doesn’t run on your local computer; it operates within the Microsoft Cloud. This means it won’t slow down your laptop (all the “thinking” happens on Microsoft’s high-speed servers), but you do need an internet connection to use it. The gateway to this power is the =PY() function. Typing =PY into the formula bar and pressing Tab opens Excel’s Python editing mode.

Excel’s Python runtime is powered by an Anaconda-curated Python environment that includes popular data science libraries like pandas (the workhorse for data tables) and seaborn (the expert for high-end charts). These are like “power-up” kits that give Python the ability to clean, analyze, and visualize data with minimal effort.

From a security standpoint, the process is designed to be highly controlled and “sandboxed.” Python reads your Excel tables via the xl() function, does the heavy lifting in the cloud, and then “spills” the results back into your cells. Because the environment is sandboxed, Python can’t access your local files—it only interacts with the specific data you explicitly choose to send it.

How to get started with Python in Excel

Some pointers to be aware of

Before you start typing or pasting code, keep these five things in mind:

1. Microsoft 365 only: You need an active subscription and an internet connection.
2. Convert to table: Python reads structured tables more reliably than loose ranges. So, before you start using it, turn your data into a table (Insert > Table or Ctrl+T), and use the Table Name field of the Table Design tab to name the table something like T_SalesData. That said, Python code can’t go inside Excel tables if the results spill down a column, so in these cases, enter them in a regular cell outside formatted Excel tables.
3. The output toggle: After typing =PY into the formula bar and pressing Tab, ensure the output type is set to Excel Value so you can see the actual text or numbers it produces.
4. The commit: In Python mode, pressing Enter just creates a new line. You must press Ctrl+Enter to run your code.
5. Straight quotes: If you copy code from a website or a Word document that uses curly (smart) quotes, Python will return an error. Make sure you replace these with straight quotes.

Setting up the example dataset

A simple real-world scenario

Now that we’ve covered the essentials, it’s time to dive in. Suppose you’ve been given the messy Excel dataset shown above (named T_SalesData). You’ve been asked to clean this data and extract insights from it.

Here’s how you can use Python to get the job done—and quickly.

Use case 1: Fix spacing and capitalization

Cleaning text in a single line of code

The Customer_Info data in column B is inconsistently capitalized and has some leading and trailing spaces. To create a new list of names that is clean and dynamic, type =PY into a blank cell, press Tab, paste this code, and press Ctrl+Enter:

xl("T_SalesData[Customer_Info]").squeeze().str.strip().str.title().to_list()

Here’s how it works:

Why this method beats the old ways

• vs. Excel: Standard Excel requires you to nest functions or use multiple helper columns to achieve the same outcome. While fine for one task, Python lets you chain dozens of cleaning steps into one readable line.
• vs. Power Query: Power Query requires opening a separate editor. Python keeps you in the flow of your spreadsheet.

Use case 2: Standardize impossible dates

Using heuristic logic to fix messy formats

At the moment, the Date_Raw column contains a nightmare of formats—dots, dashes, and text—but Python can sort that in no time. Paste the following code and press Ctrl+Enter:

import pandas as pd; pd.to_datetime(xl("T_SalesData[Date_Raw]").squeeze(), errors="coerce").to_list()

Here’s what’s happening:

Why this method beats the old ways

• vs. Excel: Excel’s Text to Columns tool is a one-time fix that often fails if the dates are truly messy.
• vs. Power Query: Power Query works well for structured imports, but messy or inconsistent formats may require extra steps.

Use case 3: Create a professional density plot

Visualizing data clusters

You now want to see where your Price_Points cluster, but a standard histogram looks too “blocky.” Use the following code to create a Kernel Density Estimate (KDE) plot, which shows the probability distribution of a dataset as a smooth curve:

import seaborn as sns; sns.kdeplot(xl("T_SalesData[Price_Point]").squeeze().rename("Price ($)"), fill=True, color="green")

Now for the translation:

Why this method beats the old ways

• vs. Excel: Excel’s histogram forces data into bins. If they’re too wide, you miss the nuances, and if they’re too narrow, the chart looks like a mess of thin sticks.
• vs. Power Query: Power Query can’t create charts—it’s a tool for importing, cleaning, and loading data.

Use case 4: Produce a smart sentiment flagger

Implementing scalable keyword detection

Let’s imagine you have lots of customer reviews in column E. You need to instantly highlight any that contain “red-flag” words like return, bad, or refund. To do this automatically, paste the following and press Ctrl+Enter:

reviews = xl("T_SalesData[Review_Text]").squeeze()
["Action Needed" if any(word in str(r).lower() for word in ["return", "bad", "refund"]) else "Clear" for r in reviews]

Here’s what’s happening:

Why this method beats the old ways

• vs. Excel: To search for multiple words in Excel, you have to nest the ISNUMBER and SEARCH functions inside a giant OR statement. It’s hard to read and even harder to update.
• vs. Power Query: In Power Query, you’d have to add a Conditional Column and manually create a new “Contains” rule for every single keyword. If you have 20 keywords, that’s 20 separate menu interactions.

In contrast, to add a new keyword in the Python code, simply type it into the brackets. What’s more, Python checks for the presence of the word fragment, so searching for “return” will also flag variations like “returning” or “returned.” Just be aware of potential false positives.