Twelve Monthly Exports, One Workbook, 40 Desks

Recommendation: one Power Query query over a folder of the twelve CSVs, output loaded to a single staging table and/or the Data Model.

Reasoning against the constraints:

• Volume is not the blocker; recalculation is. 380,000 rows fits comfortably under the 1,048,576-row sheet limit, so no export has to be split. But whole-column VLOOKUPs against 380k rows across twelve sheets means millions of cell evaluations on every recalc - that is where the four-minute open and per-keystroke lag come from.
• Power Query evaluates once per refresh, not per keystroke. The transformation runs at refresh time and the result is stored as a static table; the workbook no longer carries the lookup cost.
• The Data Model compresses the output. Loading the query to the Data Model (rather than a sheet) keeps the 190 MB file from growing further and lets PivotTables drive the year-over-year views.
• Client compatibility. Get & Transform is built into Excel 2016 Professional Plus; confirm the 38 seats are ProPlus, and note that 2010/2013 seats would need the download add-in. No database, no server, no new licence.
• Maintainability. Point the query at a folder, drop next month's CSV in, refresh. Twelve hand-appended sheets and hand-edited formulas become one query plus a documented source folder.

Rejected: a formula-based consolidation sheet (same recalc cost, now in one fragile place); Access or Power BI (no budget/approval); keeping the current design and just deleting columns (the lookup pattern, not the column count, is the cost).

Reject it for the shared deliverable. XLOOKUP, FILTER, UNIQUE and SORT are Microsoft 365 / Excel 2021 functions. They do not exist in Excel 2016.

What actually happens when a Microsoft 365 user saves a workbook containing them and a 2016 user opens it:

1. The formula is stored with a compatibility prefix (_xlfn.XLOOKUP) and the 2016 client cannot resolve it, so the cell returns #NAME?.
2. Dynamic arrays have no spill behaviour in 2016; the formula is treated as a legacy single-cell formula and returns only the first value or an implicit-intersection result.
3. The two users who built it see correct numbers; the 38 who did not see errors or silently wrong totals - the worst failure mode for a leadership report.

Options, in order of preference:

• Standardise on INDEX/MATCH (or a PivotTable) for anything that lands in the shared file. It is universally supported and the colleague's ergonomic argument is a preference, not a requirement.
• If the colleague needs modern functions, let them build a personal analysis file that consumes the published values, leaving the canonical workbook on legacy-safe formulas.
• Publish the monthly output as values only in a distribution copy and keep formulas in the working copy.

If the org later standardises on Microsoft 365 Apps everywhere, revisit - but not while 95% of seats are on 2016.

Separate the pipeline from the personal layer.

• The canonical workbook should contain only: the Power Query output table, the Data Model, and the agreed PivotTables. Lock it down with a protected structure (Protect Workbook > Structure) so sheets cannot be added or deleted by accident.
• Give the two power users a second workbook or a second sheet that reads the published output (link to the values table, or have them re-point their own query at the same source folder). Their calculated columns live there and break only themselves if they change.
• If their requests recur monthly, that is a signal the report is under-specified: capture the two or three calculations they keep adding and promote the agreed ones into the canonical query, so the next person does not rebuild them.
• Escalation trigger: if requests turn into ad hoc slicing by region, product and rep, the spreadsheet is the wrong tool - that is the point to ask for Power BI, and the monthly cost of these workarounds is your evidence.