By Hiran de Silva
One of the simplest questions we can ask about any Excel solution is also one of the most revealing.
Can you explain it to the client?
Not to another Excel expert.
Not to someone who already knows Power Query, Dynamic Arrays, LAMBDA, Office Scripts or VBA.
Can you explain it to the person who is relying on the result?
Because if you can’t, then perhaps we need to ask a much bigger question about what spreadsheet competence really means.
The Lionel Messi Test
Imagine Lionel Messi has shared restaurant bills with five friends.
Your spreadsheet calculates exactly how much he owes.
Messi looks at the number and says:
“I don’t believe it. Show me why.”
Suppose your answer is to open the Name Manager and reveal a magnificent LAMBDA function stretching across several lines.
Technically, you’ve shown him the logic.
But have you actually explained anything?
Unless Lionel Messi secretly moonlights as Alan Turing, assisted by Isaac Newton and Albert Einstein, the formula is meaningless.
Now imagine a different explanation.
You show him each restaurant bill.
You show who paid.
You show which meals he shared.
You show the payment he already made.
Finally you show the balance remaining.
At that point he is far more likely to say:
“Ah… now I understand.”
The spreadsheet hasn’t merely produced an answer.
It has earned trust.
There Is a Missing Dimension in Spreadsheet Competence
This takes me back to the Spreadsheet Competency Framework.
One question keeps bothering me.
Where is the management perspective?
Competence is usually described in terms of technical ability.
Can you write formulas?
Can you build Power Query models?
Can you create Dynamic Arrays?
Can you write LAMBDA functions?
All useful skills.
But management asks a completely different question.
Can I understand what you’ve built?
That question barely appears in most discussions about spreadsheet competence.
Yet it is probably the first question every senior manager asks.
The Dangerous Attitude
Increasingly I hear the same response from parts of the Excel community.
Managers don’t need to understand.
Managers don’t care.
Managers don’t need to know how it works.
That attitude should concern every Excel professional.
Management is not interested in Excel for Excel’s sake.
Management is interested in running the business.
Excel exists to serve the business.
Not the other way around.
When an Excel solution becomes impossible for management to understand, it becomes a black box.
What Is a Black Box?
A black box accepts inputs.
It produces outputs.
But nobody can see what happens inside.
Perhaps it is correct.
Perhaps it isn’t.
Perhaps it will fail tomorrow.
Nobody knows.
That uncertainty creates stress.
When people cannot understand a process, they cannot verify it, improve it or confidently rely upon it.
Eventually the inevitable happens.
Something changes.
A number doesn’t look right.
Nobody knows why.
Nobody knows where to begin.
Confidence disappears.
The Real Question
Whenever someone demonstrates a sophisticated Excel solution, I now ask exactly the same question.
How would you explain this to your client?
Not another Excel expert.
The client.
If your explanation begins with M code…
Or nested LAMBDAs…
Or Dynamic Array recursion…
…then perhaps you haven’t really explained it at all.
Transparency Matters
Sometimes a black box is unavoidable.
My own TravelMates demonstration is one example.
The matching algorithm is genuinely complex.
Most people don’t need to understand every line of code.
But they do need confidence that it is correct.
That confidence comes from something different.
Verification.
I built a completely separate verification process that mirrors what a human would do manually with a highlighter pen.
It follows first principles.
It is transparent.
It allows anyone to verify that the black box is producing the correct result.
Notice something important.
The verification process is independent of the algorithm.
It doesn’t explain the code.
It proves the outcome.
That distinction is vital.
Why This Matters
Modern Excel discussions often celebrate complexity.
More functions.
More features.
More clever techniques.
But very little attention is given to explaining those techniques to the people who actually own the business process.
That creates unnecessary uncertainty.
And uncertainty destroys confidence.
The Database Example
Christopher T. Fennell has famously demonstrated ways to make Excel work like a database using Power Query.
Mynda Treacy has demonstrated another approach.
These are interesting ideas.
But imagine the client asking a perfectly reasonable question.
“We currently store our reconciliation results in SQL Server so that dozens of domain experts around the world can review them every morning.”
“Can you show us how your Excel database replaces that?”
Not in theory.
Not with marketing language.
Actually demonstrate it.
Scale it.
Explain it.
Show how management would understand it.
That is a completely different challenge.
Understanding Beats Button Clicking
Take account reconciliation.
There are many excellent Power Query tutorials online.
Some require forty or fifty individual transformation steps.
Every click has a purpose.
Every click represents a decision.
Unless you understand why those decisions were made, you are simply copying button presses.
Now compare that with another approach.
Take List A.
Take List B.
Reverse the signs in one list.
Append the two together.
Summarise by transaction reference.
Remove the zeros.
The remaining values are your reconciling items.
Management can understand that process.
It mirrors traditional accounting logic.
It can be explained on a whiteboard in half an hour.
Better still, once management understands the principle, they begin recognising where the same principle can solve entirely different business problems.
That is education.
Not merely training.
The Cost of Black Boxes
I’ve watched this pattern repeat itself for years.
Someone arrives claiming advanced Excel skills.
They build an impressive solution.
It works beautifully.
Then they leave.
Months later something changes.
Nobody understands the model.
Nobody can safely modify it.
Eventually management abandons it altogether.
This is remarkably similar to what happened with Microsoft Access decades ago.
The technology wasn’t the problem.
The lack of transparency was.
The Real Measure of Competence
Perhaps we’ve been measuring spreadsheet competence from the wrong direction.
Maybe competence isn’t simply about producing powerful solutions.
Maybe competence includes something much more important.
Can you explain your solution?
Can your client understand it?
Can your manager trust it?
Can someone verify it independently?
Can they build upon the principles you’ve taught them?
If the answer is yes, then you’ve created far more than a spreadsheet.
You’ve created understanding.
And understanding is what ultimately creates confidence.
Because a spreadsheet that nobody can explain may be technically brilliant…
…but from the client’s perspective, it is still just a black box.
Add comment