My MIN MAX series is not working 'correctly' within my charts. « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

My MIN MAX series is not working 'correctly' within my charts.

(4 posts) (2 voices)

Started 2 years ago by NeverHappyMike
Latest reply from NeverHappyMike

Tags:

NeverHappyMike

Member
Joined: Jun '10
Posts: 29

Hi

I need to compare 8 charts, and their Max and min scales are all over the place. Therefore I've done some research and found that a common method is to create a new series that takes the MIN and the MAx of my data, and then copy and paste this series in to the charts. Change the data lines to no-line, and hey-presto.

This seemed to work as all my charts had the the same Min/Max scale/axis. Unfortunately the pasted series added to my original data and therefore actually changed my results.

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)

How do I use the MIN and MAX common scaling technique so that the user is unable to see the Min/Max series - neither in the legend or the chart? Or, is there a better way to force all the axis to be the same on all 8 charts?

Many thanks for your help.

Mike.

Posted 2 years ago #
Hui

Excel Ninja
Joined: Jul '09
Posts: 5,025

NeverHappyMike
Your approach is correct
Make sure you set the color of the line and markers to none
Then select the legend and then select the series within the legend and press the Delete Key
The series will remaion on the chart and be invisible, but will be used by the chart to set the common Max and Min and will not show up in the legend
If a Gap[ appears in the legend, use the up/dn arrows to select the series and set teh series Number at teh end to either 1 or the Maximum no of series so that it is now at the top or bottom of the legend box

Posted 2 years ago #
NeverHappyMike

Member
Joined: Jun '10
Posts: 29

Thanks Hui. Your comments about deleting a Min/Max legend name has helped me out of a spot.

I've managed to make it work by using paste special > new series>going through and doing all the hiding, etc.

One issue I've encountered is that the Horizontal scale changes from my required scale to the new MIN and MAX scale (which is literally the words Min and Max). I've managed to revert it back to the one I want by : highlighting one of the new series and then editing it's Axis label to the original axis range of my original data. It seems to work but I'm not usre it's the most effective way of doing it.

Ah well, at least I'm moving forward.

Thanks again
Mike.

Posted 2 years ago #
NeverHappyMike

Member
Joined: Jun '10
Posts: 29

Solved the Horizontal Axis problem. Remove the tick when pasting in the MAx Min from the Category X label option.

Mike.

Posted 2 years ago #