By Hiran de Silva
One of the most successful sales messages in software history is the claim that Excel cannot consolidate data.
Not that it consolidates badly.
Not that there are better alternatives.
But that it cannot do it.
That message has been repeated so often, by so many vendors, for so many years, that it has become accepted as fact. It appears in ERP sales pitches. It appears in FP&A software marketing. It appears in cloud planning demonstrations. It even appears in white papers whose entire purpose is to persuade organisations to replace Excel.
Yet there is one problem.
It isn’t true.
It has never been true.
And in this article I want to explain why.
The Billion-Dollar Narrative
If you watch presentations from the Excel replacement industry, there is a recurring theme.
Sooner or later, somebody says:
Excel cannot consolidate.
Or perhaps:
Spreadsheets become fragmented islands of data.
Or:
Bottom-up planning cannot be achieved with spreadsheets.
Different wording.
Same message.
The implication is always that spreadsheets are isolated documents and that consolidating information across hundreds of users requires specialised planning systems.
This narrative has been enormously successful.
Entire industries have been built upon it.
But ‘marketing message’ success does not make a statement true. Tell the Flat Earth people that the Earth is flat and they will believe you 100%.
Colin Wall and the Bottom-Up Budgeting Debate
Back in 2023 I found myself in a LinkedIn discussion with Colin Wall. An Anaplan salesman.
The claim being made was that spreadsheets could not support bottom-up budgeting.
The argument was essentially this:
Only top-down planing is possible with spreadsheets. Top-down planning is when management simply allocates targets down the organisation.
Bottom-up planning is different.
Managers throughout the organisation must submit plans which then need to be consolidated upwards.
Therefore spreadsheets become impractical, said Colin Wall.
I agreed with one point.
Top-down budgeting often becomes little more than a negotiation exercise.
Numbers are handed down from above.
Managers may have little ownership of them.
Responsibility accounting becomes questionable because the numbers were not created by the people expected to deliver them.
But I strongly disagreed with the second point.
Bottom-up budgeting is perfectly feasible in Excel.
The real issue is not Excel.
The issue is architecture.
Bottom-up budgeting is perfectly feasible in Excel, for the same reason as Anaplan – a cloud-based planning product.
The Demonstration That Started It All
After that discussion I built a demonstration model.
Not a small one.
A global budgeting model.
The demonstration represented:
- 400 shops
- 90 cities
- 50 countries
- 4 world regions
Every shop had its own budget template.
Every level required consolidation.
Every level required reporting.
And every level worked.
This demonstration later caught the attention of Christopher Argent and led to my invitation onto a Gen CFO budgeting panel discussion.
The Planful Discussion
During that panel discussion another interesting moment occurred.
One of the panelists from the planning software industry, Justin Merritt, casually remarked that Excel need not be able to consolidate, because other tools exist. But do they?
I remember thinking:
That’s a curious statement.
The very reason I was sitting on the panel was because I had already demonstrated that Excel could consolidate, and the enormous benefit of a client-server architecture with Excel.
The model that earned me the invitation was itself proof.
But panel discussions move quickly.
You think on your feet.
You answer as best you can.
Looking back, I felt the explanation deserved a more complete response.
That is one reason this article exists.
The Mental Model Problem
The misunderstanding comes from assuming there is only one way Excel can consolidate.
Most people imagine something like this:
Workbook A links to Workbook B.
Workbook B links to Workbook C.
Workbook C links to Workbook D.
Then regional workbooks link to country workbooks.
Country workbooks link to group workbooks.
And eventually you end up with a giant pyramid of spreadsheets.
A house of cards.
One broken link.
One overwritten formula.
One renamed file.
One accidental deletion.
And the entire structure becomes unreliable.
If that is your model of Excel consolidation then I completely agree.
It is horrible.
But that is not an Excel problem.
That is an architecture problem.
Modern Excel Doesn’t Solve It Either
Some argue that Power Query solves this issue.
I disagree.
Power Query improves many things.
But for budgeting and management reporting it introduces a different limitation.
The data model now sits inside a workbook.
Refreshes become batch processes.
Reports often become Pivot Tables.
That may be acceptable for analysts.
It is rarely acceptable for executives.
Senior managers generally do not want to navigate Pivot Tables.
They want familiar management reports.
Income statements.
Budget reports.
Variance reports.
Operational reports.
Exactly as they have always seen them.
Power Query addresses symptoms.
It does not address the architectural issue.
The Solution Already Exists
Now comes the interesting part.
The solution has existed inside Excel for decades.
No additional software.
No cloud platform.
No ERP system.
No planning tool.
No Power Query required.
Simply this:
Separate the data from the spreadsheet.
That’s it.
Creating the External Data Model
Imagine a standard budgeting template.
Nothing special.
Just a worksheet.
Now imagine another workbook containing a button.
The user enters a name:
Budgets
They click a button.
Excel creates a relational database.
Immediately.
A second button creates a table structure inside that database.
Immediately.
The entire one-off setup process takes seconds.
At this point we have:
- A central database
- A central table
- No external links
- No consolidation formulas
- No spreadsheet pyramid
GET and PUT
Each budget template contains two buttons:
PUT
Uploads the budget into the central database.
GET
Retrieves budget information from the central database.
Suppose each budget contains 28 rows.
A manager completes their budget.
They click PUT.
Twenty-eight rows are uploaded.
If they change the budget later, they click PUT again.
The database updates.
Simple.
Now multiply that by 400 shops.
All budget data now resides centrally.
In one location.
In real time.
The Consolidation Vanishes
This is the key insight.
Once all data resides in one central table, consolidation largely disappears as a technical problem.
Because the data is already consolidated.
The database contains everything.
A report for Shop 40001?
Retrieve Shop 40001.
A report for Paris?
Retrieve Paris.
A report for France?
Retrieve France.
A report for Europe?
Retrieve Europe.
A report for the entire organisation?
Retrieve everything.
The report layout never changes.
Only the query changes.
And the database performs the aggregation automatically.
What was previously described as a consolidation problem becomes a retrieval problem.
And retrieval is trivial.
Why Planning Systems Work
This is the part that often surprises people.
The reason planning systems work is exactly the same reason this works.
Client-server architecture.
Hub-and-spoke architecture.
Centralised data.
The planning software industry did not invent this concept.
Relational databases have existed for decades.
Excel has been capable of participating in this architecture for decades.
The difference is simply that most Excel users have never been shown how.
The Domino Effect
Once the data is centralised, something interesting happens.
Many of the problems highlighted in planning software sales presentations disappear automatically.
Version control.
Audit trail.
Archiving.
Security.
Workflow.
Status tracking.
Approval processes.
Drill-down to detail.
All become significantly easier because there is now a single source of truth.
Not because Excel was replaced.
But because the architecture is now relevant in the context. It wasn’t before.
The Real Question
So when someone says:
Excel cannot consolidate.
My response is now very simple.
Do you mean Excel cannot consolidate?
Or do you mean you were never shown how?
Those are very different statements.
Because if the answer is:
We didn’t know Excel could do that.
Then an uncomfortable question follows.
How much of the Excel replacement industry has been built upon that lack of knowledge?
The Bigger Issue
This is not merely about software.
It is about perception.
When organisations repeatedly hear that Excel cannot scale, cannot consolidate, cannot support enterprise processes, something else happens.
The people who work with Excel are diminished too.
Their skills are diminished.
Their expertise is diminished.
Their contribution is diminished.
The message becomes:
Excel is not a serious enterprise platform.
The evidence says otherwise.
The demonstration says otherwise.
The results say otherwise.
Final Thought
The purpose of this demonstration is not to attack planning software.
Many of those products are excellent.
The purpose is simply to challenge a claim that has been repeated for years without scrutiny.
Excel can consolidate.
It always could.
The moment data is separated from spreadsheets and placed into a central relational model, the consolidation problem largely disappears.
And when that happens, a great many assumptions about Excel suddenly need to be re-examined.
That is the real lesson of External Data Model 101.
Add comment