Pivot Table Overlapping Error
Please develop the dialogue / call out from simply stating 'A pivot table cannot overlap another pivot table' so that you are actually provided with a sheet and / or cell reference where an overlap is trying to occur.
If you have a large workbook / multiple sheets with many pivot tables it can be difficult to find the source of an overlap.
David Mitchell
shared this idea
This is something that clearly needs improvement, and we’re looking at it now. Please keep the votes coming as a way to help us prioritize this work. Thanks!
12 comments
-
nahcr
commented
a related error is that if you have a pivot using a table and you change the number of columns in the table, especially if you delete columns, all the pivots that refer to that table lose the source columns and you have to go in and manually correct every single pivot to change the source columns. i think this is related to using a table but not sure - it may also be having many columns like more than 26. and the same problem is involved - the error message does not tell you which pivots are a problem so you have to check every worksheet.
-
Edward Paul commented
To fix Excel cells overlapping the very first solution which I want to suggest you to try format cell option.
• Choose those Excel cells in which you are getting this Excel cells overlapping issues.
• From the context menu hit the Format Cells.• From the opened Format Cells dialog box, hit the Alignment option. In the text alignment you will see a horizontal option from its drop-down list choose the Fill.
• Hit the OK button. After that, won’t get Excel cells overlapping issue anymore.
http://www.repairmsexcel.com/blog/fix-excel-cells-overlapping
-
Solstice035
commented
This is error message is incredibly frustrating - added to the fact there is no simple way to diagnose this issue - let alone see which pivot tables exist in the workbook. Any improvement on the current status would be greatly appreciated.
-
Anon
commented
I get this error message even if the tab has a single pivot table.
-
Anonymous
commented
A related error message actually lists a pivot table name such as pivotTable2. unfortunately, there can be multiple pivot table 2, AND you still dont know which worksheet PivotTable2 is on. Microsoft should require that error messages for tasks that cross worksheets - such as CTRL-Alt-f5 always include at least the first worksheet name that generated an error.
-
Anonymous
commented
This is an amazingly obvious issue that Microsoft should have fixed long ago. My workaround is to copy the file, then delete one worksheet after another until the error goes away. then go back to the original and find the specific pivot on the sheet that when deleted stopped the error. Im sure the macro in the comments is simpler, but deleting one sheet after another is easy to remember.
-
Guillermo J Wainselboim
commented
Saw this posting, and thought it may help out, try's running an update and gives you the sheets that have errors and which pivot tables are the problem. I tried it and seemed to work, and working to see if by moving pivot tables actually works
Sub PivotCheck()
'
' PivotCheck Macro
' Running this macro will refresh all the pivot tables in the workbook. IF there are errors, a window will pop up and tell you which pivot table and what worksheet is causing the error. Why excel does not do this automatically is a mystery. Party on.
'
Dim pt As PivotTable
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
On Error Resume Next
pt.PivotCache.Refresh
If Err <> 0 Then MsgBox "pivot table """ & pt.Name & """" & vbCr & _
"refresh error on " & vbCr & "worksheet """ & wks.Name & """"
Next pt
Next wks
Set pt = Nothing
Set wks = Nothing
'
End Sub -
Cassie
commented
Agreed! Please improve this. Hours wasted looking for the error source.
-
LM
commented
Yes this is very annoying to try and fix
-
Anonymous
commented
Ido....that is it! Fingers crossed...
-
Jason Haywood
commented
*clapping* Genius Idea. Would love to see this happen soon. *looks at watch*
-
Ido
commented
And/or add an option for excel to automatically add the needed rows/columns between pivot tables so that no overlapping does accrue.