Python Coming to Excel: 10 Things to Know

Microsoft revealed yesterday the much-awaited Python in Excel feature that will allow users to combine Python and Excel analytics within the same workbook. Fro

1. Python in Excel is Built for Analysts

Python in Excel is a groundbreaking feature designed to natively integrate directly into the Excel grid. With this integration, users can input Python code into Excel cells, allowing for powerful analytics for visualizations, data cleaning, machine learning, predictive analytics, and more.

Excel users now have access to Python’s capabilities for creating advanced visualizations, leveraging machine learning models, and employing efficient data cleaning techniques. This seamless combination of Excel and Python opens new doors for end-to-end solutions within the same workbook, enhancing the analytical capabilities of Excel.

2. How to Enable Python in Excel

Enabling Python in Excel is a straightforward process. Users must join the Microsoft 365 Insider Program and choose the Beta Channel Insider level. Once this is done, the Python preview can be enabled by selecting “Formulas” in the ribbon and then “Insert Python” or by entering =PY into an Excel cell.

This feature is currently rolling out to Public Preview for those in the Microsoft 365 Insiders program, using the Beta Channel in Excel for Windows. This step-by-step process ensures that users have access to the latest builds and can explore the Python integration within Excel.

3. Writing Python Code in Excel

Writing Python code in Excel is as simple as selecting a cell and typing the code. Once Python is enabled in a cell, it displays a green “PY” icon, indicating that the cell is ready for Python code. Users can combine Python with Excel cells and ranges using the custom Python function xl().

This integration allows for advanced visualizations using libraries like Matplotlib and seaborn, machine learning with scikit-learn, predictive analytics, and efficient data cleaning techniques. The ability to write Python code directly within Excel enhances the user experience and opens new possibilities for data analysis.

4. Using Python with Excel Objects

The xl() function serves as a bridge between Excel and Python, accepting Excel objects like ranges, tables, and queries. Users can directly type references into a Python cell with this function, allowing seamless integration with familiar Excel tools like formulas, PivotTables, and charts.

This compatibility ensures that users can leverage the best of both Excel and Python, creating a unified workflow that enhances productivity and analytical capabilities.

5. Formula Bar for Python Code

Excel’s formula bar has been enhanced to provide code-like editing behavior for Python. Users can create new lines and expand the formula bar to view multiple lines of code, making coding within Excel more intuitive and user-friendly. This feature enhances the coding experience within Excel, allowing users to write and edit Python code with ease, similar to working in a traditional code editor.

6. Python Output Types

Users have control over how Python calculations are returned to Excel. The results can be displayed either as Python objects or as Excel values directly to a cell. The output type can be changed using the right-click menu or the Python output menu in the formula bar, providing flexibility in displaying results. This control ensures that users can tailor the output to their specific needs, whether they want to further manipulate the data within Python or display the results directly within an Excel cell.

7. Importing External Data

Importing external data into Python in Excel is made easy with Excel’s Get & Transform feature and Power Query. Unlike common external data functions in Python, Python in Excel is compatible with Excel’s built-in connectors, allowing easy integration with external data sources. This compatibility ensures that users can easily bring external data into Python in Excel workflows, enhancing the data analysis process and enabling more comprehensive insights.

8. Calculation Order in Python Cells

Understanding the calculation order in Python cells is vital for proper code execution. Python cells calculate in row-major order in a Python in Excel worksheet. This order is essential when defining and referencing variables, ensuring that variables are defined before they are referenced. This understanding of calculation order helps users write efficient and error-free code within the Excel environment.

9. Recalculation Options

Users have the option to suspend Python recalculations to improve performance using either Partial Calculation or Manual Calculation mode. These modes provide control over the recalculation process, allowing users to trigger calculations when ready. This control ensures that users can manage the performance of their Excel workbooks, especially when working with large datasets or complex calculations.

10. Python in Excel Runs Securely on the Microsoft Cloud

Python code used by Excel runs on the Microsoft Cloud with enterprise-level security. The code runs in isolated containers using Azure Container Instances, ensuring data privacy and compliance with Microsoft 365 connected experience. This secure environment enhances collaboration and sharing of Python in Excel workbooks, providing peace of mind for users working with sensitive data.