Excel Chart - vertical gridlines with variable intervals

Question

I want to add vertical gridlines (based on the horizontal axis) that appear at every doubling of the X value, rather than at a constant interval (as shown below).

I am charting two curves (y = f(x)) and their sum. I am using stacked bars to highlight the "optimal" range of x (where the y of the sum line is lowest), but I also want to give a sense of where X doubles, since I'm charting this on a linear scale.

I'm happy to add another data series, but I'm not sure how to get it to draw vertical lines (as opposed to slopes). I tried my hand XY Scatter with straight lines, and that seems like the most likely solution, but my chart seems to go haywire when I do so.

The best answer would be built-in Excel functionality. Next best would be a straightforward workaround. At worst, I can share some of my failed attempts and get troubleshooting on those.

Answer 1

Bunch of little things to make this go right. I assume you setup your data similar to what I have - X values, your three lines, and then a column for the range (I used one column and shaded differently, looks like you used two, but trivial distinction.)

Trick is to add a column, and for the Y value use the top of your Y axis. (in my case 14). Add this to the doubling of every X as shown. Add to chart as a clustered column and set on secondary axis. Make sure primary and secondary Y axes have the max and minimum range set, auto will probably screw it up.

On the cluster, change gap width to 500%, and color it a light grey.

You can hide the secondary Y axis - I left it in the screenshot for clarity. You can also hide the "Vertical" column name from the legend as well - just click on to it and hit delete.

Answer 2

There are (at least) two ways to accomplish this. The easier is to keep everything in a single, XY/Scatter Chart format. This creates a pseudo-control type chart. To do this, simply:
1. Add one additional series with three points with the appropriate X values for your bands, and 0 for their corresponding Y values.
2. Add vertical error bars (positive) with the appropriate size to reach the top of your vertical axis.
3. Format to taste.

To more closely reproduce your image (with actual bands), you'll need to use a mixed chart type, with both XY/Scatter and Columns. This is more complex, but visually maybe easier to read. To do this:
1. Add 2 additional helper columns to your data. One for your Minimum column, the other your Close to Minimum column.
2. Calculate your values to either be your Vertical Axis value, when appropriate, or NA() (Excel charting ignores NA()). Assuming your Minimum value is occurs at Batch 6 and vertical axis is 30, use a formula like: =IF(BATCH=6,30,na()) Then repeat for your Close to Minimum values. 3. Add the both series to your existing XY/Scatter Chart (they'll have to be added as an XY series first). 4. Change the Series Chart Types to Column. 5. Adjust your horizontal axis to match between the two chart types. A secondary axis can be helpful to keep them straight. 6. Format to taste.