Published 12 minutes ago

In 2021, after catching the tech bug as a kid and earning a degree in communications, Ada got into tech writing by editing crypto guides.

Ada’s expertise now spans Windows, Android (especially Samsung devices), mobile apps, and productivity tools through several years of hands-on exploration. She’s published over 100 articles on MakeUseOf since 2023. Her knack for breaking down complex stuff into engaging articles has earned nods from tech enthusiasts.

You’ll likely find Ada curled up with a riveting book when she’s not testing the latest shiny tech toy or uncovering its how-tos through her articles. It’s her way to unplug and fuel her passion for lifelong learning.

You’d think that PivotTables are the easiest and least stressful way to summarize data in Excel. For a long time, I believed that, too. That changed once I got comfortable enough using GROUPBY and PIVOTBY. These functions let me build summaries that behave like living parts of my worksheet rather than isolated report islands that need to be refreshed, rebuilt, or tweaked every time the underlying data changes.

I’m not arguing that PivotTables are now useless, because they still solve certain problems very well. However, after seeing how fluid, responsive, and adaptable GROUPBY and PIVOTBY can be, I’ve found myself reaching for them first and relying on PivotTables far less often than I used to.

GROUPBY the one-axis wonder

Because who actually needs bells and whistles for basic summaries

The GROUPBY function is built to produce reports with row categories and aggregated values. I think of it as a high-speed alternative to simple PivotTables that only need a row-based breakdown.

It also handles tasks that PivotTables struggle with, such as aggregating text. For example, you can create a comma-separated list of sales reps per sales team by pairing GROUPBY with the ARRAYTOTEXT or TEXTJOIN function in Excel.

The basic syntax looks like this:

=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

Despite this long list of arguments, GROUPBY requires only three mandatory inputs: the row_fields argument defines the range Excel uses to group rows and generate headers; the values argument specifies the data you want to aggregate; and the function argument defines how that aggregation happens, whether that is a simple SUM, a PERCENTOF calculation, or even a custom LAMBDA. When I want more control, the optional arguments let me fine-tune headers, totals, sorting, and overall presentation.

Related

We need to talk about the LAMBDA function in Excel

Most people skip LAMBDA, but it’s simpler than it looks and can streamline your everyday Excel work.

Suppose you want to calculate total sales per sales rep and sort the results from highest to lowest. You can do that with a single formula:

=GROUPBY(B2:B37, E2:E37, SUM, 3, 0, -2)

Here, the row_fields argument pulls from the Sales Rep column, while the values argument references the Sales column. Because the aggregation function is SUM, Excel adds up the sales figures for each rep. The 3 tells Excel to display headers in the output; 0 suppresses the Grand Total row; and -2 sorts the results by the second column (Sales) in descending order. The result is a clean summary that appears directly on your worksheet and updates automatically whenever your source data changes.

That said, GROUPBY does have limitations. If the columns you want to group are not adjacent in your source data, you need to combine them with HSTACK, which makes the formula more complex. Because GROUPBY returns a dynamic array, you can’t use it with Excel’s Format as Table feature, so you must format each cell manually. Availability is another consideration: GROUPBY is available only in Microsoft 365 and Excel 2024, so anyone opening your file in Excel 2021 or earlier will see errors instead of results.

None of these are dealbreakers for me, though. The only dealbreaker shows up when I need to spread my summaries across both rows and columns, which is exactly when I switch to PIVOTBY.

PIVOTBY the two-axis power move

The overachiever of data summaries

PIVOTBY builds on everything GROUPBY does by adding a second axis for columns. It’s the formula equivalent of a full-featured PivotTable with both Row and Column fields. When a report becomes too long to scroll through, PIVOTBY lets me pivot a category, such as the year, onto the column axis and produce a layout that’s easier to read.

This added flexibility comes with a more complex syntax, since the function supports grouping across two dimensions:

PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])

In addition to the three mandatory arguments used in GROUPBY, PIVOTBY also requires the col_fields argument to define how data is grouped across columns. One of the biggest advantages over PivotTables is how percentages are handled. Instead of navigating menus to show values as a percentage of a total, PIVOTBY includes a relative_to argument that lets you calculate percentages of row totals, column totals, grand totals, or parent totals directly inside the formula.

Altogether, PIVOTBY has eleven arguments that give you control over your column headers, totals, and sorting. Setting all of them, especially when you are new to the function, can feel more complex than building a PivotTable, but most of those arguments are optional. For example, if you want to see Sales by Sales Rep across different years, you can use a formula as simple as this:

=PIVOTBY(B2:B37, YEAR(A2:A37), E2:E37, SUM)

This produces a summary with sales reps listed down the rows and years displayed across the columns. The formula groups by the Sales Rep column for rows, extracts the year from the date column to generate column headers, and sums the Sales values. Because the result spills as a standard dynamic array, I can feed it directly into almost any chart type, including Map charts, without any additional setup.

Related

That said, PIVOTBY is not without trade-offs. Unlike PivotTables, which can automatically group dates by month or quarter with a right-click, PIVOTBY requires you to group dates manually by wrapping the date column in functions like YEAR or TEXT. The way headers are displayed can also feel less intuitive than the polished layouts produced by the PivotTable Design tab. Performance is another consideration: for very large datasets, PivotTables often handle aggregation more efficiently because they rely on a dedicated cache, whereas heavy dynamic array formulas can sometimes slow down your workbook.

PivotTables aren’t retired, but they may spend more time on the bench

PivotTables are still easier to create for anyone who prefers not to use formulas, and they excel at automatically grouping dates or handling large external datasets from sources like SQL Server or Power BI. They also offer built-in interactive features, like drill-downs, that are harder to replicate with formulas alone.

That said, I use them far less often now. GROUPBY and PIVOTBY update automatically, handle tasks PivotTables cannot, such as returning comma-separated text with ARRAYTOTEXT or running custom LAMBDA calculations without the Data Model, and feed directly into any chart type. If you take the time to master them, you may find that the same is true for you.