How can I use multiple pivot tables on the same sheet without an overlap error?
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....
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.
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.
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.