Forum Discussion

Brass Contributor

Oct 24, 2021

How to create a pivot table over a range containing dynamic arrays?

I have my raw data in Column C. In Column E I have a dynamic array `=UNIQUE(C4:C9)`. Now, I would like to create a pivot table whose data source is this dynamic array in Column E. When the height...

excel

mtarler

Silver Contributor

Oct 24, 2021

You don't. Create the pivot table using the Raw data and it will automatically collect the unique data. Maybe you need to give the full picture.

Tie CHENG
Brass Contributor
Oct 24, 2021
UNIQUE formula is just an example of dynamic array formula. What I want to say is that the range holding a dynamic array formula may change because of the change of the value of the raw data.

And how to create a pivot table over this dynamic range?
- SergeiBaklan
  Diamond Contributor
  Oct 24, 2021
  Tie CHENG
  
  In general you may create PivotTable using spill as source, but not directly and if you are on Beta version of Excel 365. In it you may Power Query the spill, make some transformations and land result as PivotTable.
  
  On the other hand, it all very depends on scenario. I understand UNIQUE() is only the sample, but applying Power Query to initial data source plus adding explicit measures in data model, most probably you may build desired PivotTable without using of spills.
  
  Another possible option is to forget about PivotTable and build entire solution using dynamic arrays programming only.