Excel: Power Query vs Pivot Tables

Key insights

• Video summary: The YouTube video demonstrates replacing classic Pivot Tables with newer formula-based functions like GROUPBY and PIVOTBY.
  It shows how formulas keep logic visible and update automatically when source data changes.
• GROUPBY basics: Use GROUPBY to aggregate by one or more fields for single-dimension summaries.
  It supports totals, sorting, filters, and aggregations such as MEDIAN that standard PivotTables may not offer.
• PIVOTBY features: Use PIVOTBY to build two-dimensional pivot-like outputs with rows and columns.
  The video shows combining PIVOTBY with functions like VSTACK and IF to merge sheets and create month-by-region summaries automatically.
• Key advantages: Formula-based reports are more transparent, flexible, and easier to integrate into dashboards.
  They benefit from dynamic arrays that update automatically and produce cleaner headers and customizable layouts.
• Recommended workflow: Start with a clean Excel Table, pick grouping fields, apply aggregation, then refine output using FILTER, UNIQUE, and SORT.
  This produces reliable, formula-driven summaries that work well in dashboards and reports.
• When to keep PivotTables or use Power Pivot: Microsoft still supports classic PivotTables, and they remain useful for many tasks.
  Choose Power Pivot / DAX when you need advanced data modeling or complex measures beyond formula-based summaries.

Introduction: A fresh look at an old tool

In a recent YouTube video, Kenji Farré (Kenji Explains) [MVP] announced that he has largely stopped using PivotTables and instead builds reports with Excel’s newer formula functions. He argues that the modern functions offer clearer logic, automatic updates, and greater control over layouts when compared with traditional pivots. As a result, Kenji demonstrates practical examples that many readers and analysts will find easy to follow and test in their own workbooks. Consequently, this story examines his approach, the key functions he showcases, and the tradeoffs for teams deciding whether to switch.

What Kenji demonstrated in the video

Kenji focuses on two recent Excel functions: GROUPBY and PIVOTBY, and he shows how they can replicate many everyday pivot tasks without creating a PivotTable object. He also demonstrates how these functions work with other modern tools like VSTACK, FILTER, UNIQUE, and SORT to combine separate sheets and build month-by-region summaries automatically. Moreover, he points out that formula results are visible in the formula bar, which can make logic more transparent for reviewers and auditors. Therefore, viewers can follow the calculations rather than reverse-engineer a pivot cache or nested UI settings.

How GROUPBY and PIVOTBY change reporting

GROUPBY lets users aggregate data by one or more keys while retaining the result as a live formula range, and PIVOTBY extends that idea to two-dimensional layouts resembling classic pivots with rows and columns. Because these approaches produce formula-driven outputs, they update immediately when source tables change and can use aggregations like MEDIAN that standard PivotTables may not offer directly. In addition, Kenji highlights that combining PIVOTBY with VSTACK and simple conditional logic allows automatic consolidation across sheets, which traditionally required Power Query or manual pivot refreshes. Thus, teams can build cleaner report headers and workflows that integrate with other formulas on dashboards.

Combining functions and practical examples

Kenji’s examples show that formula-based summaries can interact smoothly with other dynamic array tools: for instance, a single formula can pull months from separate tabs, stack them, and then pivot the stacked data by region and month. Furthermore, the approach supports filters and sorts inline, which simplifies building a formatted report that remains connected to source data. However, Kenji also demonstrates that assembling these formulas benefits from starting with a proper Excel Table, because structured tables make references and updates simpler. Consequently, the recommended workflow emphasizes clean source tables, stepwise formulas, and modular building blocks that are easier to review than opaque pivot settings.

Tradeoffs and the challenges teams should weigh

Despite the advantages, there are tradeoffs: formula-based solutions can become complex for very large datasets, and performance may lag compared with a well-optimized Power Pivot model or server-side processing. In addition, older Excel versions or users who rely heavily on the PivotTable UI may face a learning curve, so training and documentation matter when teams adopt these functions. Moreover, some multi-dimensional reporting tasks and advanced calculated measures still benefit from a dedicated data model and DAX, which is why Kenji notes that Power Pivot and DAX remain relevant for sophisticated scenarios. Therefore, organizations must balance transparency and flexibility against scale, compatibility, and the skill set of their staff.

When to keep pivots and next steps

In short, Kenji recommends using formula-based summaries for dynamic dashboards, transparent reports, and scenarios where headers and custom aggregations matter, while retaining PivotTables for quick ad-hoc exploration and keeping Power Pivot or DAX for heavy-duty models and complex measures. For many analysts, the best approach is hybrid: adopt GROUPBY and PIVOTBY for day-to-day dashboards, but maintain pivot and model skills for larger-scale analytics. Ultimately, the video provides a practical roadmap: try formula-based summaries on sample reports, measure performance, and then decide whether to migrate full workflows or keep a mix. Thus, the shift is less about removing tools and more about choosing the right tool for each task.

Conclusion

Kenji Farré’s video offers a clear, hands-on alternative to classic pivots by demonstrating how modern Excel functions can improve transparency and automation in many reporting tasks. While the new approach brings flexibility and clearer logic, it also introduces tradeoffs related to performance, version compatibility, and team training that leaders should consider. Finally, the practical recommendation is to experiment with GROUPBY and PIVOTBY on real reports and then adopt a hybrid strategy that keeps PivotTables and Power Pivot where they still outperform formula solutions. In this way, teams can capture the best of both worlds while managing risk and skills development.

Keywords

alternatives to pivot tables, Excel alternatives to pivot tables, Power Query instead of pivot tables, Power BI vs pivot tables, dynamic arrays vs pivot tables, data analysis without pivot tables, XLOOKUP instead of pivot tables, how to replace pivot tables in Excel