This is the companion demonstration to our earlier explainer on the historical background of Excel and databases.
It follows naturally from the QCA Audit case study — another real-world example that helped Hiran de Silva triple his pay using Excel.
The Story
The project is called The Chairman’s CD Collection.
Imagine the chairman of a large global corporation — an avid collector of CDs.
He wants to make his personal collection available to all staff across the world.
In turn, he invites everyone else to share their own CD collections with the company.
So the requirement is this:
- Everyone should be able to view and search the entire collection.
- Everyone should be able to add their own CDs to that list.
- Nobody should be emailing spreadsheets around.
- There should be no administrator.
- And the system should continue to work indefinitely.
- Finally — it must be built in Excel.
At first glance, this might seem like an impossible mission.
After all, most people would say, “Spreadsheets aren’t designed for that.”
But the chairman is not only a lover of music — he’s also a lover of Excel.
And he knows Excel better than most.
Pause for Thought
Let’s pause for a moment.
How would you approach this challenge?
(Pause…)
Welcome back. Let’s see how the chairman does it.
Entering Data
On his desktop, the chairman opens a simple Excel form.
It asks for the details of each CD:
his name, the title, the artist, and the genre — which currently includes Pop, Jazz, and Other.
He enters a few CDs and clicks the OK button.
The form resets, ready for the next one.
In a few minutes, he’s entered about a dozen titles.
Where Is the Data Stored?
He clicks a button labeled My CDs, and instantly a list appears.
Every CD he just entered is listed neatly on the CD List sheet.
Now, once his staff start using the system, he can click All CDs —
and see everyone’s collection combined, in real time.
Every staff member around the world has the same Excel template.
They can all enter their CDs simultaneously and query the entire collection independently —
without ever sending a spreadsheet to anyone.
Searching the Collection
As the collection grows into the thousands, users can filter and search with ease:
- Show only Pop albums.
- List all CDs by The Beatles.
- Show all CDs uploaded by Martin.
- Or search for any title containing the word blue —
and find Frank Sinatra – Old Blue Eyes and Elvis Presley – Blue Hawaii.
Flexible User Experience
Some users prefer entering their data directly in a table instead of a form.
That’s fine — they can do that too, and click OK once when they’re done.
They can even use Flash Fill or copy-down techniques as usual.
The system automatically uploads their new entries to the shared collection.
Updating the System
Suppose the chairman wants to extend the list of genres.
He simply edits the genre list in the spreadsheet —
adds Soul, Country, Progressive, and Movies —
and clicks OK.
From that moment on, every user’s drop-down will include the new genres.
Any new idea can be implemented in minutes.
How It Works
In the company’s shared folder, the chairman creates a single Access database file.
You can do this by right-clicking in the folder — just as you would create a Word or Excel file.
Inside it, we define one simple table with column names matching our form:
Name, Title, Artist, Genre, Office, and so on.
That’s all.
You don’t even have to open Access again.
No Access skills are required.
No administrator is needed.
If You Don’t Have Access Installed
Even if you don’t have Access installed, Excel can create the database for you.
In this workbook, there’s a button called Create Access Database.
It uses the folder path specified in the settings.
Click it once — and Excel automatically creates the Access database file.
Click another button — and Excel creates the table you need inside it.
Excel has just built its own relational database.
The Digital Librarian
This is what Hiran de Silva calls The Digital Librarian —
the place where spreadsheets prefer to store the data they work with.
Rather than trapping data inside each spreadsheet, scattered all over the place, that needs sending and receiving,
our spreadsheets send and retrieve their data from a shared, structured library —
just like books being borrowed and returned.
Each spreadsheet remains small, light, and fast.
And because no files are being copied or emailed, there are no moving parts —
and therefore, nothing to break.
How the Data Flows
When the chairman clicks OK, or Put, the spreadsheet sends the entered data into the Access table —
not into a local worksheet.
When he clicks My CDs or All CDs, the spreadsheet refreshes.
It’s the same mechanism, simply reversed:
Put to upload, Get to retrieve.
This is achieved using two short lines of VBA:
one to open a brief ADO connection,
and one to execute a simple SQL statement.
The operation takes milliseconds —
and it works from anywhere in the world.
Why It Matters
Any enhancement or new idea can be deployed instantly —
because we’re working entirely in Excel,
which remains one of the most powerful and flexible software tools ever built.
There are no external Add-Ins. It’s native Excel.
What makes the difference is architecture, not features.
The hard part isn’t the code — it’s the thinking:
deciding what the user experience should be.
Once that’s clear, the implementation is always just a matter of Get and Put.
Conclusion
This demonstration shows Excel acting as a true database client —
not as a rival to databases,
but as their natural partner.
In the real business world, this is exactly what we expect a database to do:
store data safely,
serve it flexibly,
and enable hundreds of users to collaborate seamlessly.
This is the power of Excel with a Digital Librarian —
and it’s what Excel as a database truly means.
Add comment