Stop Chart shrinking when grouping rows

[GreenyMcDuff]

Hi all,

Hopefully this is relatively simple...

I have a data set on the left of my spreadsheet and a chart on the right.

I need to group the rows of data on the left. When I minimise the group the chart shrinks as well because it is in-line with the rows of data.

Is there anyway to stop the chart from shrinking??

Thanks

Chris

 

[Asala42]

Are you in Excel 2010?
There is a rather obsure option for this if you know where to look.

-Select the Chart
-Design Tab
-Hit "Select Data"
-Hit the button "Hidden and Empty Cells"
-and check off "Show data in hidden rows and columns"

 

[GreenyMcDuff]

Hi Asala,

No I'm in 2007 - sorry should have mentioned that.

Thanks for the suggestion but that still doesn't stop the chart for reducing in height when the rows are hidden. I'm not sure if I explained the problem that well... I'll try again

So, the data on the left occupies cells A1:B10 (10 rows high)
The Chart on the right occupies cells E1:J10 (10 rows high)

In the data set, cells A1:B6 are part of a group.
When the group is minimised, the data set becomes 5 rows high (because of the now hidden rows)
Unfortunately, the chart also becomes 5 rows high (because of the hidden rows)

Is there anyway to stop the chart from reshaping when I hide/expand the row group?

Thanks

 

[teylyn]

Hello,

right-click the chart and select "Format Chart Area" from the context menu. Select "Properties" in the left hand pane. Tick "Don't move or size with cells" and click OK

Now the chart will stay in exactly the same place and will remain exactly the same size, no matter how many rows are showing or hidden.

cheers,