Formatting the y-axis of a chart

[Sebastian1942]

Can I have a minimum value of 390, yet keep 400 as the minimum displayed number ? If so, how ?

[teylyn]

just like you did in your chart. Define the axis minimum to the value you want. A value lower than that will simply not appear on the chart.

If that's not what you want, describe in more detail.

[Andy Pope]

Simplest way is to build the axis labelling yourself with a dummy data series
http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

[Sebastian1942]

Simplest way is to build the axis labelling yourself with a dummy data series
http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

Is the principle involved here : 'to use dummy data to get the desired formatting of the y-axis but have the dummy points invisible as they will lie on the Y-axis, and then to plot a 'second' set of data (the REAL data) ? I suspect that second set of points will have 'default' colour and shape - can THEY be customised - if so how ?

[teylyn]

I'm afraid I'm not getting what you're after. Can you explain? Do you need a dummy series at all? Can't you just define your Y axis min and max values to show what you need?

If your lowest value is 380, but your axis min is 400, what do you expect to see?

I seem to be missing something.

[Andy Pope]

With 390 as the min the Y axis labels would be 390 and 490.

Whilst you could use a number format the dummy series approach is more flexible.

Set axis font to White and transparent background.
Custom number format

[Black][=400]0;[Black][=500]0;General

[Sebastian1942]

With 390 as the min the Y axis labels would be 390 and 490.

THAT is what I have found and is why I asked the question. Even though 390 is my minimum value I would like the value displayed to the left of the Y-axis to be 400 & 500, NOT 390 and 490.

[Sebastian1942]

<<If your lowest value is 380, but your axis min is 400, what do you expect to see?>>
Obviously if the axis minimum is 400, 380 wont show. I want the Y-axis to extend below 400 so that 390 can be plotted, but I want the value displayed beside the Y axis to be 400 and 500

[teylyn]

Ah, the light just went on.

See attached for the method mentioned by Andy applied to your chart

A dummy series with X values of the lowest value on the X axis, combined with Y values of even distribution. The data markers sit on the Y axis. Use the XY Chart Labeler tool http://www.appspro.com/Utilities/ChartLabeler.htm for easy creation of labels for the data points to the left of the markers, then remove the axis labels.

I've left the data markers for the dummy series visible, but you can set them to no markers to hide them.

Just make sure that your primary and secondary Y axes have the same Min and Max settings.

[Sebastian1942]

Ah, the light just went on.

See attached for the method mentioned by Andy applied to your chart

A dummy series with X values of the lowest value on the X axis, combined with Y values of even distribution. The data markers sit on the Y axis. Use the XY Chart Labeler tool http://www.appspro.com/Utilities/ChartLabeler.htm for easy creation of labels for the data points to the left of the markers, then remove the axis labels.

I've left the data markers for the dummy series visible, but you can set them to no markers to hide them.

Just make sure that your primary and secondary Y axes have the same Min and Max settings.

Thank you for your effort. It certainly has achieved what I specified, that the printed numbers do NOT 'start' from the minimum value in the data, but at the 'rounded' figure of 400. However the horizontal 'axis' is at or just below a minimum data point/value, instead of at 400 - similarly the other horizontal line is not at 500 but at 480. Cosmetic maybe, but different from my expectation.
If falls short of my expectation to the extent that the horizontal lines are

[teylyn]

format the Y axis and set it to "Horizontal axis crosses at" <your preferred value> instead of automatic. Set the X axis labels to "Low" position, so they don't sit within the plot area

Turn off the horizontal gridlines alltogether and create new data series that emulates the grid. See attached.

cheers

[Sebastian1942]

Teylyn
I can see in your attached chart that my requirements have been met. I have only a hazy understanding of the technique of using a second 'dummy' data set to tweak the y-axis display.

When I applied your advice to my original chart :
1. There was already no tick in 'auto' and
2. the displayed numbers on the y-axis changed from 400 & 500 to 390 & 490 as was my original 'complaint'.

A new strangeness : have modified the chart you sent me by removing excessive number displays on the y-axis (for 450 & 550) - so far so good
When I change the MAX from 580 (which my data will not reach) to 510, the plotted points now have the wrong Y values.

As before, there is no option to attach the chart to this reply.....

[teylyn]

To attach a file you need to click Go Advanced below the Quick Reply box. Then you'll see the Manage Attachments button or the paper clip icon.

Re the "strangeness": When you change the min and max for the primary Y axis, you also need to set the secondary Y axis to the same values, since the dummy series is plotted on the secondary axis.

Please attach your file. I don't see what's not working.

[Sebastian1942]

Having found how to "attach", I have apparently lost the comments I made !:

How do I get access to 'secondary' as distinct from 'primary' axis for editing?

[teylyn]

You can turn on the Chart Toolbar, then select the chart element you want to format from the drop-down. That comes in handy when you can no longer see a chart element, because the existing formatting makes it invisible. Then click the Format icon or the Format menu - Selected <chart element> to open the format dialog for that chart element.

As long as you set the same parameters for the Y axis and the Secondary Y axis, you should be fine.

cheers