The History of Separating Data from the Spreadsheets

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.

To understand how we arrived at that misconception, it is worth going 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 at all.

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 analysis 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.

Technology Solved This Problem Decades Ago

Here is the irony.

Modern computers do not suffer from the limitation that created 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.

Indeed, 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 data.

Excel performs the role for which spreadsheets are extraordinarily well designed:

calculation, modelling, analysis, presentation and human interaction.

Excel can GET the information it needs from the system of record.

The user can work with it.

And when the business process requires new information to become part of the permanent corporate record, Excel can PUT that information back.

SELECT.

INSERT.

UPDATE.

DELETE.

The two applications do not compete with each other.

They perform complementary functions.

The Accountant Never Needed to Move the Stockroom

Think again about our management accountant visiting the warehouse.

He didn’t need to drag the stockroom back to his office.

He extracted the information he needed, performed his work and, where appropriate, the results of business transactions eventually flowed back into the organisation’s records.

Modern technology allows us to do the same thing electronically—and much more efficiently.

The database can remain the trusted central librarian.

Excel can ask:

Give me these records.

Give me this customer’s transactions.

Give me this cost centre.

Give me these 400 shops.

Give me this country’s budget.

Excel receives precisely what the user needs.

And when the user has something to contribute:

Put this budget into the central record.

Put this comment against that item.

Update this status.

Record who submitted it and when.

Now we have the best of both worlds.

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 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 question:

Where should the permanent data live?

And the companion question:

What is this spreadsheet’s relationship with the system of record?

Without those questions, we risk teaching ever more sophisticated techniques for managing a problem that good architecture could remove.

The spreadsheet does not have to become the database.

And the database does not have to replace the spreadsheet.

They can work together.

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.

Return whatever needs to become part of the permanent record.

Protect the integrity of the master record throughout.

Ancient administrators understood the principle.

Stockroom clerks understood it.

Bookkeepers understood it.

Accountants understood it.

Database designers understand it.

And modern Excel is perfectly capable of participating in exactly the same architecture.

What changed was not the fundamental requirement of business.

What changed was that personal computers made copying information so effortless that we gradually forgot the distinction between the record and a working copy of the record.

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 able to communicate with one trusted central source of data.

Each spreadsheet can be simple.

Each user can see precisely what they need.

Different people can perform completely different jobs.

Yet they can all be working with the same underlying corporate truth.

That 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.

Perhaps the great mistake was never that businesses used too much Excel.

Perhaps it was that, somewhere along the journey from the stockroom card to the personal computer, we forgot that the spreadsheet and the system of record were supposed to be two different things.