Formatting STOCK charts

[LiAD]

Hi,

Sorry i've tried this post before with no solution yet, but unfortunately i
need to find one quite soon.

I am using a STOCK type chart in excel with the table formatted in the
required
A B etc (up to 20 x-axis categories)
Av
Max
Min
Close

way. In order to make the chart more ‘viewable’ I have changed the colours,
sizes, bars etc as normal.

However if I need to change the range of data using the source data option
the chart reverts back to the default excel format of small bars, thin lines
etc and I loose the items that I formatted.

Can I stop this happening?
Why does it only happen to this type of chart and no other charts I use?
Where I can open this dialogue box without loosing formats

Thanks for your help

 

[Jon Peltier]

I don't have this problem when I change the source data in one step on the
Data Range tab of the dialog. If I change the individual OHLC data in the
Series tab, the chart loses its custom formatting. You should try to use the
Data Range tab of the dialog, because the series make less sense out of
context.

If you have to change each series individually, you can use the colored
rectangles highlighting the data in the sheet behind the chart. If you
select the whole chart, the category labels are highlighted with purple
lines, the series names with green, and the values with blue. If you select
just one series, these same parameters for just the series are highlighted.
You can move and resize these rectangles by clicking and dragging to adjust
the source data ranges. When I do this series by series, the chart retains
its formatting.

Another approach is to save the chart as a custom chart type, then reapply
it after adjusting the data, as described here:
http://peltiertech.com/Excel/ChartsHowTo/CreateCustomTypes.html

- Jon

 

[LiAD]

Thanks for the suggestions.

I will try the custom approach.

Their must be an option somewhere that I can adjust that is different in
mine to other peoples as no-one else looses the format. I can't avoid losing
it whatever way I try to change the data range. Even a brand new change
reformats itself in the same way.

Thanks

 

[Jon Peltier]

You never told us how you are changing the selections in the Source Data
dialog. Are you changing the Data Range selection or the series in the
Series tab?

- Jon

 

[LiAD]

Sorry,

It makes no difference as soon as I open the source data dialogue box the
chart has already reformatted. Whether I use the data range or series tab
makes no difference.

Should this matter?

 

[Jon Peltier]

I thought maybe the problem was if you were using Excel 2007. But (a) Excel
2007 has only a single dialog that includes both data range and individual
series data, and (b) I just tested it, and Excel 2007 does not lose
formatting when you change the source data using the any part of the dialog.

I will suggest not using the dialog at all to change your chart's data.
Instead use the technique I described which entails selecting a series and
adjusting the range indicated by the highlighted rectangles on the
worksheet.

Another alternative I didn't think of, which will work if the data is not on
the same sheet as the chart, is to edit the series formula of each series.
Select a series: see the formula in the formula bar? You can edit just like
any formula. You can overtype part of the formula to change, or select part
of the formula, and use the mouse to select a range.

Hmm, you know what? Now that I've been messing around with this, the
behavior has changed to match what you report. I hope you haven't broken my
Excel installation! (This is where I rub my chin and say, in my wisest
voice, Excel is like that sometimes.)

- Jon

 

[LiAD]

Whooopssss.

I guess thats a partial success then as now the problem can eb recreated.
Sorry if its due to my questions and prodding. I hope it gets fixed.
Intersting the way it happened and not before though.

I see what you mean about the blue box and the formula bar trick - didnt
know that so I've learnt something from it.

Thanks for your help once again
I hope it behaves more normally after.