In this follow-up piece, we dive deeper into the power of Excel in tackling Enterprise-Level challenges. We’ll use the example of Excel’s new dynamic array functions, specifically the *FILTER* function, and how they can evolve into enterprise-level solutions.
Let’s start with a quick demonstration of this popular product feature: dynamic array formulas. In recent years, there has been a flurry of excitement around Dynamic Arrays, and one of the most demonstrated among them is the *FILTER* function. This new formula allows us to create dynamic results that previously required manual work or complex techniques.
A Simple Example: Filtering Data with Excel’s FILTER Function.
Imagine you have a table of famous rock stars. You want to filter the list to display only those who are members of The Beatles. In the past, Excel formulas could only return a single value at a time, but now, with dynamic arrays, the formula will automatically return all the matching values. For instance, after entering a *FILTER* formula, you will get a neat, filtered list of Beatles members. This functionality is powerful for individual tasks, but let’s think bigger.
Scaling Excel’s Capabilities to the Enterprise.
Now, let’s take that same data to the next level by placing it in a relational database rather than an individual spreadsheet. The beauty of this is that the data no longer resides within your local Excel file but in a central database, making it accessible to anyone, and any spreadsheet, that needs it.
You can still filter the data just like before, but now it has greater scale and reach, as you’ll see. Imagine instead of typing a formula, the user clicks a button or select a value from a drop-down list that contains a refreshed set of options. For example, select “The Beatles,” click *Get*, and you see the band’s members. No formulas, no manual filtering–just real-time data pulled from a central source.
This shift means that whether you’re working with a small team or a company of 100,000 employees, everyone is pulling from the same data on perhaps even 100,000 spreadsheets. Imagine the impact this could have on collaboration. Gone are the days of sending around copies of a spreadsheet and hoping everyone has the same version. Now, when someone updates the central database, the change reflects across all users instantly.
Centralized Updates: A Game Changer.
Let’s say an administrator updates the database to include “Billy Preston” as part of The Beatles. He will manage the table with this simple spreadsheet. With a single click, every user across the company, and every spreadsheet that relies on this data, that pulls data from the central source will now see the updated list when they refresh. With no manual work in between. This centralized, scalable approach eliminates the chaos that comes with version control and manual updates. A horror popularly known as Many Versions of the Truth and Excel Hell!
If the administrator adds a new artist like Elvis Presley, the same process applies. When they refresh the dropdown list by double-clicking the cell, every user in the organization, whether they’re on the same network or halfway around the world, will see Elvis appear in their spreadsheet without needing to change anything on their end.
In case of mistakes or updates – say swapping Ringo Starr for Pete Best – the same process applies. It’s a real-time solution that’s accessible to thousands of users instantly.
Beyond Spreadsheets: Transforming into a Hub-and-Spoke Architecture.
What makes this approach so revolutionary? This isn’t just about making Excel easier to use. This is about taking the most popular spreadsheet tool in the world and elevating it to handle enterprise-level processes. When your data is centralized, shared, and updated in real-time, it turns Excel into a lightweight yet powerful system that can rival traditional enterprise solutions that cost several millions.
This *Hub-and-Spoke* architecture means that while the core data is centralized, the “spokes” – the individual spreadsheets – can access and manipulate that data freely. The implications are enormous. With just a few clicks, you can set up a system that can scale from a handful of users to a global workforce. Not only that, but with cloud storage and internet access, this solution can be accessed from anywhere–yes, even the International Space Station.
Security and Permissions: Keeping Control.
Of course, with great power comes the need for control. Not everyone should have the ability to update the central database, or indeed access this data. Thankfully, this methodology can easily incorporate access rights and permissions, ensuring only authorized personnel can make changes.
Setting up permissions can be as simple as updating a spreadsheet and clicking “Put”. Implemented with an Access database on a shared drive or moving to a cloud-based system for global teams. Either way, this system can support the scale and security needed for enterprise processes. Note that the backend database is inert. It is passive. It’s only role in this basic form is to hold the table, or tables; instead of tables in the workbook.
Why Bother with This Transformation?
For those of you still thinking, “Why should I learn this?” the answer is simple. Money. As I’ve mentioned before, I have worked with several clients across different industries, each of whom has seen enormous value from this transformation. In fact, every time I demonstrated this solution, they tripled my pay and invited me to stay indefinitely — not for what I did, but for what they saw it could achieve in the coming months, transforming existing messy spreadsheet processes that needed to work smoothly company-wide.
These organizations realized that this transformation would solve countless problems they were struggling with across their business processes. In their eyes, the simplicity, scalability, and flexibility of this solution far outweighed the cost of traditional IT solutions, ERP systems, or external cloud-based alternatives.
Conclusion: From Single User to Enterprise Powerhouse.
What I’ve demonstrated here is a simple example of what’s possible when you transform Excel from a single-user tool into an enterprise-wide solution. It’s an evolution that’s simple to set up, easy to modify, and infinitely scalable. But it needs a different mindset , beyond traditional Excel-thinking.
If you’re ready to take your Excel skills to the next level, the journey starts with a few simple steps–learning how to create a database table, understanding the *Get* and *Put* functionality already built into Excel, and practicing system design and innovation. The rewards for mastering this transformation are significant, both in terms of professional growth and financial gain.
So, whether you’re working in a small department or looking to revolutionize processes across a global organization, the opportunities are endless. Less hassle, more value–this is the future of Excel, and the rewards are waiting for you.
—
Stay tuned for more insights on how to set up your own *Beyond Power Query* methodologies and how you can leverage it for success in your career.
You’ve been listening to a podcast by Hiran de Silva. Read by Bill.
Add comment