How Knowing Why … Triples Your Pay With Excel

By Hiran de Silva

Let me begin with a deliberately provocative statement.

Power Query is dead.

No, Power Query isn’t actually dead.

It is an extraordinarily useful tool. It is a powerful ETL technology. There are problems for which Power Query is entirely appropriate.

But that isn’t my point.

My point is much more important to your career.

Because if you want to become genuinely valuable with Excel — valuable enough that somebody might double your pay, triple your pay, or entrust you with problems far beyond your job description — then there is something you need to understand.

Your value does not come from knowing powerful Excel features.

It comes from knowing when you don’t need them.

And that requires something considerably more valuable than technical knowledge.

It requires you to think.

---

Business Problems Never Stop

Every organisation has problems.

Small problems.

Large problems.

Accounting problems.

Finance problems.

Data problems.

Workflow problems.

Operational problems.

And entirely new problems that nobody anticipated yesterday.

They happen continuously.

And perhaps the most ubiquitous tool available for solving problems involving data and workflow is Microsoft Excel.

Full stop.

Excel is everywhere.

Almost anybody can reach for it.

It is the modern equivalent of walking to the stationery cupboard, picking up a pad of analysis paper, a pencil and a calculator, and getting to work.

For simple problems, this works wonderfully.

Even somebody with relatively little Excel knowledge can often create something useful.

But then something interesting happens.

The problem gets harder.

And when the problem appears difficult, we instinctively assume that the solution must become difficult too.

That assumption is where the trouble begins.

---

The Progress Trap

We have been conditioned to think about technological progress in a particular way.

Think about the Wright Brothers’ Flyer at Kitty Hawk in 1903.

Then think about Concorde.

Think about Robert Goddard’s primitive rockets.

Then think about Saturn V taking human beings to the Moon.

The progression seems obvious.

Greater challenge.

Greater sophistication.

More advanced technology.

More powerful machinery.

And we have imported exactly the same mental model into Excel.

Over the last fifteen or twenty years we have acquired some extraordinarily powerful capabilities.

Excel Tables.

Power Pivot.

Power Query.

M.

DAX.

Dynamic arrays.

LET.

LAMBDA.

Python.

And many more.

These are impressive technologies.

But their existence creates a dangerous assumption:

If the business problem is advanced, the Excel feature required to solve it must also be advanced.

That sounds completely reasonable.

It may also be completely wrong.

---

Social Media Reinforces the Assumption

Now add social media.

Certain Excel subjects attract attention.

Power Query attracts attention.

Dynamic arrays attract attention.

LAMBDA attracts attention.

Complex formulas attract attention.

New features attract attention.

And therefore those subjects get demonstrated, promoted, shared and taught.

There is nothing inherently wrong with that.

But there is an important distinction that Excel learners are rarely encouraged to make.

A demonstration of a technique is not necessarily a recommendation for solving the business problem used in the demonstration.

Somebody may want to teach Unpivot.

So they need a problem that allows them to demonstrate Unpivot.

Somebody wants to demonstrate LAMBDA.

So they find a problem around which they can build an interesting LAMBDA formula.

The resulting solution may be perfectly correct.

But that does not establish that it is the best way to solve that problem in the real world.

Forget that distinction, and something very important happens.

You begin learning solutions before you learn how to see problems.

And that can make you less capable rather than more capable.

---

Six Footballers Go To Dinner

Let me show you.

We begin with a wonderfully simple challenge.

A group of footballers go to dinner.

One pays the restaurant bill.

The others agree how much of that bill they should each bear.

Next time somebody else pays.

This continues.

The question is:

At any particular moment, who owes what to whom?

Look online for solutions to problems of this kind and you can find some technically impressive answers.

Power Query.

Unpivot.

SUMIFS.

Dynamic arrays.

LAMBDA formulas.

And they can produce the correct answer.

But something subtle has happened.

The learner starts thinking:

This must be a complicated problem.

Why?

Because the solutions look complicated.

The more complicated solutions you see, the more the apparent difficulty of the problem itself becomes reinforced.

And eventually you may believe that becoming “advanced” at Excel means learning increasingly sophisticated ways of manipulating that data.

But suppose we forget Excel completely.

Suppose instead we ask:

What actually happened?

That changes everything.

---

Cristiano Ronaldo Pays The Bill

Suppose Cristiano Ronaldo pays for dinner.

The spreadsheet records the amounts that everybody has agreed to contribute.

Fine.

But is that the complete transaction?

No.

Something is missing.

Ronaldo paid.

Therefore, while the other diners owe their respective amounts, Ronaldo is owed the corresponding total.

Record that other side of the transaction and something beautiful happens.

The transaction nets to zero.

Of course it does.

Because we have now completely recorded what happened.

We are using a principle that accountants have understood for hundreds of years: a transaction has corresponding effects.

Now repeat that for every dinner.

And how do we calculate everybody’s balance?

Power Query?

No.

LAMBDA?

No.

Dynamic arrays?

No.

SUM.

Possibly the first Excel formula you ever learned.

Total each person’s column.

That’s it.

And the grand total of everybody’s balances?

Zero.

We have taken something that appeared to require advanced Excel and reduced it to first principles.

But this is where the story becomes much more interesting.

Because I don’t particularly care about six footballers having dinner.

I care about what happens next.

---

Now Make It 5,000 People

Suppose there aren’t six people.

Suppose there are 500.

Or 5,000.

They aren’t all having dinner together.

Different combinations of them meet in London, Paris, Madrid, New York, Singapore or Dubai.

Sometimes Ronaldo pays.

Sometimes somebody else pays.

The bill isn’t necessarily divided equally.

People join the scheme.

Transactions continue indefinitely.

We need to know everybody’s current balance.

We need to see the transactions behind that balance.

And perhaps people around the world submit their transactions through Excel.

We have transformed a little spreadsheet puzzle into a global business process.

Now ask yourself a question.

Take the clever Power Query solution you found for the original six-person problem.

Can you turn it into that?

Take the beautiful LAMBDA formula.

Can you turn that into a global, multi-user, continuously operating process?

Perhaps not.

But something extraordinary has happened to our supposedly primitive solution.

It scales.

Once we understood the underlying transaction properly, the apparent complexity disappeared.

The problem got enormously bigger.

The thinking got simpler.

And suddenly we can build the global process around it.

That is the lesson.

---

This Is Where Your Pay Comes Into It

Imagine two Excel users.

Both are presented with an unfamiliar business problem.

The first thinks:

Which Excel feature should I use?

Power Query?

Dynamic arrays?

LAMBDA?

Python?

They search the internet.

They find something similar.

They reproduce the technique.

They may create a technically impressive spreadsheet.

The second person asks:

What is actually happening here?

What is the transaction?

What is the process?

What information do we actually need?

What is the simplest representation of reality?

What happens if there are ten users?

What happens if there are 10,000?

What happens next month when the requirements change?

Those are very different people.

And to an employer, they can have very different values.

The first person possesses Excel knowledge.

The second person can create business value.

That distinction matters enormously.

Because businesses don’t fundamentally pay you for knowing Power Query.

They don’t pay you for knowing LAMBDA.

They don’t pay you for knowing Python.

They pay you because there is something they need done.

There is a problem.

There is a risk.

There is an opportunity.

There is something that takes three days that ought to take three minutes.

And they need somebody who can see a way through it.

---

The Power Drill And The Chair

Imagine you’re a carpenter.

Your customer asks you to make a simple chair.

Unfortunately, you don’t understand how chairs are constructed.

So you buy a more powerful drill.

Still can’t build the chair?

Buy an even more powerful drill.

Perhaps one with artificial intelligence.

Obviously, this is absurd.

The problem isn’t your drill.

You don’t understand the chair.

Yet this is remarkably similar to what we sometimes do with Excel.

We encounter a problem we don’t understand.

Then we assume another Excel feature will compensate for the gap in our understanding.

It won’t.

Power Query cannot compensate for not understanding the problem.

Neither can LAMBDA.

Neither can Python.

Neither can AI.

Your Excel skills therefore do not begin with Excel.

They begin with your ability to see.

To understand.

To simplify.

To recognise structure.

To question assumptions.

And to find the underlying principle from which the solution becomes obvious. That is the central argument of this series: advanced Excel capability does not necessarily mean using advanced Excel features.

---

Sometimes Knowing Less Helps You See More

And this leads to the deliberately uncomfortable proposition behind Power Query Is Dead.

Sometimes knowing an advanced feature can actually get in your way.

Not because the feature is bad.

Because the feature becomes the lens through which you look at the problem.

If you’re thinking about Unpivot before you understand the transaction, your mind is already travelling down one particular road.

If you’re thinking about writing a LAMBDA before understanding the business process, you’ve already begun solving a problem that you haven’t properly defined.

The tool has started directing the thinking.

That is backwards.

The problem should determine the architecture.

The architecture should determine the technique.

The technique should determine the tool.

Not the other way around.

---

So, Is Power Query Really Dead?

Of course not.

ETL isn’t dead.

Power Query isn’t dead.

And I am certainly not suggesting that you shouldn’t learn it.

I am suggesting something far more useful.

Stop confusing advanced tools with advanced capability.

Learn Power Query.

Learn dynamic arrays.

Learn LAMBDA.

Learn SQL.

Learn VBA.

Learn Python.

Learn whatever technologies are useful to you.

But put them in the toolbox.

Don’t allow them to become your worldview.

Because the person who walks into a meeting and says:

“I know Power Query.”

…may be useful.

The person who walks into the same meeting, understands a problem that everybody else believes is enormously complicated, reduces it to something astonishingly simple, and then scales that solution across the organisation…

…is valuable.

Sometimes extremely valuable.

That is how careers change.

That is how responsibilities change.

And yes.

That is how pay changes.

---

Beyond Power Query

This is therefore not really a series about Power Query.

It is about going beyond Power Query.

Beyond LAMBDA.

Beyond dynamic arrays.

Beyond formulas.

Beyond whatever Excel feature happens to be fashionable this year.

Because I want to challenge an idea that has become deeply embedded in Excel learning:

To advance with Excel, you must learn increasingly advanced Excel features.

I don’t believe that is true.

In the next example, we’ll look at account reconciliation.

Once again, we will encounter solutions involving complicated formulas and Power Query.

And once again, we’ll ask whether the complexity belongs to the problem…

…or whether we put it there ourselves.

So when somebody tells you that the future of your Excel career lies in learning the next powerful feature, learn it if it’s useful.

But remember this:

The most powerful Excel feature is still the person sitting in front of it.

And if that person can see what everybody else has missed…

…that’s where the value is.

That’s where the opportunity is.

And that is how knowing why can triple your pay with Excel.