6
1
I have multiple pivot tables on the same sheet. Since each and every one of them have a dependent size due to the data, it causes the error:
A pivot table can not overlap another pivot table.
Is there any smart way to get around this? I need them all to be on the same sheet unfortunately....
Maria
Posted 2012-06-19T13:26:06.237
Reputation: 81
4
Welcome to Superuser.
You are receiving this error because the tables are trying to expand to display the data which meets the pivot criteria. The tables have to grow to to do this.
What you are asking appears to be a question asked over several years on many forums. Unfortunately, there isn't a definitive answer. It seems to be that the pivot table function was not designed to play nice with other pivot tables on the same sheet.
If your pivot tables MUST be on the same sheet, I make the following suggestion; place them side-by-side. You will need to have an idea how many columns will be needed for each table as it expands. Then place a gap between the tables which can allow for the expansion of the table.
CharlieRB
Posted 2012-06-19T13:26:06.237
Reputation: 21 303
3
I have this same issue often so i created the following work around. First, insert rows between your pivot tables that will allow them to expand to the maximum you need them to expand to. 1) insert a column to the left of the pivot tables.ex Column A 2) type the following formula in the 1st cell (A1) of the column. =if(B1<>"",1,2) 3) copy and paste this formula down to the last possible cell in the column. Each cell should contain the formula in column A. It should go all the way down the the last possible row of your pivot table. 4) select row 1 and insert a blank row. 5) apply a filter to each column across your worksheet (Data-Filter) when you filter Column A you can select 1 or 2. 1 will show only rows containing data (your pivot tables) and 2 will show all rows.
jim h
Posted 2012-06-19T13:26:06.237
Reputation: 31
This is clever. I implemented this as you explained, and hid the first row and first column leaving only the pivots and rows that I specified as "1" in the first column. Thank you. – Jason 'Jaypoc' Bauman – 2018-10-17T17:16:16.383
This is excellent. I tweaked this a little to give support to pivots that are multiple level and may end up with first value to be blank as the second level is repeating. My filters for pivots are starting from row 6. =IF(CONCATENATE(B6, C6, D6, E6, F6)="", "B","P") – Kalpesh Popat – 2020-02-18T07:28:26.003
1
I know this is a very old question from a few years ago but just in case someone else may encoutner the same issue. In Excel 2010 for example, all you need to do is to define the needed range and give it a unique table name. Then when you set up your pivot table, you can pick and choose whatever the defined table as your data source. That will allow you to insert multiple pivot tables (each may have different cell ranges) on the same sheet.
user310455
Posted 2012-06-19T13:26:06.237
Reputation: 11
You place place them side-by-side only if you're side your Pivot Tables won't expand sideways. – GTyler – 2016-06-02T23:50:31.360
Thank you for the welcomenin!:) I understand the reason why it happens, just as u explain. Also, doesnt seam to be a great solution for it all. Thought about ure suggestion aswell. But now im just thinkin out of nowhere, but is there no possibility/ function to for an exemple hide rows that are not being used, BUT, then automatically show up when needed. Thinking if i would have a lot of empty rows between all pivots so there would be space enough in need of expand. But then this imagined function would hide them....u understand what i mean? haha, i guess there is nothing like this though.... – Maria – 2012-06-19T15:24:30.160