How do I change where the secondary axis data is plotted in Excel chart?

Question

I have an Excel chart with two vertical axes; Dollars on Primary and Claims on Secondary, and Date Range horizontal. For each date range there are three organizations I'm comparing between (MaineCare, Emdeon, and Medicare). Here's my data. (Sorry, I don't know how to insert it in a table here so that it's copy/pasteable).

The Dollars are clustered columns, and the Claims are lines. Currently for each date range, all three line markers are centered in the middle column, which is not ideal. Here's my chart.

Is there a way to make each line marker line up with its corresponding column (green with green, blue with blue, etc.), OR a way to move the line markers so that they aren't lined up with any column and are instead positioned to the right of the Medicare columns in their own space?

I appreciate any advice you can offer me on this.

Answer 1

Excel (thru 2010, not sure about 2013) won't do this natively. However, there are two one way to get the same visual effect (actually my second idea wouldn't meet your goals, so please disregard).

Use an XY Scatter chart instead of a line chart for your Claim data. This way you can explicitly define the location of each point along the horizontal axis. Here's how it will look:

Here's the general steps:

1. Create a chart with your 6 data series.
2. Assign the Claims series to a secondary axis.
3. Add your horizontal and vertical secondary axis to the chart (not necessary, but visually it helps align everything).
4. Change the chart type to XY/Scatter for your Claims series. This will change their current values to their new vertical values-their horizontal values will be blank, which defaults to the equivalent of {1,2,3,4,5}.
5. Add a column for the horizontal values for each series. In your case you'll have 3 columns of 5 values (matching the set of vertical values you have).

Now the tricky formatting part:

6. To simplify our math, in your column chart formatting, change the Gap Width to 100%.
7. Now, Chart Math. If you look at the image, you'll notice that Excel puts 3 columns in each category. It also puts a gap between the series groups. It splits the Gap Width (100% of one column, unless you skipped step #6 in which case your math is harder!) evenly between the first and last column of the group. So, the center of the first column is at 0.25 of width of the first category group, and 0.50 for the second column, 0.75 for the third column. The horizontal value of 1.0 falls exactly halfway between the first and second category group. So, the horizontal values for your first series are {0.25,1.25,2.25,3.25,4.25}. Your second series will increment up from 0.50 and the third from 0.75.
8. Now just edit your Claims series with your horiztonal values (either in reference to cells, or in a static set (not recommended)).
9. Format to taste.

Here's the data included with the chart:

FWIW, this is an extremely powerful technique that will allow you to create almost any custom chart you'll want or need (with Excel!). It's worth some experirmentation to see how you can configure different parts.