What is this meeting about?
Beyond the basic excel formulas - this session will show you the formulas which might be handy to apply for a reward professional.
Starting from the Calculation engine, which formulas / functions have been used. And how can you apply those in your daily work?
This session will be a repeat of the session on Feb 19th which was fully booked.
Presentation:
Stream:
Key take-aways:
The session focused on how modern Excel capabilities—Tables, Dynamic Arrays, FILTER, UNIQUE, VSTACK, and especially GROUPBY—are reshaping reward analytics and enabling more robust, repeatable, and transparent pay analysis. These techniques, demonstrated through practical salary‑data scenarios, help reward professionals build reliable, scalable calculation engines aligned with the European Pay Transparency Directive. The training emphasized moving away from manual, error‑prone methods and instead using structures that update automatically and support multi‑criteria segmentation.
Below are the most relevant takeaways with broad relevance for HR & reward professionals.
- Modern Excel = Tables + Dynamic Arrays
Building reward models on Excel Tables combined with dynamic formulas (FILTER, UNIQUE, SORT, VSTACK, GROUPBY) ensures calculations update automatically and remain structurally stable over time. This approach reduces errors, minimizes manual refreshes and is for reliable, repeatable pay‑transparency workflows.
- Multi‑criteria calculations with FILTER + MEDIAN/AVERAGE
Instead of legacy AVERAGEIF limits, combining FILTER with AVERAGE/MEDIAN enables precise analyses—for example gender‑specific median pay per function and entity. This flexibility is essential when producing the complex multi‑dimensional breakdowns required under pay‑equity and pay‑transparency frameworks.
- GROUPBY as a superior dynamic pivot alternative
The new GROUPBY function allows robust summarisation (sum, average, median, headcount) by country, legal entity and currency—updating instantly as data changes. Because it supports medians and avoids the manual refresh burden of pivot tables, it is for building sustainable calculation engines for transparency reporting.
- VSTACK & HSTACK for multi‑country consolidation
With VSTACK, users can consolidate multiple country datasets into one dynamic table without copy‑paste. Combined with HSTACK for side‑by‑side scenario or summary views, this creates clean, scalable multi‑country reporting structures—crucial for internationally operating organisations.
- XLOOKUP & UNIQUE/SORT improve data reliability
Replacing VLOOKUP with XLOOKUP, and using UNIQUE/SORT to standardise legal entities, functions and job levels, strengthens data consistency and prevents structural errors in downstream analysis—a foundation for compliant pay‑gap reporting.
AI generated by Copilot from designated meeting content only | Rumbold
