Excel graphs: my MIN/MAX axis scaling isn't working correctly?

Question

I have lots of graphs which I need to compare against each other. Therefore, I need a common scaling along the vertical (Y) axis. I'm encountering problems when pasting in my MIN/MAX series, and then hiding them, which I believe is commonly used to handle these types of issues.

My data is 6 columns and over 100 rows. I've created two rows at the bottom giving me a MIN and MAX series for each column.

I've highlighted the MIN/MAX range and tried copying and pasting them into the graph but the new series either 'embeds' itself within the real data series (see bold below). Giving me a totally wrong representation of the actual data.

SERIES(BEL!$N$2,(BEL!$C$204:$C$404,BEL!$M$1611:$M$1612),(BEL!$N$204:$N$404,BEL!$N$1611:$N$1612),2)

Or, I've tried pasting them in as columns, which seems to work but then my legend includes them, but I don't want the MIN/MAX series to be visible at all.

Any examples or links to how to's showing me how this is meant to be done would be greatly appreciated.

Thanks

Mike.

Answer 1

If I understand your description you have 6 series of 100 values or so.

You need to add a new series (column) called "range" (or minmax or whatever you like) for the MIN and MAX, and that needs onyl two values - the MIN and the MAX for all the other values not separately calculate some value for each series.

Add this series to your chart (copy / paste it or alter the source data range).

Format the series so it is invisible (no line, no points, no colour, nothing).

Select the entry in the legend for this extra series (not the whole legend, so click once, then carefully click so you have only one item selected). Press delete to remove that from the legend.

Job done.