Excel, the modern parts
Dynamic arrays (FILTER, UNIQUE, SORT, SEQUENCE) removed most of the reasons to write helper columns, and LET and LAMBDA made it possible to name intermediate steps instead of nesting eight functions on one line. If your workbook predates those, a rebuild of the calculation layer is often faster than patching it.
Power Query for anything that arrives as a file
If someone downloads a CSV every week and pastes it into a tab, that is a Power Query job. The transformation is recorded once, runs on refresh and does not break when the export gains a column. It also handles the unglamorous work — unpivoting, splitting, type coercion — far more reliably than formulas.
VBA, only where it earns its place
VBA is still the right tool for interacting with the Excel object model: formatting loops, generating one file per row, printing, or driving a dialog. It is the wrong tool for pulling and reshaping data, which Power Query does better and without a macro-enabled file. We tell you which side of that line your problem falls on. See VBA vs Google Apps Script if you are choosing between platforms.
The handover matters as much as the build
Every deliverable includes a documentation sheet: what each input is, where the data comes from, what to change when the fiscal year rolls over. A model nobody but its author can maintain is a liability, not an asset.