By Hiran de Silva

What is magic?

The science-fiction writer Arthur C. Clarke famously gave us a wonderfully useful definition:

“Any sufficiently advanced technology is indistinguishable from magic.”

That tells us something important about magic.

Magic isn’t necessarily about what something does.

It is about the gap between what we believe is possible and what we have just seen happen.

Drop a heavy object and we expect it to fall.

Make the Statue of Liberty disappear, pull a rabbit from an apparently empty hat, or saw somebody in half and put them back together again, and we have crossed a boundary.

Something has happened that our existing model of reality didn’t predict.

That is where the magic lives.

And this provides a fascinating way of looking at Excel.

Because there isn’t one kind of Excel magic.

There are several.

And the Excel magic that astonishes you may depend entirely upon where you are standing.

The Magician’s Audience

Imagine a pyramid.

At the bottom is the largest audience.

These are people who know relatively little about how a particular magic trick works. Show them something sufficiently unexpected and the reaction is:

Wow!

Higher up the pyramid are people who have seen the trick before.

Higher still are people who understand some of the mechanics.

At the top are the experts. They know about the mirrors, trapdoors, props and machinery.

You can still impress them—but you’ll have to work harder.

A professional magician can watch another professional magician perform a familiar illusion and still applaud. They may know exactly how it works, but appreciate the finesse, timing, presentation and sheer panache of the performance.

But if you want to astonish them?

You’re going to need a better trick.

This is important when we talk about Excel magic.

Because the Excel community contains exactly the same pyramid.

Magic Number One: Power Query

Consider Tim.

Tim receives 50 CSV files containing data from different parts of the organisation.

His job is to turn them into a consolidated report.

Traditionally, Tim might open files, copy data, paste data, manipulate columns, write formulas, perform lookups and eventually assemble everything into something that management can use.

It might occupy him for most of the week.

Then somebody shows Tim Power Query.

Put the 50 CSV files into a folder.

Point Power Query at the folder.

Transform the data.

Load it.

Build the PivotTable.

Refresh.

Suddenly, work that previously occupied Tim for days can happen in moments.

But here comes the finale.

Next month another CSV file arrives.

What does Tim do?

He puts it in the folder.

Refresh.

The new data appears.

Ta-da!

That is precisely the kind of demonstration through which Power Query became enormously popular in the Excel community.

And it absolutely can look like magic.

Why?

Not because loading files from a folder possesses some supernatural quality.

It is magic because of what Tim was doing before.

The boundary of Tim’s world was manual assembly.

Power Query crossed that boundary.

That’s the magic.

But Now Move Up the Pyramid

Let’s pull the camera backwards.

Where did those 50 CSV files come from?

Suppose they represent 50 offices around the world.

And suppose those offices aren’t generating data once a month.

They’re generating it continuously.

Sales.

Orders.

Enquiries.

Stock movements.

Whatever the process happens to be.

The underlying business is operating continuously, perhaps 24 hours a day.

Suddenly we can see something that Tim couldn’t.

Tim’s Power Query solution may be fantastically efficient at processing the files once they reach Tim.

But why are files reaching Tim at all?

That’s a completely different question.

Suppose each of those 50 offices sends a spreadsheet every Friday.

Tim refreshes on Monday morning.

Wonderful.

Management now receives the report on Monday instead of waiting several days.

But the report still represents Friday.

So perhaps everybody sends Tim a file every evening.

Now Tim refreshes every morning.

Even better.

But somebody still has to send the files.

They have to arrive.

They have to be complete.

Tim has to refresh.

His boss has to distribute the resulting report.

And perhaps 100 or 200 managers around the organisation then receive another spreadsheet.

At this level of the pyramid, the original Power Query magic begins to look rather different.

Because now another trick is possible.

Magic Number Two: Nobody Sends Anything

Leave the spreadsheets where they are.

The 50 people around the world continue doing their work exactly where they are doing it.

But now there is a small change.

When somebody updates data, the spreadsheet has a button:

PUT

The data is written to one centrally accessible relational database.

No spreadsheet is sent to Tim.

No CSV file needs to arrive in a folder.

Indeed, Tim doesn’t need to consolidate anything.

Now consider the people consuming the information.

They have their own Excel applications.

Their spreadsheets have another button:

GET

Or perhaps simply Refresh.

A regional manager opens a report and refreshes.

A country manager refreshes.

The Finance Director refreshes.

The CEO refreshes.

And what do they see?

Not Friday’s data.

Not yesterday evening’s data.

Not whatever happened to have arrived in Tim’s folder when he last pressed Refresh.

They see the current centrally held data, updated from those 50 locations.

Nobody has sent them a spreadsheet.

Nobody has emailed them a report.

Nobody has consolidated 50 files.

Nobody has distributed the result.

We have changed the architecture.

The spreadsheets have become clients of shared data.

This is Hub and Spoke.

This is what I call the Digital Librarian.

And to an audience whose previous reality was sending files to Tim?

That ought to look like magic.

Now Something Interesting Happens

At this point, people’s reaction to the magic depends upon where they stand.

The 200 managers who can suddenly refresh their own reports whenever they want may think it is marvellous.

Tim might have mixed feelings.

His boss might wonder what happened to the process he used to manage.

And someone who has spent years teaching increasingly sophisticated methods of consolidating files might look at the empty folder and wonder where all their files went.

The technology hasn’t changed its properties.

The audience has changed.

Which brings us to another magician.

Magic Number Three: Replace Excel

For years, the Excel replacement industry has performed a very effective magic trick of its own.

Take the traditional spreadsheet process.

Files are emailed around.

Different versions proliferate.

People change things.

Numbers disagree.

Someone has to reconcile them.

Consolidation becomes difficult.

Nobody quite knows which spreadsheet contains the latest version of the truth.

The vendor points to this mess and says:

There must be a better way.

And then comes the reveal.

Put the process onto our centrally managed cloud platform.

Everyone works with centrally controlled data.

Consolidation happens centrally.

No emailing spreadsheets.

No version chaos.

No frantic reconciliation.

Compared with the point-to-point spreadsheet architecture that preceded it, the demonstration can be spectacular.

Once again:

Magic.

And the vendors are perfectly entitled to make that comparison.

The problem comes when we introduce the trick they didn’t show you.

Magic Number Four: Put Excel Back In

Suppose we accept the vendor’s diagnosis.

Point-to-point spreadsheet architecture is a mess.

Agreed.

Data should be stored centrally.

Agreed.

Users should be able to retrieve current data rather than exchange physical files.

Agreed.

But there is an assumption hiding inside the vendor’s trick:

To obtain those benefits, Excel has to disappear.

Does it?

Let’s return to our Digital Librarian.

The data is already central.

The spreadsheets already PUT data into it.

The spreadsheets already GET data from it.

Nobody needs to send spreadsheets around.

We already have the central truth.

We already have global reach.

We already have the Hub-and-Spoke architecture.

But now something rather important happens.

Jane wants a completely different report.

Fine.

Design it in Excel.

Point it at the Digital Librarian.

GET.

John needs a specialised operational application.

Build it.

Connect it to the same central data.

GET.

Sarah needs to submit information back into the process.

PUT.

The Finance Director wants another view.

Build it.

The regional manager wants a completely different presentation.

Build it.

They can all be different.

They can all be customised.

They can all use Excel.

And they can all be consuming the same centrally governed data.

This changes the argument completely.

Because we have separated two things that are routinely confused:

where the data lives

and

what users do with it.

The data does not have to live inside hundreds of disconnected spreadsheets merely because the users work with Excel.

That is the trick.

And Here Comes the Final Reveal

There is an additional irony.

Imagine that the organisation buys the big cloud planning system.

It solves the central process beautifully.

Then somebody needs something the standard system wasn’t designed to do.

A specialist report.

An unusual calculation.

A departmental process.

An analysis peculiar to this organisation.

“What do we do?”

Perhaps it can be configured.

Perhaps it requires consultancy.

Perhaps it becomes a feature request.

Or perhaps someone points to a familiar button:

Export to Excel.

And suddenly we are back outside the castle walls.

The central system may still contain the official data, but users begin exporting subsets into spreadsheets to perform the countless unpredictable tasks that real organisations require.

Those spreadsheets are once again disconnected.

Versions begin appearing.

Point-to-point processes begin growing.

Reconciliation returns.

The very spreadsheet behaviour the replacement system was supposed to eliminate can reappear around its edges.

The Digital Librarian takes a different approach.

It doesn’t say:

Stop using Excel.

It says:

Stop using Excel badly.

Keep Excel’s extraordinary flexibility at the edge.

Put the shared data in the centre.

And give those spreadsheets controlled access to it.

That is a very different proposition.

So Which One Is Magic?

That’s the question.

If your existing reality is manually combining 50 CSV files, Power Query may look like magic.

And rightly so.

If your existing reality is Power Query efficiently consolidating files that people continually send to one another, eliminating the files altogether may look like magic.

If your existing reality is hundreds of disconnected spreadsheets flying around an organisation, a centrally managed planning platform may look like magic.

And if your existing reality says that the only way to obtain centralisation and governance is to abandon Excel, discovering that Excel can participate in the same Hub-and-Spoke architecture may look like magic too.

Each trick crosses a different boundary of expectation.

And that is why arguments about Excel technology so often go nowhere.

People are standing at different levels of the pyramid.

One person excitedly demonstrates something that has crossed the boundary of their technological world.

Someone standing further up the pyramid says:

“But why are you doing that in the first place?”

Neither reaction necessarily tells us whether the technology is useful.

It tells us something about the observer’s operating envelope.

The Most Important Question Isn’t “Is It Magic?”

It is:

Magic compared with what?

That is the question we should ask whenever somebody demonstrates a revolutionary Excel technique, a new Microsoft feature, an AI tool, a planning platform—or indeed my Digital Librarian.

What was the starting point?

What problem disappeared?

And once that problem has disappeared, what becomes the next constraint?

Because technological progress doesn’t stop when somebody says “Wow!”

The audience simply moves up another level.

And then somebody has to perform the next trick.

So the next time somebody shows you some Excel magic, enjoy it.

Applaud the magician.

Admire the performance.

But then ask one more question:

Which level of magic am I looking at?

Because somewhere further up that pyramid, there may be another magician preparing to make the Airbus disappear.