Feedback by UserVoice

 

How can we improve Excel for Windows (Desktop Application)?

← Excel for Windows (Desktop Application)

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.

57 votes

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

David Mitchell shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

13 comments

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • nahcr commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    I get this error message even if the tab has a single pivot table.

  • Anonymous commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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

  • Ido commented  ·   ·  Flag as inappropriate

    And/or add an option for excel to automatically add the needed rows/columns between pivot tables so that no overlapping does accrue.

Excel for Windows (Desktop Application): PivotTables and Power Pivot

Categories

Feedback and Knowledge Base

(thinking…)