Automatically insert rows on worksheet linked to Pivot Table

Copper Contributor

I'm using Excel in Office 365. I have a worksheet linked to a Pivot Table in the same workbook. The worksheet is linked via cell references on the pivot table table. Here's the problem: Whenever new rows of data are added to the pivot table, the worksheet does not automatically update with these new rows. I have to go to the worksheet and manually drag down the formulas so they reference the new pivot table rows. This happens in reverse when rows are deleted from the pivot table. I have to manually delete rows on the worksheet so they match the pivot table.

 

Example: 1) The pivot table contains 10 rows and the linked spreadsheet shows those 10 rows. 2) The pivot table is refreshed and now has 20 rows, but the linked spreadsheet still only shows 10 linked rows. 3)Then I have to go to the worksheet and manually change the cell references to include the 10 new rows from the pivot table.

 

Is there a way to have my data on the linked worksheet automatically adjust for changes in the number of rows on the Pivot table? Thanks for your help

7 Replies

Hi @Gregger 

 

If you have Office 365 then it is possible to use Dynamic Arrays to automatically "Spill Down" the formulas as new rows appear in the Pivot Table.

 

Another option to consider is to use Power Query to pull the data into the new sheet.  This would require clicking the refresh button to pull in the new rows.

 

Do you have Excel 365 (test by checking if you have the =SEQUENCE function) and have you used Power Query before? 

 

Hi @Wyn Hopkins 

 

I just tried to create a dynamic array on my worksheet that links to my pivot table, but i got a #REF! error.  I just entered the cell reference from the pivot and put a # after it, like 'Pivot!"B6#. But I haven't created a dynamic array before, so maybe i did it wrong?

 

I'm vaguely familiar with power query, but don't know how to use it to build off my pivot table. But i wouldn't mind having to simply click refresh to update the linked table.

 

Lastly, i do have excel 365. 

 

Thanks for the tips!

Hi,

The # reference is used to reference an existing dynamic array rather than a Pivot Table.
The concept can be that you reference a range with many more rows than the Pivot and then use a FILTER function to remove the empty rows dynamically...

BUT...

Can you clarify if you are manually adding any data to the new rows that get auto-added to the linked worksheet. I'm thinking there may be a risk that a new item gets added in a different row of the Pivot table and your inputs (if any) on the 2nd sheet would end up mis-aligned.

If this isn't a risk then I can mock up a quick Power Query demo for you



@Gregger 

Another option, since you are using Excel 365, is to use the SORT/UNIQUE functions on the source data to build lists of the candidate row and column headers.

You could then use the lists to pull data from the Pivot Table using GETPIVOTDATA by using them as an array of parameters.  Errors returned by missing data rows could be replaced by blanks or filtered out.

@Peter Bartholomew 

IMHO, GETPIVOTDATA in particular and all cube formulas in general add load of headache if data source is updated in range. With data source pivoted in the same workbook and result returned / linked to another table Power Query could be more proper solution.

@Sergei Baklan I am perfectly content to go with your opinion on the matter.  I only very rarely get involved with data analysis and on those occasions that I do, I spend too much time fighting with PT defaults that I don't want.  Added to that, I always forget the need to refresh the data at least once.

 

What I did remember was that GETPIVOTDATA (now working as a dynamic array formula) was more reliable than trying to address cells directly and gave me scatter charts as well as bar charts that held their formatting when series disappeared.  Now I tend to ignore pivot tables and use dynamic arrays but that is merely a reflection of the work I encounter, given the need or opportunity I rather like exploring Power Query.

@Peter Bartholomew @Sergei Baklan @Wyn Hopkins @

 

Hey Guys, Thanks for the suggestions! I'll spend some time giving them a try over the weekend and let you know what happens.

 

Thanks!!!