Python in Excel

Microsoft has introduced an exciting new feature, Python in Excel, which allows users to embed Python code directly in Excel cells. This integration opens up a world of possibilities for combining data analysis and visualization capabilities with the familiar interface of Excel. Using Python as a tool to enhance your Excel experience is nothing new, but in the past this was only possible by exporting your Excel data into Python, whereas Microsoft’s new implementation allows direct use of Python within Excel, while using cloud technology to up security, but will it be enough? Let's explore how this feature works and its key benefits.

Python code execution in the cloud

Rather than running Python scripts locally, Excel executes the code in a secure, hypervisor-isolated container on Azure Container Instances. This cloud environment ensures enhanced security by preventing access to local resources such as networks, files, and tokens. Microsoft has curated a set of Anaconda libraries, including popular tools like 'pandas' for data analysis and 'Matplotlib' for visualization, which are available within the container.

Yet, there are concerns with this implementation!

Complex scripts may not be compatible between minor updates. Pandas, NumPy, matplotlib are quite complex, meaning Excel’s Python might be different than the Python everyone else is using leading to the same legacy support costs and issues any other out-of-step software system incurs like Visual Basic, COBOL, legacy Java, etc.

Embedding Python code with the 'PY' function

To embed Python code in Excel, users can leverage the new 'PY' function. By using this function, a text area opens where users can enter their Python code. The code is then executed in the cloud container, and the results, including plots and visualizations, are displayed in the worksheet. Importantly, this execution is done anonymously, ensuring user privacy and anonymity.

Integration with Excel analytics

Python in Excel enables the seamless combination of Python and Excel analytics within the same workbook without any additional setup. Users can directly type Python code into a cell, and the calculations are performed in the Microsoft Cloud. The results, including data visualizations, are then returned to the worksheet. This integration streamlines the workflow, eliminating the need for manual data transfers between different tools.

Security considerations

Microsoft treats Python in Excel like other embedded scripting languages and applies security measures to protect users. Documents containing a Mark of The Web (MoTW) are automatically blocked. MoTW flags are added to files downloaded from untrusted sources and trigger Protected View, preventing the execution of macros and embedded Python scripts. This ensures an additional layer of protection against potential threats.

Could Excel files embedded with malicious Python scripts wreak havoc. The use of cloud technology also brings up data privacy and code compatibility issues. Running the scripts external “in the cloud” could inadvertently expose the data sets to others. The other problem is Python tends to be a bit of a moving target even between minor version updates.

Conclusion

Python in Excel is a game-changer, allowing users to combine the power of Python's data analysis and visualization capabilities with the familiar environment of Excel without the need to export your data out of Excel. By executing Python code in the cloud, users can leverage curated Anaconda libraries and seamlessly integrate Python and Excel analytics. This feature brings efficiency, enhanced security, and a new level of flexibility to data-driven tasks. Although currently available to Microsoft 365 Insiders in the Beta channel, Python in Excel is set to enrich the Excel experience for users worldwide in the near future. The ability to remove the need to constantly switch between Python IDEs and Excel Spreadsheet alone could be a game changer for streamlining data processing for many data practitioners.

About HeyDevs

HeyDevs is a revolutionary new platform that empowers developers to take control of their careers, connect with other developers and find job opportunities that align with their goals and interests, without ever needing to apply for a single job! With HeyDevs, companies compete for you, not the other way around.

For developers, HeyDevs offers invaluable resources including our specialized swipe-to-work function, anonymous profile, and customizable search criteria, with the ability to connect and chat with prospective employers completely on-site.

For businesses, HeyDevs offers tools to maximize the recruitment process, saving time and money, with a talent pipeline that streamlines a majority of the hiring process directly on the platform, eliminating the need for recruiters or headhunters so the hiring process can be handled with HeyDevs as the only medium between employer and candidate, all powered by smart matching to ensure the most relevant matches are provided first. HeyDevs is a truly unbiased hiring platform, delivering a diverse pool of talent that hides information such as avatar, age, and gender, focusing solely on experience and skill, and paving the way for a more inclusive workplace.