Python in Excel: Elevating your data analysis capabilities

(Originally published on August 28, 2023 by Ndeyanta Jallow)

 

Hello, Microsoft 365 Insiders! My name is Ndeyanta Jallow and I’m a Product Manager on the Excel team. I am excited to share with you the availability of the Public Preview of Python in Excel – which makes it possible to integrate Python and Excel analytics within the same Excel grid for uninterrupted workflow.

 

IMPORTANT: Python in Excel is now available to all Enterprise, Business, Education, and Family and Personal users running Beta Channel on Windows. This feature will roll out to Excel for Windows first, starting with Version 2309 (Build 16818.20000), and then to other platforms at a later date. Sign up to be notified about future availability for Python in Excel.  

 

(6/10/24) UPDATE: Python in Excel is gradually rolling out to Enterprise, Business, Education, and Family and Personal users running Current Channel (Preview) builds on Windows. This feature is rolling out to Excel for Windows, starting with Version 2406 (Build 17726.20016).

 

Python in Excel: Bringing together the best of both worlds

 

Excel is an essential tool to organize, manipulate and analyze all kinds of data, while Python is one of the most popular programming languages today, loved by businesses and students alike - used for data analysis, visualization, machine learning and more.

 

We are excited to introduce the Public Preview of Python in Excel – combining Python's powerful libraries with Excel's features you know and love. We hope you explore the possibilities to analyze data in Excel using Python while leveraging Excel's formulas, conditional formatting and PivotTables to further refine your insights. For more details, look at our announcement blog. No set up or installation is required.

 

Python code in Excel worksheet

 

We’re partnering with Anaconda, a leading enterprise grade Python repository used by tens of millions of data practitioners worldwide. Python in Excel leverages Anaconda Distribution for Python running in Azure, which includes the most popular Python libraries such as pandas for data manipulation, stats models for advanced statistical modeling, and Matplotlib and seaborn for data visualization. To learn more, see Open-source libraries and Python in Excel.

 

Give it a try and let us know what you think!

 

How it works

1. Create an Excel worksheet or open an existing one, select a cell, and then select Formulas > Insert Python.
2. In the dialog box that appears, click the Try preview button.

   Try Python in Excel dialog box

    

   You can also enable the Python in Excel preview by entering =PY into an Excel cell and then choosing PY from the function AutoComplete menu. Selecting the PY function will trigger a dialog that allows you to enable the preview.

 

NOTE: Python in Excel uses the custom Python function xl() to interface between Excel and Python. The xl() function accepts Excel objects like ranges, tables, queries, and names.  To reference Excel objects in a Python cell, make sure the Python cell is in Edit mode, and then select the cell or range that you want to include in the Python formula.

 

Python code used by Excel runs on the Microsoft Cloud with enterprise-level security as a compliant Microsoft 365 connected experience. The Python code runs in its own hypervisor isolated container using Azure Container Instances and secure, source-built packages from Anaconda through a secure software supply chain. Python in Excel keeps your data private by preventing the Python code from knowing who you are, and opening workbooks from the internet in further isolation within their own separate containers. Data from your workbooks can only be sent via the built-in xl() Python function, and the output of the Python code can only be returned as the result of the =PY() Excel function.

 

Scenarios to try

You can directly type formulas into a Python cell that references cells with the xl() function. For example:

• To reference cell A1, type xl("A1")
• To reference range B1:C4, type xl("B1:C4").
• For a table with headers named MyTable, type xl("MyTable[#All]", headers=True).
  • [#All] ensures that the entire table is analyzed in the Python formula. To learn more about specifiers like [#All], see Using structured references with Excel tables. 
  • headers = True ensures that the table headers are processed correctly.

 

See how this scenario works as we reference data in the Excel grid to create a DataFrame below.

How to use Python in Excel

 

In addition to creating DataFrames, we can also use Python functions to understand datasets, model and predict data and even create Python plots. To see this in action, take a look at the analysis done with the Iris dataset: Python in Excel - Dive into Data

 

Tips and Tricks

• Visit aka.ms/expygetstarted to download a Getting Started Template where you can learn more about how variables, plots, shortcuts and more work with Python in Excel.
• Expand the formula bar for multi-line code-like editing behavior, use the Enter key to add lines, and commit your code to a cell by using Ctrl + Enter.
• Use the Python Output menu in the formula bar and right-click menu to control how Python calculations are returned– as Python objects or Excel values. 
• To import external data, use the Get & Transform feature in Excel which leverages Power Query. To learn more, see Using Power Query data with Python in Excel.
• Unlike traditional Python statements, Excel worksheets are 2-dimensional. They contain both rows and columns. In a Python in Excel worksheet, Python cells calculate in row-major order. This means that the cell calculations run across a row (either left-to-right or right-to-left depending on your Excel language settings), and then across each following row down the worksheet.  
• When the dependent value of a Python cell changes, all Python formulas are recalculated sequentially. To suspend Python recalculations and improve performance, use either Partial Calculation or Manual Calculation by selecting Formula > Calculation. 

 

Availability

• Python in Excel is now available to all Enterprise, Business, Education, and Family and Personal users running Beta Channel on Windows. This feature will roll out to Excel for Windows first, starting with build Version 2309 (Build 16818.20000), and then to the other platforms at a later date.

• (6/10/24) UPDATE: Python in Excel is gradually rolling out to Enterprise, Business, Education, and Family and Personal users running Current Channel (Preview) builds on Windows, starting with Version 2406 (Build 17726.20016).

The feature is not available in Excel for Mac, Excel on the web, Excel for iOS, or Excel for Android. On unsupported platforms, workbooks containing Python can be viewed but Python cells display an error.  

 

Sign up to be notified about future availability for Python in Excel.  

 

IMPORTANT: While in Preview, Python in Excel will be included with your Microsoft 365 subscription. After the Preview, some functionality will be restricted without a paid license. More details will be available before General Availability.

 

 

Feedback

As you use Python in Excel, please share your feedback and help us prioritize our work by clicking Help > Feedback and letting us know what you think. 

 

---

Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!