Since Microsoft introduced the PIVOTBY function in Excel, many analysts and data professionals thought we had found the holy grail of interactive analysis without leaving the spreadsheet. The promise was tempting: a dynamic formula that replicates the grouping and aggregation of PivotTables, but with the flexibility of a live cell. However, after several months of intensive use, one seemingly minor detail —conditional formatting and visual customization— has forced me to keep one foot in the world of traditional PivotTables. In this article we explore the virtues of PIVOTBY, its aesthetic limitations, and how at Q2BSTUDIO we combine these tools with enterprise solutions like Business Intelligence with Power BI and custom applications to deliver truly professional reports.
PIVOTBY, part of Excel’s new dynamic array functions, allows you to build data summaries with a single formula. Its syntax is elegant: you define rows, columns, values, and an aggregation function, and Excel outputs a compact table that updates instantly when the source data changes. For those of us working with lightweight dashboards or quick prototypes, it is wonderful. No need to drag fields or worry about outdated ranges; everything recalculates like a normal formula. Moreover, it integrates perfectly with other functions like FILTER, SORT, and UNIQUE, opening a range of real-time analysis possibilities. However, when it comes time to present that data to management or a client, reality clashes with expectations.
The fundamental problem is that PIVOTBY returns a range of cells that inherits the sheet’s formatting, but does not retain custom styles, value-based conditional formatting, or smart borders. In a classic PivotTable, you can apply a color theme, set traffic light rules that persist when data refreshes, and add interactive slicers. With PIVOTBY, any manual formatting breaks as soon as the matrix expands or contracts because the result is dynamic and cells shift. For example, if you apply a yellow background to cells exceeding a threshold, when a new row is added, the formatting does not replicate automatically. This forces you to resort to tricks like conditional formatting with relative references, but often results in inconsistent or hard-to-maintain outputs.
For a company that needs solid, aesthetically coherent reports, this limitation is critical. It is not just about visual vanity: a poorly formatted report can convey lack of professionalism and hinder reading of key results. That is why at Q2BSTUDIO we recommend complementing Excel with more robust Business Intelligence tools. With Power BI, for example, you can build interactive dashboards with dynamic conditional formatting, slicers, and direct connectivity to sources like AWS or Azure. Furthermore, custom application development allows you to create personalized dashboards that exactly solve each client’s needs, integrating AI for predictive analysis and AI agents that automate anomaly detection. The cloud, whether AWS or Azure, guarantees scalability and real-time updates, while cybersecurity protects the sensitive data feeding those reports. Thus, although we love PIVOTBY for quick analysis, excellence in presentation requires moving to specialized platforms.
Another aspect that keeps me tied to PivotTables is interactivity. PivotTables allow expanding and collapsing hierarchies, filtering by fields, and using slicers that affect multiple tables simultaneously. PIVOTBY, being a function, lacks that native interactivity. You can combine it with form controls, but the user experience is far from the fluidity of a PivotTable. In contexts where the report recipient is not an Excel expert, the PivotTable remains more intuitive. To bridge that gap, at Q2BSTUDIO we design automation solutions that convert Excel data into interactive web applications with custom dashboards, using cloud technologies like Azure and AI services that provide insights without the user touching a formula. This is especially useful in environments where cybersecurity is a priority, since data never leaves a controlled environment.
Finally, it is worth noting that PIVOTBY will not disappear from my toolkit. For quick exploratory analysis, prototypes, and models that only I handle, it is unbeatable. But when the report must travel to a client, the board of directors, or be integrated into a corporate system, I turn to classic PivotTables or, better yet, professional BI platforms. At Q2BSTUDIO we help companies find that balance: we develop AI agents that analyze your Excel data and transform them into professional visual reports, offer cloud migration services and cybersecurity consulting so that your dashboards are robust and secure. In the end, the perfect tool does not exist, but the right combination does. And that combination, today, includes PIVOTBY for the analysis phase and something more solid for the final presentation. So, although I love PIVOTBY, a simple formatting detail is enough to remind me that I am not quite ready to leave PivotTables behind.
