microsoft excelmicrosoft-excel-2010pivot table
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….
By default, Pivot tables share the same pivot cache. If you want two pivot tables with different groupings, you need to make sure that you create two different pivot caches. These will take up more memory, which is why Excel defaults to using one pivot cache.
In Excel 2003 pivot tables were created with the Pivot Table Wizard. This Wizard contains a question in the first step that only appears when you create more than one pivot table. If you keep that option unselected, Excel will create a new pivot cache after issuing a warning.
In Excel 2007 or later, the pivot table wizard is not normally used, so this option is not readily available.
But here's the good news: You can open the Pivot Table Wizard with the keyboard shortcut Alt - D - P. Alternatively, you can add it to a custom group on the ribbon or put it in the QAT. It's in the "Commands not in the ribbon" list.
The keyboard shortcut comes from the Excel 2003 menu bars, Alt - D to open the Data menu, then P to select the Pivot chart wizard:
What you are looking to do is "depivot" your data, then repivot it to another layout. This link is very useful, for the "depivot" step. I've used this technique for years and it's truly a time-saver!
Here's how to depivot your data.
1.With your layout as shown, hit Alt+D,P. This will bring up the old Excel 2003 Pivot Table Wizard, which looks like this. Select "Multiple consolidation ranges" then "Next": 
2.Choose "I will create the page fields", and "Next":
3.Highlight your range so that it appears in the upper "Range" box, then click 'Add' to copy that range to the "All ranges:" box. Click "Next".
4.Choose where you want to put the Pivot table. I usually choose "New Worksheet"; click "Finish". You'll get this:
5.Uncheck "Row" and "Column". You'll be left with a one-cell pivot table:
6.Double-click the one cell. You'll get your data arranged in a table format on a new sheet. You can now use the table as the basis for your new pivot table layout. Note carefully the placement of the fields in the "Row labels", "Column Labels" and "Values" areas:
Best Answer
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.