Merge multiple 'lines' from a line chart into one line

Question

I currently have a chart as follows:

As you can see, I have three 'value' columns per X-axis item represented by the colours blue, orange and grey.

I also have three 'count' lines represented by the colours yellow, light-blue and green.

What I need is for the three lines to be merged into a single line and for the markers to appear over the corresponding column.

For example, looking at label D13 along the X-axis, you'll see:

• the blue column has 42.4%,
• the orange column has 72.7% and,
• the grey column has 15.2%.

Currently there are three lines overlapping these with all markers in the middle of the orange column. What I need is one line which has three markers over the D13 columns, one marker over the blue column, one over the orange column, and one over the grey column.

Is this possible?

My current data table looks as follows:

Answer 1

This is fairly easy to accomplish-it just needs a little math and creative formatting. The problem you're running into is that you're using a line chart for the lines (only in Excel would that be "the problem").

Line charts, like column charts, use categorical values for the horizontal axis. So, the line chart has the same 6 categories horizontally as the column chart and this means that Excel stacks the points over the middle of the column chart values. You can test this by adding tick marks to the horizontal axis and you'll see that the middle column and the line markers align with the middle of each category (since the axis default value is Axis Position: Between Tick Marks.

It's even more obvious if you change the value to Axis Position: On Tick Marks.

To accomplish what you're looking for you need to convert your line chart series to XY/Scatter series. This allows you to define the horizontal location for each point. Then, set each of the XY series to a secondary axis and set the Min to 0 and the Max to 6 (or the number of categories you have in your production data). Set the Major Units to 1 and Minor Units to the number of columns in each category plus 1 additional (to account for the spaces between grouped columns). Finally, add an X value for each data point {.25, .50, .75}, {1.25, 1.50, 1.75}... This can be in the series definition, another table, or columns in your existing table-whatever is easiest for you. The final product should look like:

And, a little formatting to make the lines and column colors match, but have enough contrast to be readable:

EDIT: To address your commented questions:
When setting the Minimum/Maximum Bounds for your axis, the actual numbers are less important than the number of intervals they create. In this case, you're looking to create the same number of intervals as the categorical data in your column chart. So you have 6 categories (D11, D12, D13, D14, D15 elect, and D15 J). Each of these has 3 subcategories (low, med, high) and the equivalent of 1 open space between groupings for a total of 4. This means you'll need 24 intervals along the horizontal axis.
So, you can use either a Min of 0 and Max of 24, with Major Unit of 1 and Minor Unit of 1:

Or, you can use a Min of 0 and Max of 6, with Major Unit of 1 and a Minor Unit of 0.25 (1/4 of the Major Unit).

Either will give you the same effect in the final chart.

EDIT: To answer your original question

To get a single line on a chart, you need a single series. Also, as mentioned above, for the effect you want (consolidating 3 data series into one line), you'll need to match the horizontal intervals of your points to the categorical value of the columns. So, you need to do 2 things. First, identify the horizontal values, and put them someplace Excel can find them and second, merge/concantenate/union them into a single data series.

First, identify the horizontal values. Based on the original answer, I'm going to add the Major (1-6) and Minor (0.25-0.75) units to columns in the table, so Excel knows what they're called and where they're at. In the following image, I added 3 columns to match the 3 Count categories in the original data and populated them (skipping the whole numbers which are the gaps between columns).

Second, we need to get them into a single, consecutive series that Excel can use for the charted line. There are at least 3 ways this can be accomplished. Generally, they are:

1. Using the Select Data Source > Edit Series dialog box for a data series, highlight each cell range, separated by a comma. This isn't too bad for a small, static dataset.
2. Manually define a matrix and then use the Excel Match/Offset combo to create automagically create a series. This works well once it's setup, but it doesn't expand automatically with additional data.
3. Creatively use 2 pivot tables to generate your data series. For Excel to create a chart line, it needs a series of values. For an XY/Scatter line, it needs a series of matched pair values (an x and y). So, you can create two Pivot Tables from your data, one each for x and y values. The data will be in a single column and then selected as the appropriate series source for your chart. It'll look like this:

They're two pivot tables, formatted in Compact Form with No Subtotals and the following fields:

Then just use the Count X pivot table for your series X values and repeat for Y. It may resort them in a different order (ascending or descending), just manually drag them back into proper order.

Once added and done, your final chart should look like this: