Microsoft Integrates Python into Excel: Analyst View

Python has become established as a dynamic programming language, boasting capabilities spanning from data analysis to artificial intelligence applications. With this in mind, the recently introduced Python Editor in Excel – the widely used spreadsheet tool – calls for closer examination and analysis.

My IT department cautioned me against using the Microsoft 365 Insider Beta Channel, which offers an early test of this new feature. “Too many bad experiences and issues in the past,” they warned. As I have to be patient and await the official release, I have reviewed the experiences of some of those who weren’t so cautious.

Excel, despite its robustness for individual users, can inadvertently introduce shadow IT issues within organizations due to its convoluted functions. At the same time many enterprise number crunchers are reluctant to abandon Excel, and the Python integration will only strengthen their resolve. Meanwhile, new approaches to getting back on top of data are moving away from demonizing the use of Excel. A prominent example of this is data mesh.

Initial observations

• Intuitive Python integration: The seamless “=PY()” function enables each cell to become a programming interface. You just have to be careful not to refer to Python objects defined to the right or below the cell in question (i.e., against the reading direction). An Excel table can be easily defined as a Pandas DataFrame object by selecting a range and giving it a name with which it can be referenced in Python.
• Expansive package integration: The possibility of integrating Python packages is not limited to the standard libraries for data processing and visualization (Pandas, Numpy, Matplotlib, Seaborn etc.). There seem to be hardly any limits to the extension of Excel’s functionality. From the automated extraction of text using regular expressions to the training of machine learning models, everything seems possible.
• Value addition for Python professionals: Seasoned Python developers can leverage this feature for manual data manipulations, especially during the prototyping phase and for experimenting.
• Collaboration and performance concerns: Excel will probably remain a tool for loners, since no significant measures have been taken to improve the documentation and traceability of work steps. The question of whether Excel’s performance is also sufficient for more complex calculations and transformations with Python cannot be answered conclusively at this point and still requires in-depth testing.

Anticipated outcomes

• Simplifying the learning curve: By embedding Python within Excel, a significant portion of Excel’s vast user base may find Python more accessible. Tools like ChatGPT further mitigate learning challenges, possibly boosting data literacy in businesses.
• Elevating Excel’s analytical capabilities: With Python, complex models can now be built effortlessly in Excel. This will likely turn Excel into the advanced analytics tool of choice for many.
• Potential for chaos and data silos: Excel’s new data science capabilities should be solely used for prototyping or experimenting. Otherwise, it might result in increased chaos and data silos within companies.

Concluding thoughts

The integration of Python in Excel is a double-edged sword. It offers immense potential for individual users and data analysts, but organizations must be wary of the pitfalls. Proper training, guidelines and best practices will be crucial to ensure that this integration brings more benefits than harm.