Grab the practice workbook 👉 HERE and follow along:

What is Python in Excel and How Can It Help You?

Microsoft has added Python to Excel. Now, you can run Python scripts directly in your spreadsheets. This powerful combination lets you use Python’s advanced tools without leaving Excel.

What is Python?

Python is a popular programming language known for its simplicity and versatility. It’s used for data analysis, automation, and more. With Python in Excel, you get the best of both worlds.

Benefits of Using Python Excel:

  • Efficiency: Automate repetitive tasks and save time.
  • Advanced Analysis: Perform complex data calculations with ease with Python libraries like Pandas and NumPy.
  • Better Visuals: Create detailed and dynamic charts and graphs with tools like Matplotlib and Seaborn.
  • Integration: Seamlessly blend Python’s capabilities with Excel’s familiar interface.

Get Started Today!

Download the workbook and start using Python in Excel today! Experience the power and simplicity of Python right in your spreadsheets.

How to Start Using Python in Excel

To use Python Excel, you can use 2 options:

  • Go to the Formulas tab and click “Insert Python” in the Python section.
  • Type in “=PY” in a cell and press Tab.

Send Excel Data to Python – Create a DataFrame

To use Python with your Excel data, you need to create a DataFrame. This makes data manipulation easy and efficient.

💡 Python for Excel works in the cloud, so you’ll need an internet connection to access it.

Here are the steps to create a DataFrame:

  • Type “=PY” in a cell and press Tab
  • Select the data in your table (in our example A2:D98)
  • Press Ctrl + Enter to create the Python DataFrame

What is a DataFrame?

A DataFrame is like a table in Excel or a database. It has rows and columns for storing data. You can use DataFrames to handle large datasets easily. It can hold the content of an entire Excel data table in a single cell.

DataFrames are part of the Pandas library in Python. Pandas provides strong tools for data analysis, making it simple to work with your data.

💡 In Python for Excel, a DataFrame is the default object used for two-dimensional ranges.

Output Types

Python in Excel can output a DataFrame in two ways: as a Python object or converted to Excel values.

Python Objects

Useful for reusing results in future Python calculations. When a DataFrame is returned as a Python object in Excel, the cell shows “DataFrame” with a “PY” in square brackets before it.

Python in Excel dataframe

To see the details within a DataFrame in Excel, click the [PY] icon in the cell to show the card.

Alternatively, press the keyboard shortcut Ctrl+Shift+F5.

Excel Values

You can also output DataFrames translated to the closest Excel format. This allows you to use Excel features like charts, formulas, and conditional formatting.

💡 If a cell in the output range (e.g., F2 to I98) already contains a value, the Python cell will return a #SPILL! error instead of the Python output.

To learn more about #SPILL! errors and how to correct them, see this detailed article.

Controlling Python Output Types in Excel

You can control how Python outputs DataFrames in Excel using the Python output menu in the formula bar.

  1. Click on the cell with the DataFrame.
  2. In the formula bar, look to the left of the green PY icon for the dropdown menu.
  3. Choose the output type: either Python Object or Excel Value.

Alternatively, you can right-click on the cell containing the DataFrame. Then choose “Python Output,” and select the desired output format.

Choosing ‘Python Object’ keeps the data in Python format. ‘Excel Value’ converts it to an Excel-friendly format.

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

How to Use DataFrames in Excel

Describe Data with Pandas

Use the describe() Function in Pandas to get basic statistical insights about your data in the DataFrame. This function provides details such as count, mean, standard deviation, minimum, maximum, and percentiles.

Steps to Use describe() in Excel:

  • Select a Cell: Click on a cell in Excel where you want to display the output, e.g., F4.
  • Enter Python Mode: Type =PY in the formula bar and press Tab to enter Python mode.
  • Reference the DataFrame: Click on the cell that contains your DataFrame, e.g., F2.
  • Use the Describe Function: Add .describe() to the reference and press Ctrl + Enter to run the code.
=PY(F2.describe())
Describe in Python in Excel
  • Change Output Type: Make sure the Python output is set to Excel Data.

Using the describe() function helps you quickly understand the distribution and central tendency of your data, making it easier to identify trends and outliers.

Name Your DataFrame

I recommend giving your DataFrame a name for easier referencing and better code readability. When you name your DataFrame, you can quickly and clearly refer to it in your code. Here’s how to do it:

  • Select the Cell: Click on the cell with the DataFrame.
  • Assign a Name: Update the beginning of the formula by giving it a name, e.g., df.
  • Execute: Press Ctrl + Enter to apply the name

Naming the DataFrame allows you to use a simple reference throughout your code. This is much easier than using the full data frame code each time you need to access it.

Calculations with Python DataFrames in Excel

Simple Sum and Average Calculations

The Pandas library offers many functionalities for data manipulation and analysis. Some simple ones include calculating the sum and average of a column. Here’s how you can perform these calculations in Excel using Python:

  • Select a Cell: Click on a cell in Excel where you want to display the result, e.g., K4.
  • Enter Python Mode: Use the keyboard shortcut Ctrl + Alt + Shift + P to enter Python mode.
  • Reference the DataFrame: Use the name of your DataFrame, e.g., df, and the header of the column you want to calculate, e.g., Sales.
  • Add the SUM() Function: Type the formula to calculate the sum.
 =PY(df.Sales.sum())
  • Execute: Press Ctrl + Enter to run the calculation

This will calculate the total of the Sales column in cell K4.

Similarly, to calculate the average of the Sales column:

 =PY(df.Sales.mean())

Aggregating Data with Pandas Series in Excel

Apart from simple operations like sum and average, you can also easily aggregate your data using Pandas Series. A Series is similar to a single column in a table. It’s a one-dimensional array that can hold data of any type.

For example, you can use the groupby() function to aggregate your DataFrame by date:

  • Enter the Python code: We’ll reference the dataframe “df” and apply the groupby() function to aggregate the sales column by date
=PY(df.groupby("Date").Sales.sum())
  • Change Output Type: In the formula bar, use the dropdown menu next to the green PY icon to change the output from Python Series to Excel data.

You can also change the aggregation easily by using Grouper. A Grouper is a Pandas class that allows you to group data by a specific frequency.

How to Use Grouper:

=PY(df.groupby(pd.Grouper(key="Date", freq="M")).Sales.sum())
  • key: Represents the column you want to group by, in this case, “Date”.
  • freq: Stands for frequency. “M” means monthly, so the data will be aggregated on a monthly basis.

After executing the code with Ctrl + Enter, Excel will display the aggregated sales data on a monthly basis.

Using the Grouper function in Pandas allows you to perform flexible and powerful data aggregations based on different time frequencies. This feature is especially useful for time series data and helps you uncover trends and patterns over specific periods.

Plot a Chart with Pandas plot() Method in Excel

You can use the Pandas plot() method to create charts directly in Excel cells. Here’s how to do it:

  • Group Data: Aggregate your data by date on a monthly basis.
forchart = df.groupby(pd.Grouper(key="Date", freq="M")).Sales.sum()
  • Create the Plot: Use the plot() method to create a line chart.
forchart.plot(x="Date", y="Sales", kind="line")
  • Parameters:
    • forchart: The variable name assigned to the grouped data for the chart.
    • x: Defines which data to show on the x-axis, e.g., the date.
    • y: Defines which data to show on the y-axis, e.g., sales values.
    • kind: Defines the type of chart to plot, e.g., line, area, bar.
  • Increase the size of the chart: Merge some cells next to the cell with the chart.
python in excel plot chart

Featured Course

Automate With Power Query – Recipes to solve business data challenges

Do you want to become more confident using Power Query and automate your entire data workflow? Join this course and learn from real-world scenarios.
Learn More

Calculation Order of Python Cells

In Excel, Python calculations follow a specific order:

  • Top to Bottom: Just like traditional Python code, Python statements in Excel cells calculate from top to bottom within a single cell.
  • Across Rows and Down Columns: In an Excel worksheet, Python cells calculate from left to right across each row. After finishing one row, it moves to the next row.

Understanding this calculation order helps you organize your data and formulas efficiently, preventing errors and ensuring your results are accurate.

Example Setup 1:

  • DataFrame Location: The DataFrame holding the data is in cell F2.
  • Average Calculation: We want to calculate the average in cell K4.

Since Python in Excel calculates from left to right, the setup works because cell F2 (where the DataFrame is defined) is to the left of cell K4 (where we calculate the average).

Let’s see what happens if we move the calculation

Example Setup 2:

  • DataFrame Location: The DataFrame holding the data is in cell F2.
  • Average Calculation: We want to calculate the average in cell B1.

This causes an error because it tries to reference Sales from the DataFrame before it’s defined.

python in excel order of calculation

Key Points to Remember

  • Order Matters: Always place your variable definitions in cells that come before the cells where you use them.
  • Across Worksheets: Organize your worksheets so that data and variable definitions come first, followed by your analysis and calculations.

Example: Extract URL from String with Python

You can find and extract URLs from text data in your Excel sheets using Python and regular expressions (Regex).

Here is the Step-by-Step Guide:

Step 1: Import the ‘re’ Library:

The re library allows you to work with regular expressions in Python, which are powerful tools for pattern matching. Since it’s not part of the default libraries, we need to import it. We will do this in cell A1.

=import re
"re loaded"

Step 2: Define the DataFrame

In this step, we’re sending the Excel data we want to use to Python. Suppose the data is in an Excel table named “Table1” located in cells A3 to A7. We’ll include the headers using the ‘#All’ argument and define the DataFrame in cell B1:

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

💡 By referencing the table, we ensure that all data is included, even if new rows are added later.

Step 3: Apply the Extraction Code

We use Regular Expression (Regex) pattern to extract the URLs.

pattern_combined = r'https://\S+|http://\S+|www.\S+|\b[^ ]+.com\b'
Function to run on each text
def extract_urls(text):
url_pattern = re.compile(pattern_combined)
urls = url_pattern.findall(text)
return ', '.join(urls)
Apply the function to the dataframe
df['URLs'] = df['Feedback'].apply(extract_urls)
Filter out rows without URLs and keep only the URLs column
df_urls_only = df[df['URLs'] != ''][['URLs']]
python in excel with regex
  • The Regex pattern r’https://\S+|http://\S+|www.\S+|\b[^ ]+.com\b’ is used to find URLs in different formats.
  • Use the apply() method to apply the function to the ‘Feedback’ column of the DataFrame.
  • Filter out rows without URL and store the extracted URLs in a new column ‘URLs’.

After executing the code with Ctrl + Enter, we get the extracted URLs displayed in the specified cells.

Pandas Library

For more detailed guides and examples, check out the Pandas library.

Availability of Python in Excel

Python for Excel is currently available in Microsoft 365 to Office Insider Beta Channel users.

The Python in Excel preview is available with a paid Microsoft 365 consumer, commercial, or education license that provides access to the Microsoft 365 desktop apps, such the Office 365 E3 and E5 licenses.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to use Python in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.