How do you format stacked area/column chart x-axis like a scatter plot?

Hi all, I'm having trouble formatting my stacked column and area charts. I can't get them to autoscale the x-axis (like a scatter plot can do) and I also can't get the stacked area chart to plot y-values from empty, "" or #N/A cells without forcing the graph down to zero (like a scatter plot or stacked column can do).

I am aware this may be possible with VBA but I need a solution that does not involve VBA.

Any suggestions would be much appreciated!

I've attached a screenshot with the formatting problems marked in red.
Capture.PNG

EDIT: generic workbook attached with a stacked area chart that has the same issue.
EDIT: I've found a workaround that solves the stacked area plotting blank values, so now just looking for a solution to the x-axis autoscale problem.

That doesn't look like an Excel version in your profile. More likely Windows 8.1. Please update your profile.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

The horizontal axis on an area chart is categorical.

If you format axis as Date you can get a more numerical version. But it starts at one, not zero. So you may need to add a dummy series to redraw the axis labels using datalabels of the dummy series.

You need to include a set of datapoints at the last value in order to get the vertical drop, rather than a slope.

N/A is a stacked chart is treated as zero

The axis formatting options for an area chart don't allow formatting min/max bounds like you can do for a XY scatter chart.

Unless something significant has changed between your newer version and my older version, this doesn't seem right to me. I can open Andy's file, select the horizontal axis, and choose whatever values I want for the min/max of the date axis. For example, I can enter 1/10/1900 and 2/9/1900 if I want the axis to have limits of 10 and 40. The only hard part is that my version of Excel INSISTS on short date entry in the axis limit fields, so I need some way in the spreadsheet of figuring out what date corresponds to the "date serial numbers" I want.

That's the real problem isn't it. To my knowledge, the automatic algorithm MSFT has provided is the unchangeable one behind the "Auto" setting in the axis dialog. Obviously, we can write a macro in VBA or other programming language that will calculate and update the axis limits, but, in the OP, you say that you cannot use VBA (I assume that applies to other macro languages, too). Until MSFT provides additional built in options, we are stuck with the default Auto algorithm, manually changing the axis, or some kind of macro.

On the off chance you decide that you can relax the restriction against VBA, this is the best way I'm aware of to automate changing the axis limits when Excel's default Auto algorithm isn't suitable: https://peltiertech.com/chart-udf-control-axis-scale/

If you have the horizontal axis set to auto then simply provide data points for the min and max values.