Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Chart on Grouped Data

    I have a chart based upon data that is in an outline group. The chart appears perfectly, as long as the group is expanded to display - as soon as I collapse the grouping the chart is blanked - even though the data is still on the same sheet!

    Is there a way round this?

    I have attached a sample sheet to illustrate - any and all help very much appreciated!
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Excel accomplishes grouping by hiding and unhiding cells. Therefore, when grouped, the data is hidden - and that's what the graph shows.

    I don't know a way around that, except to put the data somewhere else - on another sheet, somewhere "out of the way" on the active sheet etc.

  3. #3
    New Lounger Jon Peltier's Avatar
    Join Date
    Mar 2011
    Location
    Massachusetts
    Posts
    3
    Thanks
    0
    Thanked 2 Times in 1 Post

    Plot Data in Hidden Cells

    You can override the default behavior, and force Excel to plot data from hidden cells. On the Edit Source Data dialog in Excel 2007/2010, there's a button at the bottom for hidden/missing data. Click the button and choose to plot data from hidden cells. In Excel 2003 and earlier, it's on Tools menu > Options > Chart tab.
    Jon Peltier
    Peltier Technical Services, Inc.
    http://PeltierTech.com/WordPress/

  4. The Following 2 Users Say Thank You to Jon Peltier For This Useful Post:

    capri (2011-03-24),sdckapr (2011-03-23)

  5. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks Jon - I didn't know that option existed.

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Thanks Jon. I have been working with excel for years and never noticed this. It is especially nices since the option can be used on a chart by chart basis...

    Steve

  7. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Renfrewshire, Scotland
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excellent Jon - it seemed so strange that the data would not appear, as it still existed in the cells the graph referred to.

    Thanks for your help!

    Alba

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    it seemed so strange that the data would not appear, as it still existed in the cells the graph referred to.
    It is not strange once it is realized that this behaviour is the default for charts.

    It allows, for example, charts to be created on an entire dataset, and if filtering is added (or manually hiding rows) the chart will reflect on the visible data. This is very powerful with large datasets where much filtering is done.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •