We Have Forgotten Something That Civilisations Knew Thousands of Years Ago
By Hiran de Silva
The miracle of Excel is still not fully realised.
There are extraordinary things that ordinary business users could be doing with Excel today, using technology that has existed for decades. Yet much of that opportunity remains hidden by misdirection, miseducation and misconceptions about what a spreadsheet is supposed to do.
My Mission Impossible demonstrations, my business process examples and the screenplay I am currently developing all have essentially the same purpose: to demonstrate what becomes possible when we stop thinking about Excel merely as a file containing some data and some formulas.
But there is a bigger principle behind all of this.
It is not really an Excel principle at all.
It is a principle of professional collaborative working.
And to understand it, we need to go back—not 20 years, or even 50 years, but thousands of years.
Because separating the permanent record from the document used to work with information is not a new idea.
It may be one of the oldest principles of organised administration.
Ancient Civilisations Had Systems of Record
Imagine trying to build the pyramids if hundreds of people wandered around carrying different pieces of papyrus containing their own versions of how many workers, tools and materials were available.
Which scroll contains the truth?
What happens when two scrolls disagree?
How do you consolidate them?
How do you know which is the latest version?
Ancient civilisations obviously had to maintain records. Somewhere there had to be an accepted record of materials, workers, payments, taxes, land, livestock and everything else required to administer a civilisation.
That record stayed somewhere.
Authorised people could consult it. They could extract information from it. They could take information away to perform a particular task.
But the master record did not wander around with them.
The principle is simple:
There is a difference between the permanent record and the information extracted from it for somebody to do some work.
That distinction survived for thousands of years.
The Stockroom Before Computers
We don’t have to speculate about ancient Egypt to see the principle in action. We only have to remember how businesses operated before computers.
Consider a traditional stockroom or warehouse.
There might be a bin card recording the transactions and current quantity of an item. Goods came in: somebody recorded the movement and updated the balance. Goods went out: somebody recorded the movement and updated the balance.
Periodically, somebody performed a stocktake.
If the card said there should be 2,000 widgets on the shelf and only 1,950 were actually there, an adjustment was made.
The record was updated.
That was the system of record.
Now imagine that a management accountant wanted to perform an analysis.
The accountant went to the stockroom, looked at the records and wrote the relevant figures onto a sheet of analysis paper. He then took that sheet back to his desk and worked with the numbers.
The analysis paper had a completely different purpose.
It wasn’t the stock record.
It was a working document derived from the stock record.
And when the accountant had finished his analysis, the piece of paper could eventually be discarded without destroying the organisation’s stock records.
The same principle applied to accounting.
The ledgers were the system of record. They didn’t go walkies around the building whenever somebody wanted to perform an analysis.
If an accountant wanted a trial balance, the balances were extracted from the accounting records and brought together into another working document.
Once again:
System of record over here.
Working document over there.
Two different functions.
Then Along Came the Personal Computer
When personal computers arrived in the early 1980s, the same two functions appeared in software.
We had database software for storing structured records.
And we had spreadsheet software for calculation and analysis.
I experienced this transition personally.
I had dBASE II in my office. I also used Microsoft Multiplan, before Lotus 1-2-3 became established in the UK.
But early personal computers had an important limitation.
Under DOS, you generally worked in one application at a time.
If you were writing something in a word processor and wanted to use your spreadsheet, you closed the word processor and loaded the spreadsheet.
If you were working in the spreadsheet and needed something from the database, you couldn’t simply query the database while continuing to work in Excel as we can today.
You changed applications.
Sometimes that literally meant taking one disk out and putting another one in.
That was cumbersome.
So spreadsheet vendors naturally started adding features that allowed their products to perform some database-like functions as well.
It made perfect sense.
If the spreadsheet was already open, why shouldn’t it also hold the data you needed?
And therein lay the seed of a problem that would become much bigger decades later.
A Spreadsheet Can Be a System of Record
There is an important distinction here.
I am not saying that a spreadsheet cannot be a system of record.
It can.
The medium does not determine whether something is a system of record.
A paper ledger can be a system of record.
A set of cards in a stockroom can be a system of record.
A database can be a system of record.
And a Lotus 1-2-3 or Excel workbook can be a system of record.
What makes something a system of record is that the organisation has designated it as the authoritative, trusted master record.
Think about the main entrance to a building.
There may be several doors through which you can physically enter. But one is designated as the main entrance.
Its status doesn’t arise from some magical property of the door.
It arises because everyone has agreed what that door represents.
The same applies to information.
The system of record is the system of record because everybody understands:
This is the authoritative record.
Everything else is an extraction, working copy, report or derivative of it.
The 1990s Changed Everything
Maintaining that distinction became much harder when computers appeared on every desk.
Files became incredibly easy to copy.
Then networks made them easy to distribute.
Then email made it effortless.
Someone extracted information from the corporate system into Excel.
They changed it.
They emailed it to somebody else.
That person added another column and sent it to three more people.
Somebody copied part of it into another workbook.
Another department downloaded a fresh version from the corporate system next Tuesday.
Suddenly there were dozens—eventually hundreds—of spreadsheets containing different versions of corporate information.
The distinction between the authoritative record and the working document became blurred.
And once that distinction disappears, familiar problems follow.
Which version is correct?
Which is the latest?
Who changed this number?
Where did this figure come from?
Has somebody else changed it since I downloaded it?
How do we consolidate all these spreadsheets?
Why doesn’t Finance’s number agree with Operations?
These are routinely described today as Excel problems.
But are they?
Or are they symptoms of having forgotten a much older principle of information management?
We Started Building Private Ecosystems of Corporate Data
This is where the modern spreadsheet problem becomes particularly interesting.
People download information from a corporate system of record into Excel.
Nothing wrong with that.
That is exactly what spreadsheets are extraordinarily good at.
But then the downloaded information begins another life.
It is copied, extended, emailed, amended, transformed and incorporated into other spreadsheets.
Before long, users have created their own private ecosystem of corporate data outside the original system of record.
And remarkably, we then blame the spreadsheet.
The problem isn’t that somebody analysed corporate data in Excel.
That is precisely what Excel is for.
The problem is that we allowed the distinction between the corporate record and the working analysis to disappear.
But there is another important part of this story.
Because simply telling spreadsheet users to use the corporate system of record does not solve the problem either.
The Missing Layer: Our Own Digital Librarian
When we talk about a system of record, we naturally think about the large authoritative systems maintained by IT.
The ERP system.
The finance system.
The HR system.
The CRM.
The stock system.
These systems contain the organisation’s formally governed records.
And they are deliberately controlled.
That is a good thing.
Ordinary spreadsheet users should not normally have unrestricted permission to write directly into the company’s ERP database.
IT governance matters.
Security matters.
Validation matters.
Audit matters.
Data integrity matters.
But that creates an important question.
Where does the business put the information that it owns and needs to manage before that information becomes part of the formally governed corporate record?
Traditionally, the answer has often been:
In our spreadsheets.
And that is where the trouble starts.
Imagine twenty people collaborating on a business process using twenty spreadsheets.
One person enters something.
Someone else changes it.
Another person consolidates it.
Somebody emails another version.
Another workbook contains a copy.
Somebody else is trying to establish which version is current.
The problem is not that these people are using Excel.
The problem is that the spreadsheets themselves have become the shared storage system.
We have asked the working documents to perform the job of the librarian.
That is the missing layer.
What the business needs is its own Digital Librarian.
The Digital Librarian Is Our Local System of Record
The Digital Librarian is not the corporate ERP system.
It does not replace it.
And it does not compete with IT’s formally governed systems of record.
It serves a different purpose.
It is our local system of record for the business process.
It might belong to Finance.
Operations.
Sales.
A budgeting team.
A project.
A group of warehouses.
Or perhaps just six people collaborating on a relatively modest business process.
Instead of storing their common information across dozens or hundreds of spreadsheets, we identify the information everybody needs to share and give it a proper home.
We decide what information we need.
We decide what the tables should contain.
We decide how those tables relate to one another.
And we store that information centrally in a relational database.
For many Microsoft Office users, the simplest starting point may be Microsoft Access on a shared drive.
For something larger it might be SQL Server, Azure SQL or another suitable relational database.
The technology is secondary.
The important change is architectural.
The shared data is no longer owned by the individual spreadsheets.
The spreadsheets become clients of the Digital Librarian.
They GET the information they need.
People work with it.
And, where the business process requires it, they PUT information back.
Now everybody can be working through completely different spreadsheets while sharing the same centrally held information.
The Digital Librarian knows what has been stored.
It can know who supplied it.
It can know when they supplied it.
It can serve the same information immediately to somebody else’s spreadsheet.
It can maintain history.
It can enforce structure.
It can provide auditability.
It can provide controlled access.
That is exactly what a good librarian does.
This Is How We Do Spreadsheets Professionally
And this is perhaps the most important point of the entire story.
None of this is some exotic new theory about Excel.
We are simply applying to spreadsheets the universally accepted principles of professional collaborative working.
If one hundred people collaborate on documents, we don’t normally encourage everyone to maintain their own private master copy and periodically email different versions to one another.
If hundreds of employees need customer information, we don’t normally suggest everybody maintains their own customer list.
If many people need access to stock records, we don’t give everybody their own independent stock ledger.
Professional collaborative working has long understood some basic principles.
Shared information should have an agreed home.
There should be an authoritative version.
People should have appropriate access.
Changes should be controlled.
Important transactions should be auditable.
Everybody who needs the same information should be capable of receiving the same information.
Yet somehow, when we arrive at spreadsheets, we often abandon those principles.
We create hundreds of individual files.
We put the shared data inside them.
We copy the files.
We email them.
We consolidate them.
We reconcile them.
We build elaborate processes to discover which one contains the truth.
And then we call the resulting difficulties Excel Hell.
That is not professional collaborative working.
Professional spreadsheet working means giving the spreadsheet the freedom to be an extraordinary working document, while giving the shared information somewhere appropriate to live.
That somewhere is our Digital Librarian.
The Digital Librarian Does Not Replace Excel
This distinction matters enormously.
The spreadsheet remains where human beings actually work.
Excel is superb at that job.
Calculation.
Modelling.
Analysis.
Presentation.
Investigation.
Review.
Scenario building.
Decision support.
Human interaction.
The Digital Librarian is not trying to do those things.
Its job is much less glamorous.
It remembers.
It organises.
It protects.
It retrieves.
It records.
Excel asks:
Give me this cost centre.
Give me this customer’s transactions.
Give me these 400 shops.
Give me France.
Give me the latest version of this budget.
Give me the comments relating to these items.
The Digital Librarian responds.
And when the user has something to contribute, Excel can say:
Put this budget into the central record.
Put this comment against that item.
Update this status.
Record this submission.
Record who made it.
Record when they made it.
The two technologies are not competitors.
They are performing completely different jobs.
And What About the Real Corporate System?
Eventually, some of the information held by our Digital Librarian may need to become part of the organisation’s official corporate record.
That does not mean our spreadsheets suddenly need unrestricted permission to write directly into the ERP database.
There is usually a controlled doorway.
Finance provides one of the oldest examples.
The journal import.
The business prepares a journal in an agreed format.
The accounting system validates it.
Does the account exist?
Does the journal balance?
Are mandatory fields populated?
Are the dates valid?
Are the cost centres valid?
Does the reference conform to the required format?
Only valid data gets through.
That is exactly how it should work.
Our Digital Librarian can therefore prepare information for the corporate system through whatever governed interface the organisation provides.
That might be an API.
It might be an automated integration.
It might be an ETL process.
It might be a carefully specified CSV import.
The implementation can vary.
The architectural principle does not.
Three Layers — Three Different Jobs
We can now see the complete architecture:
WORKING DOCUMENTS ↔ DIGITAL LIBRARIAN → GOVERNED INTERFACE → CORPORATE SYSTEM OF RECORD
Each has a different job.
The working document is where the human being works.
The Digital Librarian is where the business process’s shared working information is organised, remembered and made available to everybody who needs it.
The corporate system of record is where the organisation’s official information is ultimately governed and preserved.
And between our Digital Librarian and those corporate systems sits an appropriate controlled interface.
Suddenly the supposed conflict between spreadsheets and databases disappears.
They were never supposed to be alternatives.
They are complementary tools.
Technology Solved the Technical Problem Decades Ago
Here is the irony.
Modern computers do not suffer from the limitation that helped create this behaviour in the first place.
We no longer have to close the spreadsheet, take out a floppy disk, insert another disk and load the database.
The spreadsheet and the database can talk to each other.
Microsoft provided the plumbing for them to do precisely that decades ago.
Excel and relational databases such as Access and SQL Server can work together.
The database performs the role for which databases are extraordinarily well designed:
storing, protecting, querying and maintaining structured shared data.
Excel performs the role for which spreadsheets are extraordinarily well designed:
calculation, modelling, analysis, presentation and human interaction.
Excel can GET information.
The user can work with it.
Excel can PUT information back.
SELECT.
INSERT.
UPDATE.
DELETE.
This is not about replacing Excel with a database.
It is about allowing each tool to do the job it is good at.
Yet We Still Behave as Though It Is 1983
This is perhaps the strangest part of the story.
The technological restriction disappeared decades ago.
But much of our spreadsheet behaviour didn’t change.
We still routinely put the data inside the workbook as though Excel cannot communicate with anything outside itself.
Then we build increasingly sophisticated mechanisms for importing, transforming, combining, reconciling and managing those files.
In other words, we are often solving a problem created by an architectural assumption that no longer needs to exist.
The assumption is:
My spreadsheet needs to contain my data.
Frequently, it doesn’t.
The spreadsheet needs access to the data.
Those are two completely different propositions.
The Missing Piece in Excel Education
This is why I believe there is a major gap in Excel education.
We teach formulas.
We teach functions.
We teach PivotTables.
We teach charts.
We teach Power Query.
We teach increasingly sophisticated ways of manipulating information once it has entered the spreadsheet environment.
All of those things can be useful.
But where do we teach the fundamental architectural questions:
Where should the permanent data live?
Which information belongs in the corporate system of record?
Which shared working information belongs in our Digital Librarian?
What is this spreadsheet’s relationship with both?
And perhaps most importantly:
How do we allow many spreadsheet users to collaborate professionally without turning hundreds of individual spreadsheet files into hundreds of competing systems of record?
Without those questions, we risk teaching ever more sophisticated techniques for managing problems that good architecture could remove.
Rediscovering an Ancient Principle
In some respects, what I am advocating is not revolutionary at all.
It is extraordinarily old-fashioned.
Maintain a trusted record.
Allow authorised people to extract what they need.
Let them perform their specialist work.
Allow them to contribute new information through a controlled process.
Keep shared information somewhere everybody can reach.
Maintain auditability.
Protect the integrity of the record.
And, when information is ready to cross into the organisation’s formally governed systems, pass it through the appropriate controlled doorway.
Ancient administrators understood the principle.
Stockroom clerks understood it.
Bookkeepers understood it.
Accountants understood it.
Librarians certainly understand it.
Database designers understand it.
And modern Excel is perfectly capable of participating in exactly the same architecture.
The Miracle of Excel
This brings me back to what I call the Miracle of Excel.
Excel becomes much more powerful when we stop insisting that everything has to live inside the workbook.
Imagine hundreds of spreadsheets, used by hundreds of people, all communicating with one Digital Librarian.
Each spreadsheet can be simple.
Each user can see precisely what they need.
Different people can perform completely different jobs.
Yet everybody can be working with the same shared information.
And when that information needs to become part of the organisation’s formally governed record, it crosses that boundary through the appropriate controlled interface.
This is the architecture behind many of the demonstrations I have been showing for years.
And the remarkable thing is that it does not require us to replace Excel.
Quite the opposite.
It allows Excel to concentrate on what Excel does exceptionally well.
The great mistake may never have been that businesses used too much Excel.
The mistake was expecting individual spreadsheet files to be the working document, the database, the collaboration mechanism, the audit trail and the system of record all at the same time.
Professional Excel separates those responsibilities.
The spreadsheet is the working document.
The Digital Librarian looks after our shared working information.
The governed corporate systems look after the organisation’s official records.
That is not an Excel workaround.
It is simply applying universally accepted collaborative working practices to spreadsheets.
And once we do that, a very large proportion of what we have come to call Excel Hell starts to look less like a problem with Excel—and much more like a problem with the way we chose to use it.
Add comment