Excel: How to create a chart that automatically scales the axis

It can be done with a bit of trickery, but if it's a simple chart, it's almost definitely easier to just manually draw some new labels using text boxes with opaque backgrounds over the existing labels.

But...

Hide the existing Y-axis ticks and labels, then plot a new X-Y series of points like this:

x y

0 1

0 2

0 3

0 4

. .

By changing the format of the point markers to something suitable, like horizontal lines,you'll get a new set of tickmarks straight up the y-axis.

Unfortunately this is where it gets a little more fiddly. You now need to plot a data label for each point to form your new y-axis tick labels. As far as I know not even the latest version of Excel can do this automatically, but you'll find various macros to do this for you (Google: Excel X-Y scatter point labeller).

You'll then be able to add an extra column:

x y label

0 1 f 

0 2 e

0 3 d

0 4 c

. . .

Run the macro and each pseudo-tickmark will have a label next to it. But you'll need to play with label alignment settings to get them in the right place.

The most simple way to do it is with an Scatter (X-Y) chart but this is a line chart and won't display the bars you want.

To do it with bars you need to create a pivot table with date in the row labels and sum of qty as the values. Note this will only work if you only have one sample per date.

Set grouping on the row labels to 'Days' and 'Years' and then go in to field settings and turn on 'Show items with no data'.

Then create a pivot chart using the bar chart style. You'll get a bar chart with 365 days and the samples spaced correctly.