Microsoft announces Python integration in Excel; Know what’s coming

Launched in 1985, Microsoft Excel has been a powerful tool for businesses and individuals for almost 4 decades, and Microsoft has capitalized on this advantage by offering new features that attract and retain more users. It allows users to organize, analyze, and visualize their data in a better way. In June, Microsoft announced several major updates to its spreadsheet editor such as improvements in visualization, leveraging Office Scripts, recommended formulae, and more.

Now, Microsoft has announced more features that are coming to Excel with the integration of high-level programming language Python.

Microsoft Excel gets Python integration

Providing a major boost to its analytical capabilities, Microsoft on August 22 announced that it is bringing Python to Excel through a Public Preview. It will be natively integrated into the Excel grid and can be accessed using the Excel ribbon, without needing it to be separately installed. Users will be able to take advantage of its functions by inputting Python code directly into cells using the PY function, Microsoft announced in a blog post.

Users can use Power Query and the connectors built in Microsoft Excel to bring over data from external sources to Python-enabled Excel sheets. It will be compatible with existing tools such as charts, formulae, PivotTables, and more. Python in Excel will use Anaconda Distribution running via Azure to provide analytics services such as predictive analytics, visualization using Matplotlib and Seaborn, cleaning data, and more.

Python in Excel will also leverage machine learning, forecasting techniques, and predictive analytics based on Python libraries such as scikit-learn and statsmodels to provide a variety of services such as time series modelling, regression analysis, and more.

Stefan Kinnestrand, General Manager of Product Marketing at Microsoft said, “Today, we are excited to introduce the Public Preview of Python in Excel – making it possible to integrate Python and Excel analytics within the same Excel grid for uninterrupted workflow.”

How to use Python is Excel

Step 1: Open a spreadsheet and select the cell where you want to apply the formula.

Step 2: Go to the formula ribbon and select ‘Insert Python’ option. You can also use the PY function to directly enable Python in that cell. If it is enabled, the cell will turn green and display a PY function.

Step 3: To reference Excel elements within a Python cell, make sure that it is in Edit mode, and then select the individual or range of cells you wish to apply the Python formula to.

• Python in Excel takes advantage of the Python function xl() to establish a connection between Excel and Python, and it accepts objects such as ranges, tables, queries, and more. It's also possible to directly input references into a Python cell using the xl() function.
• To access code-like editing functions such as using the Enter key to generate new lines of code, users can utilize the formula bar. You can also expand it to view multiple lines of code simultaneously using Ctrl+Shift+U.
• To control how calculations are returned with Python in Excel, users can access the Python output menu located in the formula bar. You can either receive calculations as Excel values and output them to a cell or get them as Python objects containing additional information within cells.
• Users can also import external data into Excel using the ‘Get & Transform’ feature, although the data should either be imported from a power query or Excel workbook.

Microsoft announced that users will be able to share Python in Excel workbooks with their teammates across platforms like Outlook and Teams without the need for any additional tools. As part of Microsoft 365, it runs with enterprise-level security on Microsoft Cloud. The Python in Excel feature is currently being rolled out in Public Preview for Microsoft 365 Insiders program users. They can access it using the Beta channel in Microsoft Excel.