The short answer is that your combining (unsuccessfully) a categorical series (the columns) with interval/ratio series (the line charts). So, Excel doesn't know how to plot your categories (columns) on an interval scale (the current X axis).
To verify this, just add a second horizontal axis and format the
Position Axis to
On Tick Marks and you'll lose half of your first and last columns (because they're centered on the 0), but your XY series will line up exact center of the columns, like this:
To correct it, the easiest thing to do is create a new chart.
- Create a column chart with your current columnar data.
- Add your first XY data series to the chart-it'll only allow you to add your Y component, because you don't have an XY chart.
- Select your XY data series, right click and change your series chart type to XY.
- Use the select data dialog box to add the correct XY data for that series.
- Configure your secondary axis (horizontal and vertical). Because your using categorical data (categories 1-10, named 8, 40, 72...296) on your primary horizontal axis, you'll have to adjust your secondary horizontal axis to match it. For the data you'll have you'll need a minimum -8 (8-16, or 8- 1/2 of 32) and max 312 (296+16, or 296+ 1/2 of 32). This is what the two axis look like next to each other:
Also, this only works for evenly distributed values, if your values follow different distributions, you'll have to use a different (likely more complex) method.
- Use the select data dialog box to add the other XY data series for the chart.
EDIT: I forgot to mention that mine is Excel 2010, but my experience has been that it should still be ok in 2013, but I haven't tested it.