Expand Pivot Tables without overwriting existing data beneath or to the right of table
With all the queries on the internet, we assume there is no built-in option to prevent pivot tables from overwriting data beneath the table during refresh and expansion. Is that correct?
Given that Excel has options for 'inserting' rows, why in the world would Microsoft NOT provide a pivot table expansion/contraction option for inserting or deleting rows from within the refreshed table without overwriting data beneath or to the right of the table?
It is also interesting that inserting rows into a regular Excel table, using the TAB key in the last row, can add rows by expanding the table without overwriting the data beneath. Yet, it will fill in any blank rows first before shifting down the data rows beneath the table.
Given the many requests for this option, we are surprised that Microsoft has not responded to this request.
This is a very interesting idea and we can see how it would be helpful. Please keep voting so that we can properly prioritize this feature against other work we’re planning. Thanks!
This feature would actually reduce so much work that goes into making sure there's no data around pivots that can be overwritten. @ Microsoft it's about time you get us this feature! :)
In financial modelling you have to decide on the structure of the model, if you want to use one sheet or several. one sheet has many advantages, as simplified code in functions, shorter functions and less chance of making period mistakes when referencing cells in other sheets etc. It is also a great advantage when you want to model a merger or consolidate companies and need to have models for company A, B and Combined unit in the model. It would be a great advantage, and make the model more dynamic if one could use powerpivot tables as input and at the same time keep the model for one company in one sheet, but when you risk that pivot tables overwrite content when they change size that just becomes a very risky business. Please provide this feature as it would really increase the usefulness of pivot tables in financial models
likewise for columns - i often have formulas following the table for further processing, and my kludgy work around is to always leave at least one blank column, which means that visually the related formulas are separated from the pivot data.
LG DES commented
This feature would avoid Refresh Data error: "PivotTable report cannot overlap another PivotTable"