I love Excel's PIVOTBY function—but this one feature keeps me using PivotTables

I was excited when Microsoft added the PIVOTBY function to Excel. After years of building PivotTables, having a formula-driven alternative felt like a major upgrade. But after replacing several PivotTables with PIVOTBY, one frustrating formatting issue convinced me I wasn't quite ready to leave PivotTables behind.

PIVOTBY falls short when reports need to stay polished

Presentation problems can ruin a near-perfect spreadsheet

Earlier this year, I argued that PIVOTBY could replace many traditional PivotTables—and I still stand by that view. For exploratory analysis, chained formulas, reusable reports, and dashboards built directly in the grid, it's one of my favorite additions to Excel in years. But after using it in larger reporting workbooks, I found one area where PivotTables remain clearly ahead.

I recently attempted to rebuild a primary tracking dashboard by swapping out its core PivotTable with a brand-new PIVOTBY formula. On paper, it was a massive upgrade. The grid-native formula was cleaner, easier to audit, and much simpler to scale without relying on hidden spreadsheet layers.

But the problems began the moment the underlying data source updated overnight. While the PIVOTBY numbers calculated perfectly and adjusted their layout on the fly, my conditional formatting no longer behaved as expected. It became clear that while formulas are fantastic for analysis, polished reporting places a different set of demands on a workbook.

Conditional formatting is where PIVOTBY falls behind

The spill range moves, but the formatting doesn't

To be clear, it's a misconception that conditional formatting doesn't work at all on dynamic arrays. You can point a conditional formatting rule at a dynamic range and have it work perfectly well. The problem is that dynamic arrays don't offer the same structural awareness as PivotTables when you need formatting to follow changing data.

If you're familiar with dynamic arrays, your first instinct might be to use the spill reference operator (#) in the conditional formatting "Applies to" box. Unfortunately, Excel doesn't preserve that spill reference. If you enter something like =A1#, it's replaced with a fixed cell range as soon as you save the rule. The same happens with named ranges. Even though you can use a spilled array reference in the Name Manager, conditional formatting still doesn't maintain that live spill relationship in the "Applies to" field.

This is where the difference between PIVOTBY and PivotTables becomes obvious. When your data source fluctuates, a PIVOTBY result expands or contracts automatically, but the formatting rule itself doesn't understand what those changing boundaries represent. If your dataset shrinks, formatting can linger over empty cells. If your data expands, newly generated rows may be left without the intended highlights. While there are workarounds, they add extra complexity and maintenance that PivotTables avoid entirely.

PivotTables preserve your visual layout natively

When Excel understands the logic behind your layout

PivotTables treat formatting differently because Excel understands the relationship between report fields and the cells they occupy, rather than treating everything as a collection of fixed cell addresses. When you select the correct native setting, Excel associates the rule with the PivotTable field rather than a manually selected cell range. New rows inherit the same visual rules, removed items disappear cleanly, and your report maintains the layout you originally designed.

If you've never used PivotTables' structural conditional formatting before, it's surprisingly easy to set up. The key is telling Excel that your rule belongs to the report structure rather than a manually selected range.

For formula-based conditional formatting rules:

1. Select any cell inside the value field of your PivotTable that you want to format.
2. Click Conditional Formatting on the Home tab and select New Rule.
3. At the top of the dialog box, select one of these two options:
   1. All cells showing [Field Name] values to include subtotals and grand totals, which can be useful in variance analysis.
   2. All cells showing [Field Name] values for [Row/Column Field Name] to exclude subtotals and grand totals, which is often the better choice because totals usually use a different scale from the underlying values.

4. Choose your formatting criteria and click OK.

After applying the rule, Excel keeps the formatting tied to the PivotTable structure rather than to the report's current size and position. The result is a highlight system that automatically follows changing data during normal refreshes, rather than forcing you to maintain a moving target of cell references.

For built-in rules like color scales, data bars, and icon sets, Excel handles them slightly differently. Apply the formatting to one cell in the PivotTable, then use the Formatting Options action tag that appears to extend the rule to the entire PivotTable field. After choosing the formatting option, Excel expands the rule across the PivotTable while maintaining its connection to the report structure. This lets visual indicators continue working as the underlying data changes.

The exact process depends on the type of conditional formatting you're applying, but the underlying advantage remains the same: PivotTables let Excel track the relationship between the formatting rule and the data structure, rather than forcing you to maintain a moving target of cell references.

Choosing the right layout tool for the job

PIVOTBY and PivotTables solve different problems

Choosing between Excel's summarization tools comes down to understanding where each one works best. The PIVOTBY function remains ideal for quick, flexible calculations or ad hoc summaries. It shines in back-end reporting layers, modeling pipelines, and any scenario where raw data aggregation matters vastly more than aesthetic tracking.

However, traditional PivotTables remain my preferred choice for shared workbooks. If you're building dashboards, templates, or any high-visibility workbook that relies on strict visual thresholds to remain readable, stick with classic containers. I'd rather spend a few seconds inserting a PivotTable than continually maintaining conditional formatting rules around a changing spill range.

Microsoft hasn't made PivotTables obsolete—they simply solve different problems from PIVOTBY. That's why I haven't abandoned either tool. Instead, I've stopped trying to force PIVOTBY into the handful of jobs where PivotTables have a genuine advantage, while recognizing the many situations where PIVOTBY is the better option.

---

Newer doesn't always mean better

Switching to PIVOTBY taught me that the newest Excel feature isn't always the best fit for every workflow. Sometimes a decades-old tool still has advantages that newer approaches haven't yet matched, in the same way that many legacy Excel functions continue to earn their place in modern workbooks.