How to Replace Pivot Tables with the PIVOTBY Array Formula in Excel

by | Dec 9, 2024

Pivot tables are a staple in Excel for summarizing and analyzing data. However, they come with a few drawbacks: they do not automatically refresh when the data changes, and customizing them often involves manual adjustments. The PIVOTBY function, introduced with Excel’s dynamic array formulas, offers a solution to these challenges. By using PIVOTBY, you can create dynamic and interactive summaries that update automatically as your data changes.

In this guide, we’ll dive deep into how to replace traditional pivot tables with PIVOTBY. We’ll explore how to incorporate filters for advanced data selection, and how to integrate slicers for interactive filtering. Using the SuperHero dataset as our example, we’ll demonstrate the full capabilities of this modern Excel function.

The Power of PIVOTBY: Why Use It?

The PIVOTBY function is part of Excel’s dynamic array formula toolkit, designed to mimic and enhance the functionality of pivot tables. Unlike traditional pivot tables, PIVOTBY formulas are dynamic, meaning they adjust automatically when you update the source data. This eliminates the need to manually refresh or rebuild your pivot table every time your data changes.

Key advantages of PIVOTBY:

  1. Dynamic Updates: Automatically adjusts as data changes.
  2. Formula-Driven: Fully customizable using Excel formulas.
  3. Integration with Other Functions: Seamlessly combines with FILTER, SORT, UNIQUE, and more.
  4. Lightweight: No need to manage additional pivot table objects in your workbook.

The SuperHero Dataset

For this tutorial, we’ll use the SuperHero dataset. It contains information about various superheroes, including their creators, gender, universe, alignment, and attributes like Intelligence (Int), Strength (Str), and Speed (Spd). Here’s a snapshot of the data:

Name Creator Gender Universe Alignment Int Str Spd
Goku Shueisha Male Dragon Ball Good 45 100 100
Franklin Richards Marvel Comics Male Earth-616 Good 80 25 7
The Flash DC Comics Male Prime Earth Good 85 25 100
Darkseid DC Comics Male Prime Earth Bad 95 100 100
Dr Manhattan DC Comics Male Prime Earth Good 95 100 100
Molecule Man Marvel Comics Male Earth-616 Bad 100 100 100

This dataset will be used to demonstrate how to create dynamic summaries using PIVOTBY.

Download Workbook

 

Understanding the PIVOTBY Formula

Before diving into the examples, let’s break down the syntax of the PIVOTBY function:

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])

Key Parameters
  1. row_fields: Defines the rows of the pivot table.
  2. col_fields: Defines the columns of the pivot table.
  3. values: Specifies the data to be aggregated (e.g., numerical values).
  4. function: The aggregation method, such as SUM, AVERAGE, or COUNT.
  5. [filter_array]: Filters the data based on a logical condition.

For a complete list of parameters, refer to the official Excel documentation or explore the function tooltips in Excel.

Example 1: Creating a Basic PIVOTBY Table

Let’s start by summarizing the Intelligence (Int) attribute based on the Creator (rows) and Alignment (columns).

Formula
=PIVOTBY(SuperHero[Creator], SuperHero[Alignment], SuperHero[Int], SUM)

 

Explanation
  1. row_fields: SuperHero[Creator] groups the data by the Creator column.
  2. col_fields: SuperHero[Alignment] creates column headers for each alignment.
  3. values: SuperHero[Int] specifies the Intelligence column to be summarized.
  4. function: SUM aggregates the data by summing up values.
Output
  Good Bad Neutral
Shueisha 45 0 0
Marvel Comics 80 100 0
DC Comics 265 95 0

Example 2: Adding a Gender Filter

In this example, we’ll enhance the PIVOTBY table by adding a filter for Gender. The filter will allow us to dynamically display data for Male or Female superheroes, or both.

Formula
=PIVOTBY(

    SuperHero[Creator],

    SuperHero[Alignment],

    SuperHero[Int],

    SUM,

    ; 0; ; 0; ;

    IF(H30="All", TRUE, SuperHero[Gender]=H30)

)

 

Explanation
  • filter_array: This logical condition determines which rows to include:
    • IF(H30="All", TRUE, SuperHero[Gender]=H30) includes all rows if "All" is selected; otherwise, it filters by the selected gender.
  • H30: A cell containing the Gender selection (e.g., Male, Female, or All).
Output

When H30 = Male:

  Good Bad Neutral
Shueisha 45 0 0
Marvel Comics 80 100 0
DC Comics 265 95 0

Example 3: Adding Multiple Filters (Gender and Universe)

We can take this one step further by adding a second filter for the Universe column. This allows us to refine the data based on both Gender and Universe simultaneously.

Formula
=PIVOTBY(

    SuperHero[Creator],

    SuperHero[Alignment],

    SuperHero[Int],

    SUM,

    ; 0; ; 0; ;

    IF(H30="All", TRUE, SuperHero[Gender]=H30) *

    IF(H31="All", TRUE, SuperHero[Universe]=H31)

)

 

Explanation
  • The formula uses two filters:
    1. Gender Filter: IF(H30="All", TRUE, SuperHero[Gender]=H30)
    2. Universe Filter: IF(H31="All", TRUE, SuperHero[Universe]=H31)
  • The * operator combines both conditions, including only rows that satisfy both filters.
Output

When H30 = Male and H31 = Prime Earth:

  Good Bad Neutral
DC Comics 180 95 0

Advanced Features: Using Slicers with PIVOTBY

To make filtering more interactive, we can use slicers. Slicers are visual controls that allow users to filter data dynamically. When paired with PIVOTBY, slicers provide an intuitive way to analyze data.

Formula with Slicers
=PIVOTBY(

    SuperHero[Creator],

    SuperHero[Alignment],

    SuperHero[Int],

    SUM,

    ; 0; ; 0; ;

    BYROW(SuperHero[Gender], LAMBDA(r, SUBTOTAL(3, r)))

)

 

Explanation
  • BYROW: Applies a function to each row in the Gender column.
  • LAMBDA: Wraps the SUBTOTAL function, which determines whether a row is visible based on slicer selections.
  • SUBTOTAL(3, r): Returns 1 for visible rows and 0 for hidden rows.

By linking slicers to the Gender and Universe columns, you can dynamically control the data displayed in the PIVOTBY table.

Best Practices for Using PIVOTBY

  1. Organize Your Data: Ensure your data is in a structured table format, with clearly labeled headers.
  2. Use Named Ranges: Referencing named ranges (e.g., SuperHero[Creator]) makes formulas easier to read and maintain.
  3. Test Your Filters: Verify that your filters work correctly, especially when combining multiple conditions.
  4. Combine with Other Functions: Pair PIVOTBY with FILTER, UNIQUE, and SORT for even more powerful summaries.

Conclusion

The PIVOTBY function is a game-changer for Excel users who want dynamic, formula-driven pivot tables. With features like automatic updates, multi-criteria filtering, and slicer integration, PIVOTBY allows you to create highly customizable summaries that are easy to maintain.

By following the steps in this guide, you can replace traditional pivot tables with dynamic arrays, unlocking new possibilities for data analysis in Excel.