Beyond the Combine Button: Realities of Enterprise-Scale Budget Consolidation in Excel
Thank you to everyone who took part in the Power Query consolidation challenge. Your contributions have been incredibly insightful—but as always, the devil is in the details.
Let’s talk about what the boss really wanted.
The Brief, Revisited
What was needed wasn’t just a merged dataset. It was a true consolidated budget report—grouped by department, region, or business unit—built for management use. Think: a report that each group manager can interact with, structured like the original budget sheet they’re familiar with. In other words:
Not just a single-sheet data dump. A decision-ready reporting tool.
The Gaps in the Solutions Submitted
Here’s what emerged:
1. The Reporting Layer Is Missing
Yes, Power Query successfully combined all the uploaded budgets. But what’s missing is the ability for group managers to generate their view of the data. That means:
- A navigable selection interface (e.g., cascading drop-downs)
- A refreshable, formatted report resembling their original input sheet
- A consistent output structure for management to actually use
Combining the data is step one. Consolidation happens in the reporting layer.
2. The Workflow Is Not Fit for Purpose
In most solutions, once managers submit their budgets, the administrator:
- Moves the file into a folder
- Refreshes the Power Query model
- Distributes the updated consolidation workbook
This creates a batch-processing bottleneck. There’s too much back-and-forth. And every time a budget changes, the cycle repeats.
What’s needed is fluidity, not a round-trip shuffle.
3. Data Access Control Is Awkward
Distributing one large consolidation workbook to all managers raises data security concerns. Managers could potentially see data they shouldn’t. The workaround? Manually slicing the data into sub-workbooks and password-protecting them.
That’s not just clunky—it’s also unsustainable at scale.
A Better Way Forward (Yes, Still in Excel)
Here’s the good news: Excel can handle this—gracefully, securely, and scalably. But we need to elevate how we think about it.
✅ Upload via One-Click Submission
Users shouldn’t be emailing files or dragging them into folders. A single “Submit” button—much like a Save button—can push budget data directly into a cloud-hosted table (tblBudgets). No friction, no admin overhead.
✅ Instant Consolidation
Once the budgets are uploaded, the consolidation workbook doesn’t require human intervention. It refreshes in milliseconds, even over the cloud, pulling from the central data table. No middleman required.
✅ Custom Views with Secure Scope
A front-end interface (using cascading drop-downs) lets each manager access only their footprint—drawing from centrally hosted lists, not local static tables. Yes, hundreds of YouTube tutorials exist for drop-downs, but most of them source data from a single sheet. That’s not enterprise-ready. We need centralised, scalable references.
What Was Missed—and Why It Matters
One of the most surprising and revealing outcomes of this challenge was a widespread confusion between combining data and consolidating data.
Combining is mechanical. It brings everything together.
Consolidating is strategic. It brings meaning to the chaos.
The idea that once you’ve appended 400 budgets into one table your job is done—that mindset shows the limit of imagination around enterprise budgeting workflows.
Final Thought
This challenge wasn’t just about clever tricks in Power Query. It was a benchmark to identify who could think like an enterprise architect, not just a spreadsheet technician.
Excel can do far more than most people realise—but only if we stop treating it like a fancy calculator, and start treating it like the enterprise platform it is.
Let’s raise the standard.
—
Written as a response to the Excel Consolidation Challenge review, May 2025.
Add comment