Learn how to use the PY function in Excel to use the power of the Python programming language for data analytics and visualization in the Microsoft Excel desktop app.

Before the integration of Python in Excel, you had to follow several individual steps to incorporate an Excel dataset into a Python script. This included converting an XLSX file to a CSV, using the pandas library to read the CSV file, and manually copy and paste Excel data into a Python program using the Jupyter Notebook, among other tasks.

With the introduction of the PY function in Excel, you can directly work on your Excel datasets using various Python codes without leaving the familiar Excel user interface. Also, you no longer need to configure your PC for Python, as Excel now has a built-in cloud-based Python engine.

Read this Microsoft Excel tutorial until the end to learn the basic and intermediate functions, features, and techniques of Python in Excel, enabling you to extract functional data insights effortlessly.

What Is Python in Excel?

Python in Excel is a cloud-based Python instance running on the Microsoft Cloud which preloads the following open-source libraries for data manipulation:

1. NumPy: A fundamental library for numerical computing in Python, providing support for large multi-dimensional arrays and matrices and a collection of mathematical functions to operate on them.
2. Matplotlib: A versatile plotting library for creating static, animated, and interactive visualizations in Python, enabling users to generate high-quality graphs and charts easily.
3. pandas: A powerful data manipulation and analysis library that offers data structures like DataFrames for handling and analyzing structured data, making it easier to clean, transform, and visualize data.
4. statsmodels: A library for estimating and testing statistical models in Python, providing classes and functions for regression, statistical tests, and data exploration.
5. seaborn: A data visualization library based on Matplotlib that simplifies the creation of attractive and informative statistical graphics, offering built-in themes and color palettes for improved aesthetics.

The feature allows you to run Python scripts in an Excel worksheet to slice and dice your data in many ways that Excel itself won’t offer. Also, integrating spreadsheet data becomes ridiculously easy.

Python in Excel can interact with the following elements:

• Numeric values
• Date and time
• Text strings
• Named ranges
• Tables
• Power Query data connections

At the time of writing, Python in Excel was only available for Excel for the Microsoft 365 desktop app on Windows only. It’s not available for macOS, iOS, and Android devices. Also, only the following subscription tiers could use the PY function Excel:

• Enterprise and business users
• Monthly enterprise
• Family and personal users
• Education subscription

You must use the Current Channel Preview edition of Excel for enterprise, business, family, and personal subscriptions. However, if you’re an education subscription holder, you must sign up for the Microsoft 365 Insider Program for Excel 365.

As of now, Python in Excel is included in the current subscription price without any additional fee. However, when the feature becomes generally available, Microsoft might put some of the premium features behind a paywall.

How to Enable Python in Excel

If you’re using the Excel for Microsoft 365 desktop app on a Windows 10 or 11 PC and don’t see the Python block or the Insert Python command in the Formulas tab, follow these steps:

1. Open the Excel desktop app.
2. Click on the File tab and select Account at the bottom of the Excel backstage preview.
3. You’ll enter the Account window.
4. Click on the Microsoft 365 Insider drop-down menu and select the Current Channel (Preview) option.

5. Follow on-screen guidance.
6. Microsoft 365 dialog box will show up asking you to end all open Microsoft apps.
7. Once you comply, it’ll download the latest Current Channel (Preview) build for all of the Microsoft 365 apps.
8. The updates will install automatically.

9. You must restart Excel to see the Insert Python command in the Python block of the Excel Formulas tab.

Different Ways to Call the PY Function in Excel

Firstly, you can select any cell where you’d like to place and execute a Python code and navigate to the Python block in the Formulas tab from the Excel ribbon menu.

Click on the Python icon or the Insert Python command.

A Microsoft Excel warning dialog box will show up. Checkmark the checkbox for Don’t show this message again and click OK.

The cell and the formula bar for the corresponding cell will show a dark green PY band. This is the Python scripting interface on Excel.

If the formula bar area can’t accommodate the entire script, you can click on its bottom edge and drag the cursor down to increase the coding area.

Another way to activate the Python coding interface in an Excel cell is by calling the PY function.

You can type =PY to bring up a formula suggestion box.

Hit the Tab key to get the code editing cursor.

Finally, you can select the destination cell and press the Ctrl + Shift + Alt + P keys to enable Python coding.

How To Use PY Function in Excel

Now that you’ve learned the basics of navigating the PY function, let me walk you through the steps to add datasets to the Python engine, visualize different output types, use the dataset, and load a new Python library below:

Exporting Data to the Excel Python Engine

Before you can analyze and visualize your Excel dataset using the PY function, you must send it to the Python engine. This is called creating a Python DataFrame.

You must try to create it in the first cell of the first worksheet. Then, you can easily use the same DataFrame in the succeeding rows, columns, and even in different worksheets of the same workbook.

Your working dataset could look like the one shown above.

Insert a new row above A1 by right-clicking the existing A1 row and choosing the Insert option.

Select A1, enter the PY function, and press the Tab key to enable Python coding.

Enter the following formula syntax in the formula tab and hit Ctrl + Enter.

df=xl("A2:E12", headers=True)

Customize the cell range inside the formula according to your own dataset. Here, the codelet df defines the dataset in A2:E12. From now on, you can simply mention df in all the Python scripts to use the A2:E12 in data manipulation activities.

You should see a Python object named DataFrame.

That’s it! You’ve successfully imported a dataset from the Excel worksheet into the Python engine.

Output Types in Python Excel

Excel will show Python objects for script execution results, like a DataFrame, Series, Tuple, and so on.

If you wish to see the underlying data, select the cell containing the Python object you want to inspect.

Click on the drop-down menu to the left of the formula bar.

Select the Excel Value option.

The underlying dataset will show up as a spill range.

Another effortless way to change the Python output type is by right-clicking on the Python DataFrame cell, hovering the mouse cursor over the Python Output menu, and selecting the Excel Value option from the overflow menu.

Using a Python DataFrame in Excel

Now that you’ve created the DataFrame, you can use it to get a detailed statistical analysis of your input dataset.

Go to a cell where you’d like to populate the statistical analysis spill range.

Insert a PY function.

Now, enter the following formula inside the code editor:

df.describe()

Hit Ctrl + Enter to execute the code.

If you don’t see the statistical analysis instantly, change the Python Output value to Excel Value.

This is how you can use the describe() method of the pandas library to effortlessly get an elaborate analysis of any statistical survey data in Excel.

Loading an Open-Source Library in Python in Excel

If you need to use a method or function from a different Python library that Microsoft Excel doesn’t load automatically, you can do so by using the keyword import.

Let’s say you want to get the value of Pi with up to 9 decimal places.

Select a cell on the active worksheet and enter the PY function.

Now, type in the following formula:

import math as mt
mt.pi

Hit Ctrl + Enter to calculate Pi.

Basic Calculations With A PY DataFrame in Excel

Find below the easy steps to perform some basic calculations and data analytics using the Excel Python module:

Sum

Suppose, you’ve added a DataFrame for the above dataset and want to calculate the total for the Price column using the PY function.

Select the destination cell and activate Python coding using the PY function.

Now, enter the following formula inside the selected cell:

df.Price.sum()

Hit Ctrl + Enter to calculate the sum.

To add the currency formatting, switch the output into an Excel Value.

Now, press Ctrl + 1 to bring up the Format Cells dialog and select your preferred currency formatting from the Currency category.

Excel will show the chosen currency.

Mean

To calculate an average using the PY function you can use the following formula in the selected cell:

df.Price.mean()

If the cell contains a special formatting already, the output value will be adjusted accordingly.

Aggregate

Besides the basic calculations, you can also perform intermediate-level data analytics, like grouping revenue values from a dataset by date, month, week, etc.

Here, you can use the groupby() method from the pandas library.

For instance, you’d like to find the sum of revenue by date entries in the above sample dataset. Let’s consider, you’ve already created the df DataFrame.

In A2, enter the following formula:

df.groupby("Date").Revenue.sum()

Press the Ctrl + Enter keys to perform the aggregation task.

If you don’t see the aggregate yet, change the output mode to Excel Values.

Python engine will show a spill range for aggregates of the Revenue column by the Date column.

Merge

Merging two datasets into one large dataset is fairly easy with the Excel Python integration.

For example, you’ve got the above datasets where the column Age is the common parameter.

Let’s consider, you’ve already created two Python DataFrames named df1 and df2 using the first and second datasets respectively.

To merge these on the Age column as a spill range in G3, enter the following formula:

pd.merge(df1, df2, on='Age')

Press Ctrl + Enter to execute the Python script.

Select the Excel Value output from the formula bar drop-down menu.

You should now see the merged dataset.

Creating a Data Visualization in Excel Python

Creating accurate charts in Excel can be challenging when you use the built-in charting tool. Instead, you can use the PY function to draw cool graphs. For this purpose, you also need to use the Matplotlib library.

You can use any compatible dataset, like the one displayed above, create the required DataFrame, and assign it the name df.

Now, go to C2, enable Python coding, and type in this Python script:

plt.figure(figsize=(10, 6))
plt.plot(df["Age"], df["Median Salary (USD)"], marker="o", color="b", linestyle="-")
plt.title("Median Salary by Age in the USA")
plt.xlabel("Age")
plt.ylabel("Median Salary (USD)")
plt.grid(True)
plt.xticks(df["Age"])  # Ensure all ages appear on x-axis

plt.show() 

Hit Ctrl + Enter to create the Python Image object.

Use the formula bar drop-down menu to switch to the Excel Value output. You should now see an in-cell graph.

Right-click on C2, hover the cursor over Picture in Cell, and select Create Reference.

You should now see a larger version of the chart.

Conclusions

If you’re here, congratulations! You’ve learned how to use the PY function in Excel to create data analytics insights and visualize those in a graph.

Did this Microsoft Excel tutorial on Python in Excel help you? Do share your acknowledgment in the comment box. If you know something cool about Excel Python scripting, share that in your comment.