Introduction: The Mess We Have Learned to Accept
By Hiran de Silva
Let us begin this series by agreeing with almost everybody.
Spreadsheets are broken.
Or, more precisely, the way spreadsheets are routinely used across organisations creates a problem that is very real, very familiar and very well documented.
So before I introduce the Digital Librarian, I want to begin with the problem.
And for that, let us meet Tim.
Meet Tim
Tim is extremely good at Excel.
In fact, Tim is probably as good as we could reasonably expect a modern Excel user to be.
He follows the leading Excel influencers. He watches the most popular YouTube channels. He keeps himself up to date with the latest developments in Excel.
Power Query.
Dynamic arrays.
LET and LAMBDA.
Perhaps Python.
Automation wherever automation is possible.
Tim has left no stone unturned.
He has taken his daily workload and made it about as efficient as he knows how to make it.
Every morning, Tim receives information from various places. Some arrives in spreadsheets. Some is downloaded from the ERP system. Some may come from other colleagues.
Tim processes it.
He transforms it.
He analyses it.
He produces his result.
And when he has finished, he sends his splendid piece of work to Tom.
Naturally, he copies his boss.
Tom’s boss wants to see it too.
Ted has asked to be included.
And while Tim is at it, the rest of the team might as well have a copy.
Why not?
Tim has done some excellent work. People need the result.
And so Tim emails the spreadsheet.
Now Meet Tom
Tom is pretty good at Excel too.
In fact, Tim and Tom regularly exchange ideas.
They follow many of the same influencers. They share interesting techniques with one another. When Tim discovers a clever new Power Query technique, Tom hears about it. When Tom discovers an interesting use of dynamic arrays, Tim hears about that.
Tom performs a different business function, but his working day has a familiar shape.
He receives data.
He downloads data from the ERP system.
He receives Tim’s spreadsheet.
He receives spreadsheets from other people.
He processes them using all the sophisticated tools available to him.
And then Tom produces his own spreadsheet.
Which he sends to his boss.
And Tim.
And Ted.
And everybody else who needs to know.
Around them are many more people doing much the same thing.
Each is competent.
Each is efficient.
Each is automating as much as possible.
Each may be using Excel at an extremely sophisticated level.
And yet, when we stand back from their individual desks and look at the organisation as a whole, something rather different appears.
We see spreadsheets flying everywhere.
The Point-to-Point Organisation
The output from one person’s process becomes the input to somebody else’s process.
The mechanism by which that information travels is frequently the workbook itself.
Tim sends Tom a workbook.
Tom sends somebody else another workbook.
That person modifies it and sends another version somewhere else.
Perhaps the files are emailed.
Perhaps they are placed on a shared drive.
Perhaps more modern collaboration technology is used.
But the underlying idea remains much the same:
the document is carrying the data from one process to another.
This creates what we might call a point-to-point processing architecture.
Information travels from point to point, carried around the organisation inside documents.
And very quickly, our beautifully efficient individual Excel users collectively create something that looks remarkably like spaghetti.
One workbook becomes twelve copies.
Those twelve copies acquire different lives.
They get names such as:
Budget_Final.xlsx
Budget_Final_v2.xlsx
Budget_Final_v2_UPDATED.xlsx
Budget_Final_v2_UPDATED_FINAL.xlsx
And eventually, inevitably:
Budget_Final_v2_UPDATED_FINAL_USE_THIS_ONE.xlsx
We laugh because we recognise it.
But it isn’t particularly funny when you are running a business.
And Then the Real Work Begins
Once information has been distributed across all these spreadsheets, somebody eventually has to bring it together again.
That means consolidation.
Perhaps the business has shops, cities, countries and regions.
Somebody must take all those separate pieces of information and aggregate them through the organisational hierarchy.
Fortunately, Excel has excellent tools for doing this.
Power Query can be particularly useful.
But consolidation is only the beginning.
Because once multiple versions of information exist in multiple places, numbers can diverge.
One spreadsheet says one thing.
Another says something slightly different.
Now somebody must determine why.
That is reconciliation.
Again, Excel comes to the rescue.
There are countless clever techniques for reconciliation. Modern Excel makes it possible to perform extraordinarily sophisticated comparisons and analyses.
And once the differences have been discovered, somebody must investigate them.
Why did they happen?
Which number is correct?
Which workbook contains the latest information?
What action needs to be taken?
Can the process be automated?
Can Power Query help?
Could dynamic arrays make the analysis easier?
Could LET or LAMBDA simplify the formulas?
Could Python do something clever?
And so an enormous ecosystem of knowledge has developed to help Excel users perform this work faster and more efficiently.
Which brings us to something rather peculiar.
We Have Become Brilliant at Managing the Mess
Look across social media.
Look across YouTube.
Look across Excel training.
There is an extraordinary amount of excellent material showing people how to consolidate spreadsheets.
How to reconcile spreadsheets.
How to combine files from folders.
How to compare different versions.
How to clean data.
How to transform data.
How to automate repetitive spreadsheet work.
And much of this content is genuinely excellent.
The problem is not that these techniques don’t work.
They do.
The problem is that almost everybody appears to have accepted the underlying workload as inevitable.
We have become extraordinarily sophisticated at dealing with the consequences of the architecture.
But we rarely question the architecture itself.
Why are all these copies necessary?
Why does Tim need to send his data to Tom inside a workbook?
Why does Tom need his own copy?
Why must twelve people possess twelve different physical versions of substantially the same information?
Why do we need to consolidate information that we previously distributed?
Why do we need to reconcile numbers that we previously allowed to diverge?
Those questions are much less fashionable.
Enter the Excel Replacement Industry
And this is where things become even more interesting.
Because while the Excel community develops increasingly sophisticated techniques for coping with this environment, another industry stands outside pointing at the resulting chaos.
The Excel replacement industry.
ERP vendors.
Planning systems.
FP&A platforms.
Software companies selling alternatives to spreadsheets.
They take one look at our point-to-point spaghetti and say:
Look at this mess.
Versions everywhere.
Files everywhere.
Reconciliation.
Consolidation.
Manual intervention.
No single version of the truth.
They produce white papers describing Spreadsheet Hell.
Workday Adaptive Planning famously promoted the idea through its Nine Circles of Excel Hell.
Other vendors have variations on the same message.
And the CFO community hears it repeatedly.
Stop relying on spreadsheets.
Move away from Excel.
Buy our platform.
Centralise your information.
Create one version of the truth.
And here is the uncomfortable part:
the criticism is not entirely wrong.
The mess is real.
The duplicated files are real.
The reconciliation is real.
The consolidation workload is real.
The version-control problem is real.
We can demonstrate all of it.
The CFO’s Dilemma
So the CFO finds themselves in a rather awkward position.
On one side is the spreadsheet environment the organisation already knows, understands and depends upon.
People like Tim and Tom can work extraordinarily quickly in Excel.
Excel is flexible.
It is ubiquitous.
It is powerful.
It allows knowledgeable people to solve business problems remarkably quickly.
The CFO doesn’t necessarily want to lose that.
But on the other side is the undeniable organisational mess created when hundreds of individual spreadsheet processes exchange information through hundreds or thousands of individual files.
The CFO doesn’t want that either.
So we appear to face a choice.
Keep Excel…
…and accept Spreadsheet Hell.
Or eliminate Spreadsheet Hell…
…and replace Excel.
Much of the technology industry has spent decades encouraging us to believe that these are the available choices.
But what if there is a third choice?
What If Excel Isn’t the Problem?
Suppose we keep Tim.
Keep Tom.
Keep Excel.
Keep Power Query.
Keep dynamic arrays.
Keep LET and LAMBDA.
Keep all the extraordinary flexibility that makes Excel valuable.
But remove the point-to-point architecture.
What happens if Tim no longer needs to send his data to Tom?
What happens if Tom no longer needs Tim’s workbook?
What happens if everybody can work independently while sharing the same central information?
What happens if the data stops travelling around the organisation inside documents?
Something rather remarkable happens.
A great deal of the work we have spent years learning how to perform…
disappears.
Not becomes faster.
Not becomes more automated.
Not becomes easier.
Disappears.
The consolidation problem changes.
The reconciliation problem changes.
The version-control problem changes.
The proliferation of files changes.
The endless movement of data from spreadsheet to spreadsheet changes.
And we do not have to remove Excel to achieve it.
We simply introduce one new character into our story.
The Digital Librarian
Imagine a librarian sitting quietly in the middle of our organisation.
Tim no longer sends his information to Tom.
He gives it to the librarian.
Tom doesn’t ask Tim for his spreadsheet.
He asks the librarian for the information he needs.
Ted does the same.
Everybody does the same.
The librarian knows where the information belongs.
The librarian knows which information is current.
The librarian can receive information.
The librarian can retrieve information.
The librarian can serve hundreds of different Excel workbooks without requiring those workbooks to exchange data directly with one another.
Instead of hundreds of point-to-point relationships, we create something much simpler.
Excel → Librarian → Excel.
The spreadsheets remain.
The people remain.
The flexibility remains.
But the architecture changes.
And that simple architectural change is the subject of this series.
Excel and the Digital Librarian
In the next episode, I will introduce the Digital Librarian properly.
We will look at what it is.
What it does.
And, importantly, what it is not.
Then we will build the simplest possible example.
Nothing exotic.
Nothing requiring some enormous enterprise transformation programme.
We will use Excel itself to demonstrate the principle.
And once you see that principle operating, we can return to Tim, Tom and our spaghetti diagram and ask a rather different question.
Not:
How can modern Excel help us manage this mess more efficiently?
But:
Why does the mess need to exist at all?
That is where Excel and the Digital Librarian begins.
Add comment