Get the Python in Excel Practice Workbook
Follow along with the exact file from this tutorial. Build a DataFrame, run describe() and groupby(), plot a chart with pandas, and extract URLs with Regex, all inside Excel.
What is Python in Excel?
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 in 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.
How Do You Turn On Python in Excel?
To use Python in Excel, you can use 2 options:
- Go to the Formulas tab and click “Insert Python” in the Python section.
- Or, type in “=PY” in a cell and press Tab.
How Do You Create a DataFrame in Excel?
To use Excel with Python, 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.
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.
How to Create a DataFrame in Excel
Here are the steps to create a Python DataFrame in Excel:
- 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
Output Types for DataFrames
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.
- 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.
- Click on the cell with the DataFrame.
- In the formula bar, look to the left of the green PY icon for the dropdown menu.
- 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.
Popular Course
Python in Excel For the Real World
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())
- 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.
How Do You Calculate Sum, Average, and Groupby with Python 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.
Prefer to skip the code? You can group dates the same way using a PivotTable instead of Python, useful if you’re working with a teammate who doesn’t use Python.
How Do You Plot a Chart with Python 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.
Why Does Cell Order Matter in Python in Excel?
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. 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.
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.
How Do You Extract URLs from Text with Python in Excel?
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']]
- 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.
For more information about Regular Expressions, check out this detailed article.
Pandas Library
For more detailed guides and examples, check out the Pandas library.
Want more real-world Python in Excel examples? See our guide on 3 Python in Excel tricks, including cleaning messy dates and building Seaborn charts.
Who Can Use Python in Excel in 2026?
Python in Excel is available on Microsoft 365 Business and Enterprise plans with the Python in Excel license.
Standard compute is included at no extra cost and covers everyday cleanup, calculations, and charts. Heavy or fast workloads can use premium compute, a paid add-on.
Availability continues to expand across plan tiers, so confirm your specific license includes it under File > Account.
How Does Copilot Write Python in Excel for You?
As of April 2026, Edit with Copilot in Excel can write and run Python for you.
Ask Copilot to analyze your data in plain language, for example “forecast sales for next quarter,” and it generates the Python code, runs it in the secure cloud sandbox, and inserts the result, including charts, directly into your sheet.
You can also invoke Python manually inside a Copilot prompt if you want more control over the analysis. Copilot’s Plan mode, rolled out in May 2026, shows you what it intends to change before it touches your workbook.
Frequently Asked Questions
How do I turn on Python in Excel?
Go to the Formulas tab and click Insert Python, or type =PY in a cell and press Tab.
What is a DataFrame in Python in Excel?
A DataFrame is a table-like object with rows and columns, similar to an Excel range or a database table, used to store and manipulate two-dimensional data.
Why does my Python in Excel formula return an error?
Python cells calculate left to right, then top to bottom. If a formula references a DataFrame before the cell that defines it, Excel throws an AttributeError. Place your DataFrame definition in a cell before any cell that uses it.
What’s the difference between Python Object and Excel Value output?
Python Object keeps the result in Python format for reuse in further Python calculations. Excel Value converts the result into native Excel data so you can use it with formulas, charts, and conditional formatting.
Why does describe() show #NUM! for a date column?
Standard deviation can’t be calculated on datetime values, so pandas returns an error for that cell. This is expected behavior, not a bug. Focus on count, min, and max for date columns instead.
What Microsoft 365 plan do I need for Python in Excel?
Microsoft 365 Business and Enterprise plans with the Python in Excel license. Standard compute is included; premium compute is a paid add-on for heavy workloads.
Can Copilot write Python code for me in Excel?
Yes. Edit with Copilot can generate and run Python code from a plain-language prompt, or invoke Python automatically when it determines that’s the best approach.
Download the Workbook
Download the practice workbook and run every DataFrame, groupby, and chart example yourself.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
