Dynamic Array Functions vs. Pivot Tables: Building a Better Presentation Layer in Excel

Dynamic Array Functions in Excel vs Pivot Tables

I finally shared my true feelings about pivot tables, and people had questions. Lots of questions.

This post is for those who understood my intent and wanted to learn more about how I build presentation layers using dynamic array functions in Excel.

What We're Building

A region-by-quarter volume table that:

• Updates automatically when data changes
• Includes totals
• Has conditional formatting for headers
• Requires zero manual refresh

The Key Functions

SORT and UNIQUE

To get our list of regions:

=SORT(UNIQUE(RegionColumn))

This creates a dynamic array of unique region values, sorted alphabetically.

TRANSPOSE and UNIQUE

To get our quarters as column headers:

=TRANSPOSE(UNIQUE(QuarterColumn))

The transpose flips the vertical list into a horizontal row.

SUMIFS with Array References

To populate the data:

=SUMIFS(VolumeColumn, QuarterColumn, QuarterArray#, RegionColumn, RegionArray#)

The # symbol references the entire spill range from our dynamic arrays.

VSTACK for Totals

To add a total row that moves with the data:

=VSTACK(
  SUMIFS(Volume, Quarter, Quarters#, Region, Regions#),
  SUMIFS(Volume, Quarter, Quarters#)
)

VSTACK stacks one array on top of another.

The Conditional Formatting Trick

I use two rules:

Rule 1: Header Row

=AND($A2="Region", A2<>"")

Format: Bold, black fill, white text

Rule 2: Total Row

=AND($A2="Total", A2<>"")

Format: Bold, top border

The key is locking the column reference ($A) but leaving the row relative. This lets the formatting apply across the entire row.

The Magic: It's Fully Dynamic

When I add Q3 and Q4 data to my source—without refreshing anything—the table automatically expands to include the new quarters.

With a pivot table? You'd still be looking at Q1 and Q2 because you forgot to hit refresh.

Bonus: Handling New Regions

What if a new region appears (say, we split Latin America into Central America and South America)?

The table breaks—because our SUMIFS array is now shorter than our region list.

Solution: Use VSTACK to append "Total" dynamically

=VSTACK(SORT(UNIQUE(RegionColumn)), "Total")

Then use DROP to exclude the total from the region-level SUMIFS:

=SUMIFS(Volume, Quarter, Quarters#, Region, DROP(Regions#, -1))

The -1 tells DROP to remove the last item (our appended "Total").

When To Use This

Dynamic arrays are best when:

• You need a polished presentation layer
• The data structure might change
• You want zero-refresh updates
• You're building something others will inherit

Pivot tables are best when:

• You need to explore data quickly
• You're doing rapid prototyping
• The output is just for you

The Bottom Line

This approach takes more setup time than a pivot table. But once built:

• It's truly dynamic
• It never needs refreshing
• It looks exactly how you want
• It represents your brand

That's the trade-off I'm willing to make.

Common Questions About Dynamic Array Functions

Q: Do I need Microsoft 365 to use dynamic array functions?

Yes. UNIQUE, SORT, FILTER, VSTACK, and other dynamic arrays only work in Microsoft 365 or Excel 2021+. If you're on an older version, you're stuck with pivot tables or array formulas.

Q: Isn't this overkill for most analyses?

For quick exploratory work? Yes, use pivot tables. For deliverables you'll use repeatedly or hand off to others? No, this is the right amount of effort.

Q: What if I don't know these functions yet?

Start simple. Learn UNIQUE and SORT first. Then add FILTER. Then SUMIFS with array references. Build up your skills one function at a time. You don't need to master everything at once.