Why this matters Excel has long been the default tool for analysis, financial modelling, and reporting. But as datasets grew (and the questions got tougher), many people hit the same bottleneck: either learn a programming language or keep wrestling with Excel’s limits.
What changed In August 2023, Microsoft introduced Python directly in Excel. In plain terms: you can now run Python code inside worksheet cells and do data-science work without leaving your spreadsheet.
What we’ll do in this post This is a hands-on walkthrough. You’ll see how to:
- Enable Python mode in a worksheet cell
- Reference Excel ranges and tables from Python
- Write and run Python code in cells to complete practical data-science tasks
What makes Python in Excel different?
If you have only ever used Excel formulas (or maybe a bit of VBA), this feels like a big step up. Python in Excel lets you write actual Python—right inside the workbook—so you can do more than just spreadsheet gymnastics.
- You are writing real Python — full Python syntax, not formula language
- You can use the libraries — pandas, scikit-learn, matplotlib, and more
- You are not limited to “Excel-sized” workflows — handle bigger datasets more comfortably than typical worksheet-only approaches
- You can do ML without leaving the sheet — train models and generate predictions inside the workbook
- You can build visuals from code — generate plots and charts programmatically
One key thing to know: your Python code runs in Microsoft’s secure cloud environment (not on your laptop). That keeps execution consistent—and means you will need an internet connection when you run it.
Getting Started: Writing Python in Your Worksheet
Step 1: Turn on Python mode
Here are the two quickest ways to enable Python in a cell.
- Select the cell where you want to write Python.
- Go to Formulas → Insert Python.
- Confirm the cell is in Python mode (you’ll see Py in the formula bar).
Quick shortcut: In any cell, type =PY( to start a Python formula immediately.
Step 2: Understanding the xl () Function
The xl () function is your bridge between Excel and Python. It converts Excel ranges into Python objects. Let’s reference the Excel range (A1:D20) as a python DataFrame. To do this, enter the formula below in the function box.
You will notice the highlighted box showing the output on the worksheet. You can use Ctrl + Enter to commit the code. This creates a panda DataFrame for your worksheet data.
Referencing a table from another workbook
Next, let’s pull in a named table from another workbook. In my example, I have a worksheet called Store_Code inside a workbook named Messy_PowerBI_Training_Dataset, and I want to bring that table into my current sheet as a pandas DataFrame.
Here’s how:
- Make sure Python is enabled in the cell you’re working in.
- Type xl( and then highlight the table/range you want to import (as shown in the screenshot).
- Press Ctrl + Enter to run the code.
After you press Ctrl + Enter, Excel imports the selected table and returns it as a DataFrame in the active cell (see the next screenshot).
Tip: that book-like icon in the cell is a handy “inspect” button. You can preview the data, paste it elsewhere, and quickly check the structure of what you imported. The next screenshot shows what that looks like.
Step 3: Write your first Python code
Let’s keep it simple for the first run. Say you want the total square_footage in your dataset. Enable Python in a cell, use xl() to bring the range in as a DataFrame, and then calculate the sum. The screenshot below shows both the code and the output.
Data science workflow: Data cleaning
If you have done any real-world analysis, you know the drill: cleaning and prep can easily take most of your time. The good news is that Python in Excel makes these “messy bits” much easier to handle.
In this section, we’ll clean up a deliberately messy Sales_Data dataset from a fictitious company. Here’s the imported data we’ll be working with:
You can already see inconsistent formatting and gaps in the data—so let’s start cleaning.
- Check for missing values
In the screenshot above, the code =PY(df.isnull().sum()) counts the null values in each column. Notice that Units_Sold looks like it has zeros—because some blanks may be coming through in a way pandas doesn’t treat as true NaN. This is a good reminder to decide (column by column) how you want to handle missing values before you start summarizing or modelling.
- Remove unnecessary columns
In the screenshot above, we dropped the Date column from the original DataFrame (top). The output (bottom) confirms that the column is gone.
- Standardize the Region column
Next, let’s clean up the Region column so the casing is consistent. Right now, you’ll see values like “north”, “NORTH”, and “Central”. The screenshot below shows the code and the result.
- .str tells pandas you want to apply a text (string) operation to the column.
- .title() applies proper casing—for example, it turns “north”, “NORTH”, and “nOrTh” into “North”.
- Split the Category & Item column
Now let’s split the Category_&_Item column. It contains two values joined by a pipe (|) delimiter, so we’ll separate it into two cleaner columns.
The image shows the code and a quick explanation. Run it, and you’ll see the output in the next screenshot.
At this point, the DataFrame is already looking much cleaner. One last step: let’s remove duplicates.
- Remove duplicates
We can already spot duplicates in the first couple of rows, and there may be further down. Since Order_ID should be unique, we’ll drop duplicate values in that column using .drop_duplicates() (see the code in the screenshot).
Once you run the code, the duplicate Order_ID values are removed (as shown above). Now you’ve got a clean dataset that’s ready for analysis, insights, and visuals.
In Part 2, we’ll use this cleaned data to derive insights and create visualizations—still inside Excel, using the Python editor.
If that sounds useful, keep an eye out for the next post.
Best practices
- Start with Excel for input/output: use Excel for data entry and results; use Python for processing.
- Break complex workflows into steps: avoid trying to do everything in one cell.
- Name your ranges: named ranges make your Python references cleaner and easier to read.
- Comment your code: leave notes so that in the future, you will recall why you did what you did.
- Use the preview pane: Excel’s preview shows exactly what your Python will return.
Limitations to keep in mind
- Internet required: Python runs in Microsoft’s cloud.
- Library restrictions: you can only use the pre-installed libraries.
- Execution limits: long-running operations may time out (you might need to reset the runtime).
- Memory constraints: very large datasets can be slow or fail.
- No external files: you can’t read from or write to local files directly.
Conclusion
Python in Excel turns a workbook into a practical data-work environment. You can run end-to-end workflows—from cleaning to analysis (and even basic modelling)—without leaving the spreadsheet.
Ready to try it? Open a workbook, select a cell, and type =PY(—your Python-in-Excel journey starts right there.
