How to Use Python in Excel for Data Analysis

Excel has long been the go-to tool for everything from simple budgeting to complex financial modeling, but its analytical capabilities have always had their limits. Pushing past those limits often meant exporting data to a dedicated programming environment. Now, that's changing with the introduction of Python directly within Excel, merging the world's most popular spreadsheet with one of the most powerful programming languages for data analysis. This article will walk you through how to get started and use Python to supercharge your data analysis in Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Python in Excel and Why Should You Care?

Python in Excel brings the power of popular Python libraries for data analysis - like Pandas for data manipulation and Matplotlib or Seaborn for data visualization - directly into your spreadsheet cells. Instead of relying on complex nested formulas or clunky VBA macros, you can now write Python code to perform sophisticated analysis right next to your data.

This integration fundamentally changes what's possible inside a workbook. Here are a few key benefits:

• Advanced Data Manipulation: Clean, group, pivot, and transform your data with a few lines of Python code, handling tasks that would be incredibly tedious or even impossible with traditional Excel functions.
• Powerful Visualizations: Move beyond Excel's standard charts. Create customized, complex plots like heatmaps, violin plots, and pair plots to uncover deeper insights.
• Automation and Scalability: Automate repetitive data cleaning and reporting tasks directly within the environment where your data lives. Refreshing a complex analysis is as simple as recalculating the sheet.
• A Single, Unified Workflow: End the awkward dance of exporting CSVs, running a Python script, and then importing the results back into your spreadsheet. Now, your data, your analysis, and your results can all live in one place.

Getting Started: Enabling Python in Excel

As of late 2023, Python in Excel is in Public Preview and available to users in the Microsoft 365 Insiders program on the Beta Channel. This means it's not yet available to all Excel users, but you can get early access. Here's how:

1. Join the Microsoft 365 Insiders Program: In any Office application (like Excel or Word), go to File > Account > Microsoft 365 Insider. Check the box to sign up for early access and select the Beta Channel. Office will then update to the latest Insiders version.
2. Check for the Python Feature: After the update, open a new Excel workbook. Go to the Formulas tab in the ribbon. If the rollout has reached your account, you'll see a section called Python with an "Insert Python" button.

Because it's a preview feature, keep in mind that functionality may change, and you might encounter occasional bugs. All Python code runs securely on the Microsoft Cloud, not your local machine.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Your First Python Formula: Introducing =PY()

All Python code in Excel lives inside a single new function: =PY(). You type this into any cell, and your Python script goes between the parentheses. You don't need to install Python or any special editors, it's all built-in.

Let's start with the classic "Hello, World!" to see how it works:

1. Click on any empty cell.
2. Type the following formula:

=PY("print('Hello from Python in Excel!')")

1. Press Ctrl+Enter to execute the code. Do not just press Enter, as that will move you to the next cell. Ctrl+Enter commits the formula to the current cell.

Instead of seeing the text, you'll likely see a small "DataFrame" button or a Python object icon in the cell. This is because Python formulas return objects. To see the output, you can click the dropdown arrow next to the formula bar and select "Excel Value." The cells below will then "spill" to show the printed message.

Connecting Excel Data to Python

The real power comes from using your spreadsheet data within your Python script. To do this, Excel provides a special function, xl(), that you use inside your =PY() formula to reference cells or ranges.

Let's say you have a simple table of sales data in cells A1 through C5, with headers in the first row.

To turn this table into a Python object called a pandas DataFrame (the standard for data analysis), you would write this formula in a new cell, like E1:

=PY( df = xl("A1:C5", headers=True) df )

Let's break that down:

• df = ...: We're creating a variable named df.
• xl("A1:C5", headers=True): This references our table. We tell Python that the first row contains headers.
• df: The last line of a PY formula determines what it outputs. By ending with just df, we're telling Excel to display the contents of the DataFrame.

After pressing Ctrl+Enter, you'll see your table perfectly replicated, now ready for serious analysis.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Practical Data Analysis Examples

Now that we have our data in a DataFrame, we can perform some powerful analytical tasks that would be cumbersome in standard Excel.

1. Calculating Comprehensive Summary Statistics

Instead of calculating average, count, standard deviation, and median with separate formulas, you can get all of them at once with the .describe() method.

Assuming your DataFrame is in cell E1, click a new cell and type:

=PY( E1.describe() )

This instantly generates a summary table with the count, mean, standard deviation, min, max, and quartile values for all numerical columns. It's an incredibly fast way to get a high-level overview of your data's distribution.

2. Grouping and Aggregating Data

What if you want to find the total sales for each region? In Excel, this would require a PivotTable or a SUMIF formula. In Python, it's a simple groupby operation.

=PY( # Reference the DataFrame from cell E1 sales_by_region = E1.groupby('Region')['Sales'].sum() sales_by_region )

This code groups all the rows by the 'Region' column and then sums up the 'Sales' for each group. The result is a clean, new table showing total sales for North and South.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

3. Creating Sophisticated Visualizations

One of the most exciting capabilities is creating charts with Python libraries. Let's make a simple bar chart of the sales by region we just calculated. Let's say our aggregated data is now the output of the cell G1.

In a new cell, type:

=PY( import matplotlib.pyplot as plt

# The result from our previous groupby calculation in cell G1 regional_data = xl("G1", headers=True)

plt.bar(regional_data.index, regional_data['Sales']) plt.ylabel('Total Sales') plt.title('Sales by Region')

plt )

After you press Ctrl+Enter, a fully-rendered bar chart will appear in the cell as an image. This chart is dynamic, if your source data changes, the chart will update automatically upon recalculation.

Tips for a Smooth Workflow

• Keep Code Brief: Avoid writing massive scripts in a single cell. Break your analysis into logical steps. For example, have one cell to load your data into a DataFrame, a second to clean it, a third to aggregate it, and a fourth to create a visualization.
• Reference Other Python Cells: Just as an Excel formula can reference another cell, a =PY() formula can reference a cell that contains a Python object. You saw this in our examples - we referenced the cell E1 (which contained our DataFrame) in subsequent formulas. This is the key to building a chained analysis.
• Toggle Your Output View: You can switch between seeing your Python object output ([DataFrame]) and the actual Excel values by clicking the dropdown icon in your formula bar or by right-clicking the cell and going to Output > Excel Value.
• No Install Necessary: Common libraries like pandas, NumPy, Matplotlib, and Seaborn are available out of the box through a standard Anaconda distribution - no complicated setup needed.

Final Thoughts

Integrating Python into Excel powerfully bridges the gap between everyday spreadsheet use and the world of data science. It enables users to perform complex data cleaning, analysis, and visualization without ever having to leave their workbook, making sophisticated techniques accessible to a much broader audience of business and data professionals.

But even with powerful tools, the goal is often just to get fast, clear answers from your data. Whether you're wrangling pandas DataFrames in Excel or building PivotTables, it still takes time and effort to connect all your data sources and build the right reports. With Graphed, we aim to eliminate this friction entirely. We connect to an ever-growing list of sales and marketing platforms, allowing you to use simple, natural language to create real-time dashboards and get immediate insights - no writing code, no wrestling with formulas. It's like having a data analyst on your team, ready to build exactly what you need in seconds.