locked
Excel 2013 vs 2010 relating to double-clicking on a Pivot Table heirarchy RRS feed

  • Question

  • I'm using Excel 2013 as the query front-end to SQL Server Analysis Services 2012

    When using Excel 2010 if you double click on a row header, it automatically EXPANDS the selection.  So imagine you are looking at sales by year, with three years showing:  2015, 2014, and 2013.  If you double click on "2014", you can drill down to each quarter in 2014 ***BUT*** the 2015 and the 2013 totals will still show.

    Now switch over to Excel 2013.  If you do the same thing, then ONLY the 2014 quarters will be displayed (apparently a "drill through" display).  If you want to keep 2015 and 2013 showing in Excel 2013, you have to right click and select "expand".

    I like the Excel 2010 -- double click automatically expands the selection.  Anyway then to override this new "feature" in Excel 2013?

    Thanks.

    Thursday, October 8, 2015 1:25 PM

Answers

  • You can also single click on the expand button (small plus sign) in a pivot table; these buttons can be toggled on or off via the Analyze ribbon menu. See the picture at the bottom of this post. You can also use the ribbon menu expand/collapse buttons.

    The default behavior with a formal hierarchy is to drill down on double click. The default behavior with an informal hierarchy (when you just bring various fields onto the pivot table as row labels that are not members of a defined hierarchy) is to expand/collapse on double click.


    GNet Group BI Consultant

    • Marked as answer by Bob Harford Friday, October 23, 2015 7:32 PM
    Friday, October 9, 2015 2:55 PM

All replies

  • Hi,

    Since this issue is related to PowerPivot, I'll move this thread to PowerPivot forum:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlkjpowerpivotforexcel

    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.

    Regards,

    George Zhao
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Friday, October 9, 2015 2:05 AM
  • You can also single click on the expand button (small plus sign) in a pivot table; these buttons can be toggled on or off via the Analyze ribbon menu. See the picture at the bottom of this post. You can also use the ribbon menu expand/collapse buttons.

    The default behavior with a formal hierarchy is to drill down on double click. The default behavior with an informal hierarchy (when you just bring various fields onto the pivot table as row labels that are not members of a defined hierarchy) is to expand/collapse on double click.


    GNet Group BI Consultant

    • Marked as answer by Bob Harford Friday, October 23, 2015 7:32 PM
    Friday, October 9, 2015 2:55 PM
  • Greg,

    Thanks for the response and apologies for my slow response.

    I was aware of the +/- functionality.  I was not aware of the ribbon functionality, so thanks a lot.

    I will say again that this is a new Excel 2013 "feature".  Again, under Excel 2010, contrary to your point about Excel 2013, it was not default behavior to "drill down" / not expand the formal hierarchy against a SQL Server Analysis Services cube when you double click on that hierarchy.  I'd say this is a feature / default behavior change for the worse.

    But you confirmed that is sadly the case.

    Friday, October 23, 2015 7:32 PM
  • All users in my organization are EXTREMELY ANNOYED by this "change" in default double-click behavior.

    So the Microsoft confirms that there is no way to change it to function "the old way"?

    Monday, June 4, 2018 9:50 AM