By Hiran de Silva
In today’s Excel landscape, users are bombarded with “modern” features—Power Query, dynamic arrays, Excel tables, lambda functions, and XLOOKUPs—each promoted as essential tools for professional development. But here’s a vital question too few are asking:
What if these features, though powerful in isolation, are completely inadequate for the kind of enterprise-scale problems most businesses actually need solved?
This article challenges the assumptions baked into “modern Excel” and presents a clearer framework for decision-making: Are you solving a local problem inside a box, or are you transforming a collaborative process across the enterprise?
Let’s break it down.
Power Query: Inside the Box or Beyond?
Power Query is often touted as Excel’s most powerful feature—and indeed, it is extremely useful if your task is local and static. You load data from various sources, transform it, and output to a sheet or pivot table. Great.
But here’s the design limitation:
The data model lives inside the workbook. It can’t be updated remotely. Other systems or spreadsheets can’t query it.
In effect, you’ve imported enterprise data into a private silo.
For solo analysis, that’s fine. But if your process is part of an organization-wide workflow—like budgeting, forecasting, or reconciliation—this approach is a dead end. What’s needed instead is a data model that lives outside the spreadsheet, can be updated by many sources, and serves many consumers. Microsoft Office gives us that already—with Access or SQL Server.
Verdict: Power Query is ideal only for solo tasks. For enterprise workflows, use external, sharable data models.
Excel Tables: Convenient, But Not Scalable
Excel tables are useful for input forms, structured formatting, and easier formulas. But when it comes to enterprise needs—especially updating data from multiple sources, running SQL joins, or integrating into automated processes—they fall flat.
You cannot run SQL against an Excel table.
That alone is a dealbreaker if you need your data manipulated remotely, updated concurrently by multiple users, or joined with other tables.
In contrast, Access or SQL-based tables are built for this. They support multi-user inserts, remote updates, SQL joins, and reliable automation.
Verdict: Use Excel tables for local entry forms. Use database tables for anything collaborative or automated.
Dynamic Arrays: Reinventing a Wheel
Dynamic arrays let you spill ranges automatically based on a formula. But many are unaware that Excel has always had dynamic arrays—they just weren’t visualized this way. Developers have long used recordsets (arrays in memory) to load, filter, and reshape data from databases or even from spreadsheets.
The key difference?
Dynamic arrays in modern Excel still rely on data inside the workbook. Recordsets allow data to be pulled from outside, transformed programmatically, and fed into the sheet dynamically.
So again, we have a choice:
- Local data → use dynamic arrays.
- External, changing data → use recordsets and ADO with a central data store.
Verdict: Choose based on whether your data lives inside the box or needs to be retrieved from outside.
XLOOKUP: Great Tool, Wrong Job?
XLOOKUP and its predecessors (VLOOKUP, INDEX/MATCH) serve a clear purpose: lookup a value in a table.
But again, it assumes the table lives in your workbook. In a collaborative or dynamic business process, that table may be updated elsewhere, from multiple sources. You need joins across datasets—which is the domain of relational databases.
Verdict: For isolated lookups, use XLOOKUP. For enterprise joins and integrity, use SQL-based joins in Access or SQL Server.
Lambda Functions: Neat, But Not Centralized
Lambda functions let you write reusable formulas and encapsulate logic inside Excel. Very handy—if your logic is only ever needed by you.
But if you’re trying to build a process that runs across the business, where logic needs to be applied uniformly across many users and systems, then lambda isn’t enough. That logic must live in the database, accessible to all.
That’s what stored procedures are for—server-side encapsulations of logic that can be invoked by any user, from any client.
Verdict: For personal reusability, use lambdas. For enterprise reusability, use stored procedures.
So… How Should You Choose?
You now have two clearly defined paradigms:
- Inside-the-box thinking: Tools designed for the lone Excel user. Great for isolated tasks.
- Spray-gun thinking: Tools and architecture designed to paint the whole wall—to address end-to-end enterprise processes.
Ask yourself:
“Is this task something one person does for themselves, or is this part of a larger workflow across teams, departments, or systems?”
If it’s the latter, then the modern Excel features—despite their popularity—are fundamentally the wrong tools.
They were built for the individual analyst. What the business needs is architecture.
Why Are So Many People Choosing Wrong?
Simple: social media.
Social media platforms reward short, flashy demos—“Look what I can do with XLOOKUP!”—not thoughtful discussions about relational databases, ADO, or centralized architecture.
Even most influencers don’t know these approaches exist, let alone how to use them.
It’s easier to sell “10 cool tricks in Excel” than to teach people how to transform a clunky spreadsheet workflow into an enterprise system. But the rewards for doing the latter are enormous.
The Opportunity
This is the transformation journey my program offers.
We take existing spreadsheet processes—ones created by inside-the-box thinking—and turn them into enterprise-grade, automated, collaborative systems. The kind of transformation your boss and your boss’s boss will immediately recognize and reward.
And here’s the kicker:
You already have the tools.
Microsoft Office. Excel. Access. SQL. VBA. ADO.
The only missing piece is understanding how to use them together—strategically.
Final Thought
Mark Proctor demonstrated a reconciliation method using Power Query. It worked perfectly—for one pair of accounts.
But what if you had 100 accounts to reconcile daily?
Would you rather:
- Manually run 100 Power Query routines?
- Or set up an unattended system that runs them all automatically, flags exceptions, and sends alerts?
That’s the difference between inside-the-box tasks and spray-gun enterprise thinking.
The choice is yours.
So ask yourself: how would you choose?
And, how would your boss choose?
Add comment