EXCEL · POWER QUERY · VBA · POWER PIVOT
Excel becomes much more capable when its parts are treated as a small ecosystem rather than as interchangeable ways of solving the same problem.
Not every problem belongs in a formula
Formulas are excellent when the result should remain visible and reactive inside the sheet. Dynamic arrays, lookup functions and structured references can make a workbook remarkably expressive. But formulas become hard to maintain when they are forced to perform acquisition, cleaning, orchestration and user interaction at the same time.
Give each layer a job
- Tables and formulas: visible calculations and user-facing logic.
- Power Query: acquisition, normalisation and repeatable transformation.
- VBA: interaction, orchestration and operations that need explicit user actions.
- Power Pivot: relationships and measures when the analytical model becomes larger than a worksheet should carry.
The point is not to use all of them. It is to choose the smallest combination that keeps responsibilities clear.
A common example
Imagine a workbook that receives files every day, cleans them, compares them with reference data and produces an operational summary. Power Query can handle the incoming files and standardise the data. Tables can expose the result. Formulas can provide local calculations. VBA can manage buttons, refresh sequences or controlled exports.
If one technology is forced to do everything, the workbook usually becomes harder to understand. If the responsibilities are separated, each part stays smaller.
The real benefit is maintenance
A clear architecture does not only make the first version easier to build. It makes later changes safer. A new source can be added in Power Query without rewriting the user interface. A button can change without touching the data model. A calculation can remain visible in a worksheet instead of being buried in code.
Where the boundary should remain
Excel is not automatically the right place for persistence, complex permissions or large concurrent workflows. A well-designed workbook knows what it should not own. Databases, services and other applications should remain responsible for the parts they handle better.
The advantage is not using more Excel. It is using each part of Excel at the right point in the process.