Not able to get whether pivot item is collapsed RRS feed

  • Question

  • Hi Team,

    I am working on a excel add-in using c# where I have to find the status of the pivot items i.e. whether it is collapsed or expanded. I can collapse or expand by using this code (pivotItem.DrilledDown = true/false) but problem is that I am not able to get whether it is collapsed or not. Basically I am trying this (if(pivotItem.DrilledDown == false)) which always returns false. I have tried even in vba but no luck.
    Could you please suggest anything on this? Thanks in advance.

    Thursday, November 15, 2018 7:29 AM

All replies

  • Hi Sibasish,

    Thanks for visiting our forum. Then here we mainly focus on general issues about Excel user interface. Since you query is related to developing excel add-in, I'll move your thread to the dedicated MSDN forum for Excel for better response:

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Yuki Sun

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, November 16, 2018 2:24 AM
  • Hi Sibasish,

    You could use pivotItem.DrilledDown only for OLAP data sources.

    You could not set pivotItem.DrilledDown if the field or item is hidden.

    Please see the following link:

    PivotItem.DrilledDown property (Excel)

    If you use VBA to get or set the status of a pivot items, you could use the Range.ShowDetail property. But this property isn't available for OLAP data sources.

    Please refer to the following link:

    Range.ShowDetail property (Excel)

    VBA - Is there a way to check if a Pivot Table Field is Showing Details or not. Is it Expanded or Collapsed?

    Hopefully it helps you.

    Best Regards,


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Friday, November 16, 2018 9:18 AM
  • Hi Bruce,

    Thanks for your answer. All my tables are OLAP data source so obviously I can not use range.ShowDetails property. Only way to go with PivotItem.DrilledDown property. I tried to check whether the pivot item is collapsed or not (which are definitely not hidden) by using "if(pivotItem.DrilledDown == true)"  but it always returns false. So is there any way to check whether pivotitem is collapsed or not using c#?



    Thursday, November 22, 2018 9:05 AM