Creating a dynamic pivot table using the Excel PIVOTBY function

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Creating a dynamic pivot table using the Excel PIVOTBY function
This blog gives a practical example of using the new Excel PIVOTBY function to create a dynamic pivot table (one where you can choose the row, column and data fields from drop lists).

Posted by Andy Brown on 05 November 2024

In this blog

Step 1 - creating the column titles range
Step 2 - creating the column range names
Step 3 - creating the fomula

This blog shows you how to create a dynamic pivot table like this using the new Excel PIVOTBY function:

Your user can choose which row, column and data field to show.

You'll probably need to do a bit of background reading first before tackling this blog: for example on range names, the syntax of the PIVOTBY function, and on data validation settings. You can download this workbook to see all of the examples shown in this blog.

I've divided the process to create the example above into 3 steps, explained under separate headings below.

Step 1 - creating the column titles range

To create the dropdowns for these, first create a range called ColumnTitles as follows:

Create the given range name for the cells A1:F1.

You can then base your 3 dropdowns on this:

We'll let our user choose the row group, column group and data field.

Here someone is choosing to show the run-times of films by country and genre:

For each dropdown you can choose which field to show.

Step 2 - creating the column range names

To do this, select a single cell in the list and press Ctrl + A to select the whole range:

The block of cells containing your data.

Now press Ctrl + Shift + F3 to create range names:

Specify that your range names are in the top row.

This will create one range name for each column:

For example, the Oscars range name will refer to cells D2:D11.

Step 3 - creating the fomula

Before doing this, it'll make things clearer if you create one more set of range names:

Press Ctrl + Shift + F3 with these cells selected.

After choosing the option shown above, specify that your range names are on the left:

Select Left column and then choose OK.

You can then create your PIVOTBY formula:

Notice how the range names make the formula a bit easier to understand!

The INDIRECT function tells Excel to use not the contents of the cells referenced but the ranges given by their range names instead.

This should give your final pivoted data:

The data returned by this function, spilling over into as many rows and columns as prove necessary to accommodate it.

It doesn't seem easy to get the statistic to be choosable from a drop list (for the above example I've hard-coded the aggregation function to be AVERAGE). Any suggestions gratefully received for how to achieve this! I suspect the aggregation function is a hidden enumeration, but can't find any more information on this.

Some other pages relevant to the above blog include: