This guide will explain how to use the PY function in Microsoft Excel.
Table of Contents
Python is a powerful programming language that is known as an excellent tool for data analysis.
Python’s vast library of functions allows users to create visualizations, clean data, perform machine learning, and so much more.
Excel is slowly rolling out a feature that will allow users to run Python code in Excel using the PY function. With this function, users will be able to type Python code directly into a cell and have the code run in the Microsoft Cloud.
In this guide, we will provide a step-by-step tutorial on how to access the PY function. We will cover how to start running a Python script and how to convert your data into a DataFrame object.
The Anatomy of the PY Function
The syntax of the PY function is as follows:
=PY(python_code,return_type)
Let’s look at each argument to understand how to use the PY function.
- PY() refers to our
PYfunction. This function allows us to run Python code on a secure Microsoft Cloud runtime. - python_code refers to the Python code you wish to run. This value must be static text.
- The return_type parameter allows us to specify the desired return type. A value of 0 indicates an Excel value, while a value of 1 indicates a Python object.
- Do note that the
PYfunction cannot be used with any other Excel functions.
A Real Example of Using PY Function in Excel.
Let’s explore a few examples that use the PY function in Excel.
Using PY Function With Excel Cells and Ranges
For our first example, let’s try using Python code to add two cell values together.
In the table above, we want to add the values in cells B1 and B2 and return the result in cell B4.
To enable the Python editor in Excel, select a cell and type “=PY(“. The formula bar and selected cell will display a green PY icon to indicate you are now able to add Python code.
Python in Excel uses a function named xl() to reference Excel objects. While in the Python Edit mode, you can add cell references to cells and ranges by selecting the cell or range.
You can also directly type references yourself. For example, if you want to store the value in cell A1 in a variable labeled x, you can enter the code x = xl(“A1”).
We can use the following Python code to return the sum of cells B1 and B2:
x = xl(“B1”)
y = xl(“B2”)
x+y
To evaluate the Python code, hit the shortcut Ctrl+Enter. In our example above, we find out that the sum of our two values is 65.
Changing the Output Type
Python in Excel allows users to set how Python calculations are returned. The output of the PY function can be returned as either a Python object or as an Excel value.
Output returned as Excel values are translated to their closest Excel equivalent. Returning an Excel value also allows users to perform other Excel features using that data.
Output returned as a Python object contains additional information, which can be viewed by selecting the card icon.
Python in Excel DataFrames
A DataFrame is a type of data structure that comes with the pandas Python library. Much like spreadsheets, DataFrames sort data into rows and columns and are compatible with a variety of other data analytics functions that are included in the pandas library.
Python in Excel allows users to easily convert spreadsheet data into a DataFrame.
For this example, we’ll use a dataset containing measurements from specimens of different species of iris flowers. We want to add our dataset into a Python DataFrame object for further analysis.
We can use the following formula to convert our dataset in cells A1:E151 into a DataFrame:
df = xl("A1:E151", headers=True)
The function will return a DataFrame object stored in a df variable.
We can run the code df.sample(10) to return 10 random rows from our DataFrame object.
Click on the link below to create your own copy of our examples.
Head to the next section to read our step-by-step tutorial on how to get started with Python in Excel.
How to Use the PY Function in Excel
- Select a blank cell and type “=PY(“ to set the cell to Edit mode. You may also use the keyboard shortcut Ctrl+Shift+Alt+P.
- You may also Insert Python code in Excel by selecting Formulas > Insert Python > Python in Excel.
- Type the Python code you wish to run in your current Excel sheet.
In this example, we’ll create a new DataFrame object df using data from a specific cell range. Afterward, we’ll use the sample method to return 25 random entries from our dataset. - Hit Ctrl+Enter to evaluate the Python code. After a few seconds, the formula will return a DataFrame object.
- To view the actual data, we will need to convert our output to Excel values. To do this, click on the card icon next to the formula bar and select the Excel Value option.
- Excel will now show the actual data stored in our Python DataFrame.
These are all the steps you need to know to start using the PY function in Excel.
FAQs
- Why is the PY Function not accessible to me?
As of September 2023, you’ll need to join the Microsoft 365 Insider Program to use the Python in Excel feature. This program allows all subscribers to have access to the latest features in Excel. Once you’ve signed up for the Insider program, choose the Beta Channel Insider level to access Python in Excel features. - Do I need to install Python to use Python in Excel?
No, it is not required for users to have Python installed on their device to use the PY function. The Python in Excel feature runs the code in the Microsoft Cloud. Because of this implementation, the PY function requires internet access to run.
If you’re using Excel for data analysis, you might be interested to read our post on how to use ChatGPT for data analysis. You can also read our guide on performing multiple linear regression in Excel.
That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!
