PY function
Applies To
Excel for Microsoft 365After entering =PY into a cell and selecting the PY function from the AutoComplete menu, the cell editing experience transforms into a Python editor. To learn more, see Introduction to Python in Excel and Getting started with Python in Excel.
Important: The PY function cannot be directly entered or seen from Excel's formula bar, instead the Python editor will generate it and insert it into the cell for you. The PY function syntax is primarily used by addins to direcly insert or read Python formulas. It can also be seen when using the FORMULATEXT function on a cell containing a Python formula.
Syntax
The PY function runs Python code on a secure Microsoft Cloud runtime. The result is returned as either a Python object or an Excel value. The below syntax
=PY(python_code,return_type)
|
Argument |
Description |
|
python_code (required) |
The Python code to run on a secure Microsoft Cloud runtime. Must be static text. |
|
return_type (required) |
Specify the desired return type. Must be a static number 0 or 1.
|
Excel references
The Python code can reference values in Excel by using the custom Python function xl().
The xl() function supports referencing the following Excel objects.
-
Ranges
-
Names
-
Tables
-
Power Query connections
The xl() function supports an optional headers argument. The headers argument specifies whether the first row has headers. For example, xl("A1:Z10", headers=True) indicates that cells A1:Z1 are headers.
Remarks
-
The PY function can be seen by FORMULATEXT and written and read by add-ins.
-
The PY function is not visible in the formula bar as it be displayed using the Python editor. is automatically set.
-
The PY function cannot be used with any other Excel functions. If used with other functions, a formula cannot be entered.
-
Both the python_code and return_type arguments must be static.
Examples
Example 1 uses the Python plus operator to generate "Hello World" from 3 text fragments.
=PY('Hello' + ' ' + 'World',0)
———————————————————————————
Example 2 uses the xl() function to obtain the data from Table1 and returns the result as a Python object.
=PY("xl(""Table1[#All]"", headers=True)",1)
Need more help?
Want more options?
Explore subscription benefits, browse training courses, learn how to secure your device, and more.
Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.
