Embracing Python in Microsoft Excel: Opportunities and Considerations for Users

Microsoft Excel has many built-in functions for manipulating and organizing data, and on Mac on Windows, you can extend the application even further with the use of VBA scripts. Microsoft is now introducing support for running Python code in your Excel spreadsheet, opening up even more possibilities for data analysis.

Python is one of the most popular programming languages in the world, with a script-like syntax and an emphasis on human readability. It’s especially popular with data scientists for those reasons (and others), so it makes sense that Microsoft would integrate it into Excel over other possible third-party programming languages. The new functionality allows you to use Excel data with popular Python libraries, exporting new data or visualizations which can then be used elsewhere in the spreadsheet. It will be accessible with the new PY() function.

Microsoft said in its announcement, “Now you can do advanced data analysis in the familiar Excel environment by accessing Python directly from the Excel ribbon. No set up or installation is required. Using Excel’s built-in connectors and Power Query, you can easily bring external data into Python in Excel workflows. 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, statsmodels for advanced statistical modeling, and Matplotlib and seaborn for data visualization.”

Machine Learning model predicting the weather using Python and Excel LAMBDA (Microsoft)

Python code in your spreadsheet runs in Microsoft’s own cloud servers, pulling libraries and applications from the Anaconda repository. It’s not clear if Excel will ever support using the Python installation on your own PC, which currently requires workarounds using VBA or third-party solutions like PyXLL . Microsoft also says “some functionality will be restricted without a paid license” after the initial preview period. The upside is that nothing extra needs to be installed, Microsoft doesn’t have to bundle a Python environment and libraries with Excel, and it should eventually work in all versions of Excel.

Python in Excel is now available in the Beta Channel on Windows, and will roll out to all Excel on Windows users starting with version 16.0.16818.2000. Microsoft says the feature will arrive on other platforms “at a later date.” If you don’t want to wait for the stable rollout, sign up for the Microsoft 365 Insider program at the beta level.

Source: Microsoft 365 Blog , Excel Blog

Also read: