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

[Tie CHENG]

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 of the dynamic array changes (because the value of the raw data change), I would expect the pivot table to automatically adjust its data source to refer to the entire dynamic array.

Does anyone know how to achieve this?

[#2]

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.

[#3]

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?

[#4]

@Tie CHENG 

Why would you want the same data set that grows in one sheet?  Your Eye can only look at one set of information at a time and the idea of a pivot table is to do analysis to the raw data set meaning if you're going to analyze the raw data set it would be unlikely that you would be working on adding new data to the data set. So why would you want both in the same sheet?

[#5]

@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.