Field-tested patterns for Power Query, formulas, VBA, tables, and workbook design — short enough to apply today.
Name every applied step clearly (Filtered closed deals, not Filtered Rows). Prefer unpivot + proper types early. Use parameters for file paths and environment switches so the same workbook works on a laptop and a shared drive.
When joining dimension tables, check key uniqueness first. Expand only the columns you need. Disable “load to worksheet” for intermediate queries so the model stays lean and refreshes stay fast.
Reach for XLOOKUP, FILTER, UNIQUE, and LET before building seven-level nested IFs. Name intermediate calculations with LET so audits read like English, not archaeology.
Use IFERROR or IFNA only when you understand the failure mode. Prefer XLOOKUP(..., if_not_found) so blank-vs-error semantics stay intentional for charts and conditional formats.
One public entry point per button or ribbon action; private helpers do the work. Turn off ScreenUpdating and set Calculation deliberately during bulk writes — and always restore them in a clean exit path.
Loop ListObject rows instead of used ranges. Table columns survive insert/delete better than hard-coded column letters, and your code documents intent through structured references.
Separate sheets for Inputs, Data (or queries), Calc, and Reports. Hide or protect calc sheets when needed. Colour code tab groups so users know where to type versus where to look.
Convert every data island to a Table. Consistent header styles and freeze panes make large sheets usable. Document assumptions on a README sheet inside the file — not only in email threads.
Power Query for ETL. Formulas for live calc. Office Scripts or Power Automate for cloud-friendly flows. VBA for desktop UI and legacy Excel. Pick one primary path per job so the solution stays supportable.
Use the AI Hub for prompting patterns that draft M, formulas, and VBA — with verification steps built in.