Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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.

share|improve this answer
    
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 Jun 19 '12 at 15:24

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.