How Can Python Be Used in Excel? Setup, Features and Examples

Python in Excel allows Microsoft 365 users to write Python code inside spreadsheet cells and analyse worksheet data with libraries such as Pandas, NumPy, Matplotlib and Scikit-learn. Calculations run in the Microsoft Cloud, and the results return directly to the workbook.

To use Python in Excel, select Formulas > Insert Python or enter =PY in a cell, reference workbook data with the xl() function, write the code and run the calculation. This integration reduces the need to export worksheet data into separate coding tools for advanced analysis.

What is Python in Excel?

Python in Excel runs code directly inside cells using a secure Microsoft Cloud environment with a curated set of libraries provided by Anaconda. No local installation, path configuration or virtual environment is required.

This feature allows teams to perform advanced Excel data analysis without third-party add-ins. For professionals beginning hands-on Python programming training, it offers an accessible way to apply programming skills to business reporting.

Key features include:

• Cleaning and restructuring messy tabular datasets
• Calculating advanced statistical summaries and multivariable correlations
• Generating custom heatmaps, pair plots and distribution curves
• Running predictive machine-learning models on spreadsheet tables
• Automating multistep data manipulation and aggregation workflows

What is needed before getting started?

Python in Excel requires a qualifying Microsoft 365 subscription and an internet connection. It is currently supported in Excel for Windows, Excel on the web and Excel for Mac, but not in the Excel mobile apps for iPhone, iPad or Android. Microsoft provides current details in its official Python in Excel introduction.

Python does not need to be installed locally. However, custom packages or settings from a local Python installation are not automatically available inside Excel’s cloud environment.

How to use Python in Excel with the =PY() Function

Getting started with Python in Excel requires no complicated local software configuration.

Follow these steps:

1. Select an empty cell, type =PY, and choose PY from the autocomplete menu.
2. Reference spreadsheet tables using the xl() function.
3. Write the Python logic in the formula bar.
4. Press Ctrl + Enter to execute the script.

# Load an Excel table into a Pandas DataFrame

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

# Calculate regional summary statistics

summary = df.groupby("Region")["Revenue"].agg(

    ["count", "mean", "median", "std"]

)

summary

Excel securely transmits the code to its cloud container, processes the data and returns the result. Results can appear as standard Excel values that spill across rows or as Python objects containing the complete DataFrame.

Power Query vs Python: What are the differences?

Power Query is designed for importing and reshaping data, while Python supports advanced analysis, statistical modelling and machine learning.

The tools work best together. Power Query can ingest and standardize data before Python performs advanced calculations.

Which tool is better for data preparation in Excel?

Clean data is the foundation of reliable decision-making. Choosing the right tool for each stage of data preparation in Excel can reduce manual troubleshooting.

When to Use Power Query

Use Power Query for initial data extraction and standard data preparation:

• Combining files: Merge spreadsheets or CSV files into one master table.
• Unpivoting data: Convert wide reports into database-ready tables.
• Basic cleaning: Remove blanks, split columns and correct data types.
• Automated refreshes: Update connected data without repeating manual work.

When to Use Python

Switch to Python when transformations require programmatic logic:

• Missing-value imputation: Fill gaps using medians, interpolation or regression models.
• Regex text parsing: Extract codes or email domains from unstructured text.
• Outlier detection: Identify anomalies using Z-scores or IQR methods.
• Fuzzy matching: Group misspelled records using string-similarity algorithms.

Mastering these in-demand data science skills helps analysts build automated cleaning pipelines that reduce repetitive manual errors.

What are some practical Python in Excel examples?

Integrating Python into spreadsheets unlocks analytical capabilities that traditional formulas cannot easily replicate.

1. Instant Exploratory Data Analysis

Instead of creating separate AVERAGE, MEDIAN and STDEV.S formulas for every column, Pandas can generate summary statistics in one step:

# Generate summary statistics across all numeric columns

sales_df = xl("QuarterlyReport[#All]", headers=True)

sales_df.describe()

This command calculates the count, mean, standard deviation, minimum, 25th percentile, median, 75th percentile and maximum values across every numeric column.

2. Advanced Visualizations with Seaborn and Matplotlib

Python libraries such as Matplotlib and Seaborn can create correlation matrices and distribution charts that are difficult to build with standard Excel tools.

import matplotlib.pyplot as plt

import seaborn as sns

# Build a correlation matrix heatmap

data = xl("FinancialMetrics[#All]", headers=True)

plt.figure(figsize=(8, 5))

sns.heatmap(

    data.corr(numeric_only=True),

    annot=True,

    cmap="coolwarm",

    fmt=".2f"

)

plt.title("Key Metrics Correlation")

Excel displays the output in the worksheet as an image object that can update when the underlying source data changes.

3. Predictive Modelling Inside the Spreadsheet

With Scikit-learn available through Python in Excel, analysts can run predictive models directly from workbook data.

Practical applications include:

• Customer churn modelling: Train classification models to identify customers at risk of leaving.
• Sales forecasting: Apply regression algorithms to estimate upcoming revenue using historical and seasonal patterns.
• Customer segmentation: Use K-Means clustering to group customers according to purchasing behaviour and transaction value.

For professionals taking a structured data science and machine learning course, this feature creates a practical bridge between data science concepts and everyday business spreadsheets.

What are the best practices for Python in Excel?

Follow these principles to keep Python-powered spreadsheets fast, secure and easy to maintain:

1. Let Power Query handle data ingestion: Use Power Query to import, filter and standardize tables before passing data to Python.
2. Consolidate code blocks: Write organized scripts instead of spreading dozens of separate =PY() formulas across individual cells.
3. Understand cloud execution: Python calculations run in Microsoft Cloud containers rather than on the computer’s local processor. Organizations should ensure their governance policies permit cloud computation.
4. Comment the logic: Add short comments to Python code so colleagues can understand and review each calculation.
5. Start with small tasks: Replace complicated nested formulas with simple Pandas commands before progressing to predictive models.

Conclusion

Python in Excel combines familiar spreadsheet workflows with advanced analytics. Starting with simple Pandas calculations before progressing to visualization and predictive modelling makes the feature easier to adopt.

Learners who want structured practice with Excel, Python and business reporting can explore the Data Analytics course at GALTech School of Technology.