A Brief History of Excel Connecting to Data Sources and Why It Matters Today

At Positive8, we often talk about unlocking the power of data. But to understand where we’re going, it helps to know where we’ve come from, especially when it comes to how Microsoft Excel connects with data. 

Excel has always been a powerhouse for analysis, but in the early days, it was primarily a static tool. You’d key in numbers manually or copy/paste data from somewhere else. Then came a leap forward that fundamentally changed how we work with data in Excel: the ability to connect directly to external data sources. 

The Leap: Separating Data Storage from Reporting and Visualisation 

This shift marked a critical evolution in business intelligence: the separation of data storage from reporting and visualisation. 

Before, Excel files were the data store. Now, Excel could act as a live window into external systems by pulling in data from SQL databases, OLAP cubes, web APIs, SharePoint, and more. 

This paved the way for two revolutionary tools that reshaped how analysts and accountants work. 

MS Query: A Bridge for Professionals and Amateurs Alike

In the late '90s and early 2000s, MS Query introduced the concept of connecting Excel to databases.  

Why it mattered: 

• Familiarity: It used the same visual query tool as MS Access, making it instantly accessible to professional developers and approachable for analysts and accountants. 

• No code needed: Users could build queries with a drag-and-drop interface. Joins, filters, and conditions were all done visually. 

• Learning SQL: A built-in SQL button let curious users see and edit the SQL behind the scenes – offering a natural, low-pressure way to start learning SQL. 

This opened the door for amateur coders, people who didn't come from a programming background but were highly skilled in numbers to become more technical and more independent. 

Win: MS Query made powerful data access tools available to non-developers without dumbing anything down, and gave them a path to grow their skills. 

Power Query: The Modern Standard

Fast forward to the 2010s and along came Power Query, a dramatic evolution.

Now built directly into both Excel and Power BI, Power Query became the central engine for importing, cleaning, and transforming data. 

Why it matters: 

• One tool, two platforms: Power Query uses the same visual query tool as Power BI. That means once you learn it in Excel, you can apply the same logic and skills in Power BI, and vice versa. 

• Best tool for the job: 

• Use Excel for reports and analysis, when your work is mainly numerical, tabular, or model-driven. 

• Use Power BI for dashboards and visualisations, when your work needs to be interactive, visual, and shared. 

This separation allows for focus and flexibility. Analysts can use the tool that fits their output without changing how they get or shape the data. 

Win: Analysts and accountants now work with the best tool for the job, using the same back end for consistency, speed, and collaboration. 

Why This History Matters 

Today, we live in a world of connected data. Whether it’s cloud systems, CRM platforms, or financial databases, the ability to link, shape, and report on data is a critical business skill. 

The evolution from MS Query to Power Query shows how Microsoft continues to make professional-grade tools more accessible, and how business users are increasingly empowered to do more, faster. 

At Positive8, we think this story matters because: 

• Tools like Power Query allow non-technical users to do technical work, safely and repeatably. 

• This evolution supports the rise of low-code data teams, where analysts, not just IT, are building value. 

• And best of all, Excel and Power BI now form a unified ecosystem with one skillset and two powerful tools. 

Coming Soon: In our next post, we’ll explore how Power Query fits into the modern data stack, and how to design Excel and Power BI workflows that deliver flexible, scalable, and secure business intelligence.