Excel Chart on Grouped Data

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

Thanks Jon – I didn’t know that option existed.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote