Andrew Moss recently posted a thought-provoking reflection on LinkedIn, celebrating the 39th anniversary of Excel by sharing a screenshot of its original packaging. Interestingly, the packaging prominently referenced the term “database,” which triggered a long-standing debate in the Excel community: should Excel be used as a database?
This discussion has evolved over time, but many modern Excel users and even trainers fail to grasp the historical context and the deeper capabilities Excel has had since its inception. To understand why Excel included database functionality in its early days, we need to take a step back to the 1980s and early 1990s, a time when DOS (Disk Operating System) was the dominant platform. At that time, DOS did not allow more than one application to run simultaneously. As a result, the expectation was that productivity software needed to be an all-in-one package.
Excel’s Historical Role in Database Functionality
In those early days, many high-end spreadsheet packages like Lotus 1-2-3 and Excel included basic database functionality. This was not relational database functionality as we know it today but rather a way to store and browse lists using a form-like navigation wizard. It was rudimentary but necessary because separate database programs could not run simultaneously with spreadsheets. Lotus 1-2-3, for example, integrated spreadsheet, database, and business graphics in one package, hence the “1-2-3” name.
For more advanced database needs, professionals would turn to programs like dBASE II, dBASE III, and later, Paradox from Borland. These were specialized database management systems, separate from the spreadsheet ecosystem, and people who needed professional-strength database solutions knew exactly where to go. There was no confusion about what spreadsheets like Excel or Lotus could do versus what databases like dBASE were built for.
The Emergence of Synergy in the 1990s
By the early 1990s, the introduction of Microsoft Access as part of the Office suite made it clear to professionals that Excel could seamlessly connect to a database for more complex operations. Access was designed to work hand-in-hand with Excel, offering a desktop-strength database solution for users who needed it. At the same time, Microsoft heavily promoted ActiveX Data Objects (ADO), a powerful tool for connecting Excel with databases like Oracle and SQL Server, in addition to MS Access.
During this time, there was no debate or confusion among professionals about what Excel could and should do in terms of database functionality. Tools like ADO were widely recognized as the “glue” that held Microsoft Office applications together, allowing them to communicate seamlessly with one another and with external systems. Bill Gates even referenced this in his book Business @ the Speed of Thought, introducing the concept of a “Digital Nervous System” where data triggered actions across different parts of a business in real-time, facilitated by tools like ADO.
The Current Debate and Power Query’s Role
Fast forward to today, and the Excel landscape has changed. The majority of Excel users now rely on what I call “popular Excel” techniques, using Excel as a makeshift database for exporting, storing, and sharing data. Many of these users are unaware of Excel’s more advanced capabilities, including its built-in relational database connectivity.
When Power Query was introduced to Excel about a decade ago, it was hailed as a groundbreaking tool. And indeed, for certain data operations—particularly ETL (Extract, Transform, Load) processes—Power Query provided a user-friendly way to pull data into Excel, offering something akin to OLAP (Online Analytical Processing) functionality. Power Query’s rise was inevitable, with Microsoft and social media influencers promoting it as a revolutionary feature.
However, it’s curious that in all this buzz around Power Query, Excel’s seamless integration with relational databases, like Microsoft Access and SQL Server, is rarely discussed. This relational database connectivity is arguably far more powerful and relevant for many business processes, yet it receives little to no attention in modern Excel training or discussions.
The Detour to Hastings: Why Are We Using Power Query When We Don’t Need To?
This brings me to an important observation—one that might seem controversial to many Excel users today. Using Power Query in some business cases is akin to making an unnecessary detour on a road trip.
Imagine you’re driving from London to Brighton for a meeting, and you typically pick up a colleague in Hastings along the way. This detour makes sense when your colleague is joining you because it provides an opportunity to discuss the meeting on the way. However, if your colleague is on holiday and not attending, there’s no rational reason to take the detour—you’d be wasting time. Yet many Excel users today continue to “drive to Hastings” (i.e., use Power Query) when their needs could be met more efficiently by Excel’s built-in database connectivity, skipping the detour entirely.
What’s surprising is that when I explain why this detour is unnecessary, I often receive abuse and hostility. Normally, one would expect people to be delighted to learn that a complication can be avoided. So why is this?
Why is Excel’s Built-in Database Connectivity Overlooked?
One explanation is that many social media influencers and Excel trainers are simply unaware of Excel’s more advanced capabilities. They promote Power Query because it’s the “new shiny thing,” and it generates views and engagement on platforms like YouTube. But as I pointed out in my conversation with many social media debaters, this detour has become ingrained in the Excel community, despite the existence of simpler and more powerful solutions that have been available for decades.
The irony here is striking. Satya Nadella, now CEO of Microsoft, was the young engineer who demonstrated Excel’s database connectivity at Microsoft’s quarterly developer event DevCast as far back as 1993. The capability has been baked into Excel since then, yet today, it’s almost entirely forgotten or ignored by the popular Excel crowd.
The Consequences of This Ignorance
As more and more users rely on Power Query, we see a growing gap between what Excel is capable of and what is commonly taught and understood. Excel’s built-in relational database connectivity is a powerful tool that allows for real-time, transactional data processing, known as OLTP (Online Transaction Processing), a far cry from Power Query’s one-way data flow.
The reluctance to embrace this capability, even after being made aware of it, raises important questions about the credibility of those who evangelize Power Query as a “best practice.” Many of these influencers have built their reputations on promoting a tool that, while useful in certain contexts, is often not the most efficient or necessary solution.
Conclusion: Question the Detour
In closing, it’s time we ask ourselves why we’re making the unnecessary detour to Hastings in our Excel workflows. When real-time data connectivity is required, Excel’s built-in tools offer far more utility and efficiency than Power Query ever could. My goal in the Mission Impossible series is to explore these nuances and encourage a deeper understanding of Excel’s true capabilities.
Let’s challenge the current narrative and start asking the right questions. Why are we using Power Query when Excel’s relational database connectivity offers a more direct route? It’s time to rethink the detours we’ve been taking and use Excel to its fullest potential.
A podcast by Hiran de Silva. Narrated by Charlie.
Add comment