A quiet formatting flaw has surfaced in one of Excel’s most celebrated new functions, and it’s forcing spreadsheet builders to rethink when they should retire the classic PivotTable. According to a detailed analysis by How-To Geek published on July 12, 2026, the PIVOTBY function—a dynamic array formula that generates PivotTable-style summaries directly in a worksheet—cannot reliably preserve conditional formatting when the underlying data changes. The spill range expands or contracts correctly, but formatting rules break. The result: polished, refreshable reports remain the domain of traditional PivotTables for now.

The formatting gap: what actually breaks

PIVOTBY is part of Excel’s modern dynamic array toolkit. It accepts source data and spits out a grouped, aggregated summary that updates automatically when inputs change. Unlike a classic PivotTable, everything lives in the grid—you can audit the formula, chain it with other functions, and avoid the hidden caches and refresh buttons of the old object model. Microsoft’s own documentation stresses that PIVOTBY is “not directly connected to the PivotTable feature,” and that independence is precisely what makes it so attractive to power users.

But autonomy has a cost. When you apply conditional formatting to a PIVOTBY output—color scales, data bars, icon sets, or threshold rules—Excel loses track of the range as soon as the data shifts. The initial rule typically uses the spill operator (e.g., =A1#), which should dynamically refer to the entire output. However, How-To Geek found that saving the rule forces Excel to convert the reference into a fixed cell address. The same problem occurs even if you first define a named range that references the spill formula: the named range updates, but the “Applies to” field inside Conditional Formatting does not follow.

In practice, that means three things:

  • New rows go unformatted. If your dataset grows and the PIVOTBY output adds rows, those cells land outside the fixed rule range.
  • Old formatting lingers. If the dataset shrinks, the rule still paints cells that no longer contain results.
  • Workarounds are brittle. You can manually expand the “Applies to” range or write VBA to reset rules on every refresh, but those approaches add maintenance overhead that PivotTables avoid entirely.

The limitation isn’t a calculation bug—PIVOTBY aggregates numbers perfectly. It’s a presentation gap that shows up only when visual cues matter as much as the data itself.

What this means for everyday users

For the analyst pulling ad-hoc numbers, the formatting hiccup barely registers. You write =PIVOTBY(...), see the summary, maybe throw on a light header color, and move on. The same holds true for back-end models where one PIVOTBY feeds another formula: conditional formatting is often nonexistent in those layers.

The pain hits when you build a dashboard, a executive report, or a shared template that relies on color-coded thresholds. If you’ve promised stakeholders that cells turn red when sales dip below $10K, PIVOTBY cannot guarantee that rule stays accurate after a data refresh. The first update might shift the red highlight two rows down or leave it stuck on yesterday’s numbers.

IT and admin staff responsible for workbook reliability face a similar choice. When a report is central to a workflow, the cost of a formatting error can outweigh PIVOTBY’s formulaic convenience. Traditional PivotTables, for all their quirks, have a built-in mechanism that keeps formatting attached to the data’s structure, not to rigid coordinates.

How we got here: two tools, two architectures

To understand why the rift exists, you have to look at how Excel treats each summarization tool:

  • PivotTable objects live in their own container. Even though they appear on a worksheet, the underlying engine manages field relationships, caching, and layout. Conditional formatting can be scoped to a field rather than a cell range. Microsoft’s documentation explicitly describes three scoping options: “Selection,” “All cells showing [Field Name] values,” and “All cells showing [Field Name] values for [Row/Column Field Name].” That second option ties the rule directly to the field, so whether you filter, collapse, or move fields, Excel knows which cells belong to that rule and adjusts accordingly. Additionally, a “Preserve cell formatting on update” checkbox ensures that any direct formatting you apply (fonts, borders) survives refreshes.

  • PIVOTBY, along with its sibling GROUPBY, is a formula. It outputs a dynamic array. Dynamic arrays are a relatively young feature—introduced in 2018 with the FILTER and SORT functions—and many Excel components, including Conditional Formatting, have not been rearchitected to fully embrace them. The spill operator # works beautifully inside formulas but hits a wall when Conditional Formatting’s legacy engine tries to store it. The engine expects a static reference, so it quietly replaces the dynamic pointer with a hard-coded address.

This isn’t malice; it’s a gap in feature maturity. Dynamic arrays have been a rolling revolution, and not every corner of Excel has caught up. The PIVOTBY function itself only became generally available in 2023 for Microsoft 365 subscribers, and it’s still evolving. The current behavior reflects an internal tension: a formula-driven tool that lives in a grid vs. a formatting engine designed for static ranges.

What to do now: a practical guide

If you’re staring at a workbook that mixes PIVOTBY and conditional formatting, here’s a decision framework based on the findings:

Use PIVOTBY without conditional formatting when:

  • You need formula composability (e.g., you’re nesting functions, building custom sort orders, or feeding the output into LAMBDA).
  • The summary is intermediate—it will be read by another formula, not by a human.
  • You’re performing one-off exploratory analysis and can apply formatting manually if needed.
  • You want to avoid the overhead of a PivotTable’s cache, which can balloon file size on large datasets.

Stick with traditional PivotTables when:

  • Conditional formatting is part of the report’s intended design—especially color scales, data bars, or icon sets that must track changes.
  • The workbook is shared, and other users will refresh the data without understanding the underlying formatting rules.
  • You need field-level formatting that survives layout changes (adding/removing row fields, expanding/collapsing levels).
  • The report is a template that will be reused over months with varying data sizes.

If you must use PIVOTBY with conditional formatting today:

The How-To Geek article confirms that you can approximate stability by oversizing the “Applies to” range. For example, set the rule to cover $A$1:$Z$1000 even if your spill currently occupies only 50 rows. This prevents missing new rows when the output grows, but it also clutters empty cells with formatting and requires you to have a rough idea of maximum size. You’ll need to periodically check and expand the range if the data grows beyond your estimate.

For PivotTable users, applying structural conditional formatting correctly is straightforward once you know the right dialog:

  1. Click inside the PivotTable’s Values area.
  2. Go to Home → Conditional Formatting → New Rule.
  3. At the top of the dialog, choose the field-aware option: “All cells showing [Field Name] values” (to include subtotals/grand totals) or “All cells showing [Field Name] values for [Row/Column Field Name]” (to exclude totals).
  4. Set your rule criteria and click OK.

When using built-in formats like color scales, apply the formatting to one cell first, then use the “Formatting Options” smart tag that appears to extend the rule to the entire field. This ensures the visual indicator is tied to the field, not a fixed location.

A hybrid approach

Power users are increasingly combining both tools. Build a PivotTable for the front end that stakeholders see, and use PIVOTBY formulas on a hidden sheet to feed the PivotTable’s source data or to drive additional calculations. This gives you formulaic flexibility where you need it while preserving the formatting reliability of PivotTables in the visible layer.

Outlook: will Microsoft close the gap?

Microsoft has not publicly commented on a specific timeline for making Conditional Formatting spill-aware. The company’s development focus has been on expanding dynamic array function—GROUPBY, PERCENTOF, and others—as well as integrating Python and AI. That said, fixing the “Applies to” field to accept and retain spill references seems technically feasible. Until then, the choice between PIVOTBY and PivotTables remains a trade-off: raw formula power vs. polished presentation persistence.

For now, the older tool holds one edge that the newer rival cannot match. The next time you build a report, ask yourself whether the numbers alone will tell the story—or whether the colors need to follow along.