Excel PY function

Summary

The Excel PY function integrates Python scripting directly within Excel, allowing users to run Python code in a secure Microsoft Cloud runtime from the Excel grid. This function bridges the gap between Excel and Python, offering a powerful tool for data analysis, manipulation, and extending Excel’s capabilities with Python’s vast libraries and functionality.
Syntax
				
					=PY(python_code, return_type)
				
			
  • python_code: The Python code to be executed. This must be static text.
  • return_type: Specifies the desired return type, where 0 indicates an Excel value and 1 indicates a Python object.
Return value
  • When the return_type is set to 0, the PY function returns the result as an Excel value, translating the Python output to its closest Excel equivalent. This option is ideal for further analysis using Excel’s native features, such as charts, formulas, and conditional formatting.
  • When the return_type is set to 1, the PY function returns the result as a Python object. This allows for retaining the Python data structure and types, which is beneficial for complex data analysis or when the Python output will be used in subsequent Python calculations within Excel. Python objects are indicated in the cell by a card icon, and additional information can be viewed by selecting the card.

How to use

To use the PY function, enter the Python code directly into a cell after typing =PY, and select the PY function from the AutoComplete menu. The cell then becomes a Python editor, where you can write or paste your Python code. The PY function allows referencing Excel objects like ranges, names, tables, and Power Query connections via the custom Python function xl().

Examples

Simple PY
Generating “Hello World”: Using PY to concatenate text strings.
				
					=PY("Hello" + " " + "World", 0)

				
			
Combines three text fragments to generate “Hello World”, demonstrating basic Python string operations within Excel.
PY with Excel References
Accessing Table Data: Retrieving data from an Excel table and returning it as a Python object.
				
					=PY("xl('Table1[#All]', headers=True)", 1)

				
			
Uses the xl() function to obtain data from “Table1” with headers, showcasing how Python can interact with Excel tables directly.
PY for Mathematical Operations
Creating a Multiplication Table: Generating a 2D array representing a multiplication table.
				
					=PY("[[r * c for c in range(1, 4)] for r in range(1, 4)]", 0)

				
			
Utilizes Python list comprehension to create a 3×3 multiplication table, illustrating Python’s capability for complex data structure generation in Excel.
PY for Data Cleaning
Removing Non-Numeric Values: Filtering out non-numeric values from a dataset.
				
					=PY("[x if isinstance(x, (int, float)) else '' for x in xl('A1:C2')]", 0)

				
			
Applies a Python list comprehension with an isinstance() check to filter non-numeric values, enhancing data cleanliness directly within Excel.
PY for Advanced Analysis
Analyzing Data with Pandas: Employing Python’s Pandas library for advanced data analysis (note: this is a conceptual example, as direct external library usage may be restricted).
				
					=PY("import pandas as pd; df = pd.DataFrame(xl('A1:C100', headers=True)); df.describe()", 1)

				
			
Conceptually uses Pandas to describe statistics of data in range ‘A1:C100’, highlighting the potential for sophisticated data analysis through Python within Excel.

Additional Notes

  • The PY function transforms Excel into a more versatile data analysis tool by integrating Python’s power directly into Excel’s interface.
  • It’s important to note that while the PY function offers extensive capabilities, data security and function limitations apply, particularly with the usage of external data and libraries.

Related Functions

Excel LAMBDA function

The Excel LAMBDA function creates custom functions without VBA, enabling complex calculations and reusable formula components.

Excel LET function

The Excel LET function assigns names to calculation results, simplifying formulas and improving performance by reducing repetition.

Content Navigation