Over the years, I’ve been a strong advocate for the integration of Python into the workflow of Excel users, followed by the incorporation of AI technologies. Microsoft has now directly integrated both Python and AI into Excel, signaling agreement from the product managers, despite some ambivalence or disagreement among many Excel users.
In this post, I aim to explore the notion that while each of these technologies might have had limitations when used separately, they appear to complement each other effectively when combined.
Let’s start with the integration of Python into Excel.
Python and Excel… too hard
For many years, there was a notable interest in running Python directly within Excel. I’d describe this interest as decent rather than universal. Some developers were keen on this integration as it allowed them to leverage tools like unit testing and code packages they were becoming accustomed to with the rise of Python, agile methodologies, open source tools and other trends. On the other hand, data scientists saw potential in using Python for tasks like clustering or outlier detection, aiming to present their work within the familiar environment of Excel.
However, for many Excel users, the proposition was less appealing. The conversation often went something like this:
The prospect of learning a new programming language from scratch for potentially limited use cases, coupled with the uncertainty of being able to actually use Python due to IT restrictions, made it a hard sell.
Then came the announcement of native Python integration into Excel in the fall of 2023. This move alleviated many of the previous hesitations about using Python. However, while the tool didn’t cover all possible uses of Python in Excel, namely most automation and development scenarios, it did make Python less of an outsider in the Excel user’s toolkit.
Initially, if we look at Google Trends data, there was a brief surge of interest, but it seems to have waned since then and perhaps even declined further than the pre-announcement trends.
What might have caused this? I believe that even with Python integrated into Excel, once users explored it, they reverted to the mindset of “it’s too hard, and I can do most of this in Excel anyway, or at least the easy parts I’m interested in learning. The hard parts remain too complex.”
Moreover, around this time, an interesting trend began to emerge with the rise of generative AI, which might have made people even more reluctant to learn coding from scratch. Despite the efforts to integrate Python into Excel, it still hasn’t fully convinced many users. It’s perceived as too challenging or too niche. So, let’s explore how Copilot might fit into this evolving landscape next.
Copilot and Excel… too easy
Copilot for Excel was released to the general public about six months later in the spring of 2024. Initially, many were quite disappointed for various reasons, including its slow and unreliable performance. Copilot in Excel presented a bit of a Catch-22 situation: to effectively use Copilot to “drive” Excel, you need a strong understanding of Excel itself, including a good intuition and mental model of how it works. However, if you’re already that proficient in Excel, you might prefer to do the work manually rather than rely on Copilot.
Needless to say, both everyday Excel users and MVPs didn’t have a favorable first impression. Although many have acknowledged that Copilot has improved—becoming faster and smarter—there’s still considerable reluctance, or perhaps users just don’t see compelling use cases to integrate it into their regular workflow.
If we examine the search trends, it appears that Copilot may have indeed captured some of the interest in Python and Excel. However, there was still considerable disappointment. People outside the Excel community were touting the incredible capabilities of coding without needing to be seasoned, hardcore coders. This promise wasn’t fully realized in Excel. It wasn’t able to create macros from scratch or developing elegant M Power Query pipelines. Unfortunately, the application’s silos within Excel made these advancements challenging, and Copilot often struggled with performing even basic Excel tasks.
Excel found itself in a bit of a dilemma on how to effectively leverage generative AI-driven code, given the significant technical obstacles inherent in its current design. It turns out that Python in Excel came along at just the right moment for this transition.
Excel, Python and Copilot… just right
Then, around a year later in September 2024, the Advanced Analysis features of Copilot were introduced to users. This update enabled the generation and execution of Python code directly within Excel, transforming the workbook into something akin to a Jupyter Notebook environment.
This development overcame many of the technical barriers associated with jumping between VBA, Power Query, and Power Pivot layers by allowing users to concentrate solely on the Excel grid while utilizing a language extensively trained on by Large Language Models (LLMs) like Copilot. It was a match made in heaven.
My theory is that this trio of Python, Copilot, and Excel represents the “just right” or “holy trinity” combination where Excel users can benefit from these tools. Python and Excel alone can be too challenging, and Copilot with Excel might be too simplistic, but the integration of Python, Copilot, and Excel strikes the perfect balance. Ultimately, this combination can make users’ lives easier than with Excel alone, as it eliminates the need to be an expert in Excel formulas.
If you’re keen to dive deeper into using Advanced Analysis or curious about what this indicates for the future of Excel, including its impact on languages like M or DAX, or even the broader implications for dashboards and business intelligence, I encourage you to check out my blog further.
In the meantime, what questions do you have? Does this narrative about the evolution of these tools intrigue you? Or how would you interpret the search trends? Feel free to share your thoughts in the comments.
Leave a Reply